Boolean vs tinyint(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.

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.

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

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.

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

You can controll the behavor by add parameter to jdbc-url:

tinyInt1isBit=false

The tinyInt1isBit=false parameter in a JDBC URL is related to how MySQL handles the TINYINT(1) data type. Let’s break it down:

TINYINT(1):

• In MySQL, the TINYINT data type can store integer values from -128 to 127 (signed) or 0 to 255 (unsigned).

• When you define a TINYINT column with a length of 1 (e.g., TINYINT(1)), it is often used to represent boolean values (true or false).

• By default, when retrieving data from a TINYINT(1) column, MySQL maps it to a Java Boolean type (true or false).

If tinyInt1isBit is set to true (which is the default behavior), the driver converts TINYINT(1) columns to Java Boolean values. If you set tinyInt1isBit to false, the driver converts TINYINT(1) columns to Java Integer values instead of Boolean.

If you want to retrieve this value as an Integer (0 or 1) rather than a Boolean, you can add tinyInt1isBit=false to your JDBC URL. Here’s an example of how you might include this parameter in your JDBC URL:

jdbc:mysql://localhost:3306/mydb?tinyInt1isBit=false