For Boolean purpose column which one is better: nullable char(0) or tinyint(1).

I know bool is an alias for tinyint(1) , but in "High Performance MySQL" book that published by O'reilly said:

"If you want to store a true/false value in a single bit of storage space, another option is to create a nullable CHAR(0) column. This column is capable of storing either the absence of a value (NULL) or a zero-length value (the empty string). "

which one is better for size, performance, indexing or ...

UPDATE: I found this link useful for this question: Efficient Boolean value storage for Innodb Tables

2 Answers 2


You should keep in mind it is impossible to index a CHAR(0) column, even as part of a compound index.

In terms of size nothing beats NULLable CHAR(0); but how many of these do you have? As compared to other data types in your table? Typically when compared to overall data types & size this is neglect-able.

The clearest is BOOL/TINYINT UNSIGNED. It is most obvious what values are expected.

Otherwise please consider many alternatives in my post, and please see good ideas on comments: Choosing MySQL boolean data types

  • good article,it's probably better if it has benchmark for each of them. thanks for your help. It may be interesting to note that things are different in tech world from politics, A Israeli man helps An Iranian man ;) Jul 2, 2013 at 14:46
  • 1
    @Shlomi I don't see BIT columns considered in the article. Did you discard them intentionally? Jul 2, 2013 at 14:55
  • @ArashMousavi, not much into politics myself, most certainly not when it comes to code. Jul 2, 2013 at 18:45
  • @ypercube, see discussion in comments #1 - #4. For a single value this may be fine (it's just 0 or 1); for multiple values packed in one BIT columns the cost is not being able to index. Jul 2, 2013 at 18:48
  • 2
    @NeilMcGuigan Not necessarily (if True is 3% and False 97%, you may still want to optimize col=TRUE). And it can be even more useful in composite indexes. Jul 3, 2013 at 21:30

I think you should be using TINYINT(1) because with integer values, you can get values down to 0 and 1. With TINYINT, we only pay with 1 byte of storage. As with CHAR(1), we cannot prevent anyone from INSERTing other values than 0 and 1. But that doesn't really matter, if we're willing to accept that 0 evaluates as False, and all other values as True. Using TINYINT(1), boolean expressions works very well with your column values.

Size, Performance and Indexing:

I feel that retrieving and sorting integer fields is faster than character fields and it's familiar to show 1s and 0s for on/off flags too.

  • I said char(0) , not char(1)! Jul 2, 2013 at 13:20
  • OK .. char(0) as stated it shows NULL but same applied to tinyint(1) as well only difference is you can leave a field blank incase with char(0) whereas tinyint(1) stores as 0 always practically for boolean type nobody wants to leave field BLANK Another thing if you can execute this query on a table which has char(0) and tinyint(1) then you will not see any recommend datatype to use so it's your choice either you can go ahead with char(0) or tinyint(1) but ..I and many MySQL expert recommneds to use tinyint(1) for practical use: SELECT * FROM DB.test PROCEDURE ANALYSE(1, 10); Jul 2, 2013 at 13:44
  • I don't prefer any of them, I need to know pros and cons for each of them,Also I know many people suggests TINYINT, but this is not strong reason to select this one. Some of expert thinks different, I edit my answer to add link, check it if you want. Jul 2, 2013 at 14:48
  • 2
    tinyint(1) will happily store 42 as well. The (1) does not limit the range of possible values.
    – user1822
    Jul 2, 2013 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.