[sql] Boolean vs tinyint(1) for boolean values in MySQL

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

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.

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 isn't a distinct datatype in MySQL; it's just a synonym for tinyint. See this page in the MySQL manual.

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.

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

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.

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.