Boolean vs tinyint(1) for boolean values in MySQL

Question

What column type is best to use in a MySQL database for boolean values? I use boolean but my colleague uses tinyint(1).

It seems that MySQL transparently treats `boolean` as `tinyint(1)`. So you can use `boolean`, `true` and `false` and MySQL treats them as `tinyint(1)`, `1` and `0`. — ADTC, Nov 05 '16 at 07:26
Another case is char 1 with Y & N which is supposed to be faster by some people. — Zon, Jun 23 '17 at 05:14

Māris Kiseļovs · Accepted Answer · 2015-12-15T18:21:49.873

191

These data types are synonyms.

edited Dec 15 '15 at 18:21

answered Sep 20 '10 at 13:26

Māris Kiseļovs

16,957
5
41
48

8

I wouldn't say the data _types_ are synonyms -- tinyint(1) is the same as bool, but tinyint and bool are not the same. Minor point, but your answer tripped me up the first time I read it – Kyle Chadha Oct 06 '17 at 19:57
4

This doesn't answer the question. While it's true that tinyint(1) is functionally identical to bool, the OP asked what is best to use. The answer by @dj_segfault does a proper job explaining why bool should be preferred over tinyint(1) when storing a boolean value. – Kyle Morgan Nov 21 '17 at 02:05
Indeed, the [docs](https://dev.mysql.com/doc/refman/8.0/en/numeric-type-syntax.html) indicate BOOL and BOOLEAN are synonyms for tinyint(1). – Woodchuck Jul 13 '23 at 22:53

score 120 · Answer 2 · edited Jan 18 '22 at 15:13

I am going to take a different approach here and suggest that it is just as important for your fellow developers to understand your code as it is for the compiler/database to. Using boolean may do the same thing as using tinyint, however it has the advantage of semantically conveying what your intention is, and that's worth something.

If you use a tinyint, it's not obvious that the only values you should see are 0 and 1.
A boolean is ALWAYS true or false.

score 40 · Answer 3 · edited Oct 04 '21 at 08:14

boolean isn't a distinct datatype in MySQL; it's just a synonym for tinyint. See this page in the MySQL manual. See the quotes and examples down below from the dev.mysql.com/doc/

BOOL, BOOLEAN These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true:

    mysql> SELECT IF(0, 'true', 'false');
    +------------------------+
    | IF(0, 'true', 'false') |
    +------------------------+
    | false                  |
    +------------------------+
    mysql> SELECT IF(1, 'true', 'false');
    +------------------------+
    | IF(1, 'true', 'false') |
    +------------------------+
    | true                   |
    +------------------------+
    mysql> SELECT IF(2, 'true', 'false');
    +------------------------+
    | IF(2, 'true', 'false') |
    +------------------------+
    | true                   |
    +------------------------+

However, the values TRUE and FALSE are merely aliases for 1 and 0, respectively, as shown here:

mysql> SELECT IF(0 = FALSE, 'true', 'false');
+--------------------------------+
| IF(0 = FALSE, 'true', 'false') |
+--------------------------------+
| true                           |
+--------------------------------+
mysql> SELECT IF(1 = TRUE, 'true', 'false');
+-------------------------------+
| IF(1 = TRUE, 'true', 'false') |
+-------------------------------+
| true                          |
+-------------------------------+
mysql> SELECT IF(2 = TRUE, 'true', 'false');
+-------------------------------+
| IF(2 = TRUE, 'true', 'false') |
+-------------------------------+
| false                         |
+-------------------------------+
mysql> SELECT IF(2 = FALSE, 'true', 'false');
+--------------------------------+
| IF(2 = FALSE, 'true', 'false') |
+--------------------------------+
| false                          |
+--------------------------------+

The last two statements display the results shown because 2 is equal to neither 1 nor 0.

Personally I would suggest use tinyint as a preference, because boolean doesn't do what you think it does from the name, so it makes for potentially misleading code. But at a practical level, it really doesn't matter -- they both do the same thing, so you're not gaining or losing anything by using either.

Although this is a very thorough response, at the end you share your preference for `tinyint`, stating "boolean doesn't do what you think it does from the name". Can you elaborate? I find this answer very good on this (https://stackoverflow.com/a/47404140/2099911): "...(boolean) carries the semantic meaning of what you're trying to do". — Rafael Leite, Nov 25 '21 at 19:00

score 9 · Answer 4 · edited May 23 '17 at 12:10

9

use enum its the easy and fastest

i will not recommend enum or tinyint(1) as bit(1) needs only 1 bit for storing boolean value while tinyint(1) needs 8 bits.

ref

TINYINT vs ENUM(0, 1) for boolean values in MySQL

edited May 23 '17 at 12:10

Community

1
1

answered Sep 20 '10 at 13:27

Pramendra Gupta

14,667
4
33
34

1

We can't use enum as our database also needs to support sqlite – tom Sep 20 '10 at 13:32
15

If you are using InnoDB, bit ends up using just as much space as tinyint. From [High Performance MySQL](http://www.amazon.com/High-Performance-MySQL-Optimization-Replication/dp/1449314287/) (the percona guys) "InnoDB store[s] each [bit] column as the smallest integer type large enough to contain the bits, so you don't save any storage space." The only gain is if you are storing multiple boolean values in a BIT(morethan1) column. So if you only have one boolean field, using tinyint is the same as bit in InnoDB, and is preferable since tinyint is typically easier to work with. – billmalarky Apr 16 '13 at 18:59
Not true for MySQL: `BIT(M) - approximately (M+7)/8 bytes` see: https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html – Jens Jul 11 '18 at 08:37

score 8 · Answer 5 · answered Nov 21 '17 at 02:12

While it's true that bool and tinyint(1) are functionally identical, bool should be the preferred option because it carries the semantic meaning of what you're trying to do. Also, many ORMs will convert bool into your programing language's native boolean type.

score 0 · Answer 6 · answered Sep 04 '17 at 08:45

My experience when using Dapper to connect to MySQL is that it does matter. I changed a non nullable bit(1) to a nullable tinyint(1) by using the following script:

ALTER TABLE TableName MODIFY Setting BOOLEAN null;

Then Dapper started throwing Exceptions. I tried to look at the difference before and after the script. And noticed the bit(1) had changed to tinyint(1).

I then ran:

ALTER TABLE TableName CHANGE COLUMN Setting Setting BIT(1) NULL DEFAULT NULL;

Which solved the problem.

score 0 · Answer 7 · answered Feb 05 '21 at 22:59

Whenever you choose int or bool it matters especially when nullable column comes into play.

Imagine a product with multiple photos. How do you know which photo serves as a product cover? Well, we would use a column that indicates it.

So far out product_image table has two columns: product_id and is_cover

Cool? Not yet. Since the product can have only one cover we need to add a unique index on these two columns.

But wait, if these two column will get an unique index how would you store many non-cover images for the same product? The unique index would throw an error here.

So you may though "Okay, but you can use NULL value since these are ommited by unique index checks", and yes this is truth, but we are loosing linguistic rules here.

What is the purpose of NULL value in boolean type column? Is it "all", "any", or "no"? The null value in boolean column allows us to use the unique index, but it also messes up how we interpret the records.

I would tell that in some cases the integer can serve a better purpose since its not bound to strict true or false meaning

Boolean vs tinyint(1) for boolean values in MySQL

7 Answers7

Linked

Related