Boolean vs tinyint(1) for boolean values in MySQL
SqlMysqlSql Problem Overview
What column type is best to use in a MySQL database for boolean values? I use boolean
but my colleague uses tinyint(1)
.
Sql Solutions
Solution 1 - Sql
These data types are synonyms.
Solution 2 - Sql
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
.
Solution 3 - Sql
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.
Solution 4 - Sql
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
https://stackoverflow.com/questions/3546186/tinyint-vs-enum0-1-for-boolean-values-in-mysql
Solution 5 - Sql
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.
Solution 6 - Sql
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.
Solution 7 - Sql
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