1

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

| improve this question | |
5

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

| improve this answer | |
  • 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 ;) – Arash Mousavi Jul 2 '13 at 14:46
  • @Shlomi I don't see BIT columns considered in the article. Did you discard them intentionally? – ypercubeᵀᴹ Jul 2 '13 at 14:55
  • @ArashMousavi, not much into politics myself, most certainly not when it comes to code. – Shlomi Noach Jul 2 '13 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. – Shlomi Noach Jul 2 '13 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. – ypercubeᵀᴹ Jul 3 '13 at 21:30
-1

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.

| improve this answer | |
  • I said char(0) , not char(1)! – Arash Mousavi Jul 2 '13 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); – Peter Venderberghe Jul 2 '13 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. – Arash Mousavi Jul 2 '13 at 14:48
  • 2
    tinyint(1) will happily store 42 as well. The (1) does not limit the range of possible values. – a_horse_with_no_name Jul 2 '13 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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