MySQL BOOL, BOOLEAN – A Complete Guide

In this tutorial, we will learn the MySQL data type BOOL and BOOLEAN. We will also be learning how to implement boolean in the queries and some exceptions you must know. We will also go through some simple and practical examples to understand the BOOL and BOOLEAN data types.

What is BOOLEAN data type in MySQL?

The boolean data type is present in almost every programming language, and we all know that it tells us either “true” or “false”. The true is represented by 1 or any value apart from 0, and the false is represented by 0.

In MySQL, you have three options to set the data type of column as boolean. You can use built-in data types for columns such as BOOL, BOOLEAN, and BIT.

When you use BOOL and BOOLEAN, it automatically sets the TINYINT(1) data type to the column. So, you can say that BOOL and BOOLEAN are the aliases for TINYINT, and all three can be used alternatively.

The BIT data type can also be used to represent the boolean values in MySQL. When using a BIT data type for storing boolean values, you can proceed without defining the data type’s range because MySQL will automatically create a column of type BIT(1), which will allow you to store the values 1 and 0.

Now, let’s dive into the examples which will help you understand the boolean concept in MySQL very easily.

Examples of boolean in MySQL

First, we will create a table using BOOL and BOOLEAN data types and insert values in it. Then we will also see the table description, so we will get what the datatype of a column is.

First, create a table of name MySQLBOOL –

CREATE TABLE MySQLBOOL(boolean_col BOOLEAN, bool_col BOOL);Code language: SQL (Structured Query Language) (sql)

As stated earlier, the BOOL and BOOLEAN data types are synonyms for TINYINT(1), so the generated columns would have the data type as TINYINT(1). Check below –

DESC MySQLBOOL;Code language: SQL (Structured Query Language) (sql)

As you can see, both columns have the same data type, the TINYINT(1), because MySQL doesn’t have any implementation for full boolean type handling.

Now, let’s insert some values in the table. Note that because the column type is TINYINT, you can store any number from -128 to 127 in the table.

Every other value apart from the acceptable range or type will be either capped to the maximum possible value or set to be 0. We will see all the possible cases down below.

Note – You can use “true” and “false” while inserting the values in the table without a quotation so that the value won’t be supposed to as a string. If you insert a value with the quotes, the value is considered as a string and converted back to 0.

INSERT INTO MySQLBOOL VALUES (true, 0);Code language: SQL (Structured Query Language) (sql)

Now, let’s see the values in the table –

SELECT * FROM MySQLBOOL;Code language: SQL (Structured Query Language) (sql)

You can see, we have inserted the value “true,” but it gets stored as 1. Now, let’s try inserting other values.

 INSERT INTO MySQLBOOL VALUES(false, 1);Code language: SQL (Structured Query Language) (sql)

Let’s check values in the table now-

SELECT * FROM MySQLBOOL;Code language: SQL (Structured Query Language) (sql)

Here you can see, “false” is converted to the 0 without any explicit conversion.

Inserting String or Incorrect Values in BOOL

Now, let’s try inserting wrong values like string or out of the range.

INSERT INTO MySQLBOOL VALUES ("true", 10);Code language: SQL (Structured Query Language) (sql)

Here, we are trying to insert a “true,” but if you can see here, it’s a string and not a boolean. Along with it, we are inserting value 10, which is within the range of TINYINT.

SELECT * FROM MySQLBOOL;Code language: SQL (Structured Query Language) (sql)

When you try to insert such values, you get ended up inserting a new row in a table with a warning on the console. Because the string values are not permitted in the TINYINT column, it gets stored as a 0.

Inserting Out Of Range BOOL Values

What will happen if we insert an out-of-range value in the table? Will it be converted to 0? Let’s check –

 INSERT INTO MySQLBOOL VALUES(300, 200);Code language: SQL (Structured Query Language) (sql)

Here, we are inserting values greater than 127 (range of TINYINT). Let’s see the result of what gets inserted!

SELECT * FROM MySQLBOOL;Code language: SQL (Structured Query Language) (sql)

Unlike string values, the integer values get capped to 127 because that’s the maximum possible value a TINYINT data type can hold.

This way, you can use the BOOL and BOOLEAN data types in MySQL.

Fetching BOOL Values Using MySQL IF()

To fetch the table/column values as true or false, you can use an IF statement. Check below-

SELECT IF(boolean_col, 'true','false') AS boolean_col, IF(bool_col, 'true','false') AS bool_col FROM MySQLBOOL;Code language: SQL (Structured Query Language) (sql)

This query will return false for all the 0s and true for every value other than 0 from your table. Using this way, you won’t need to write any extra conditional statements in your program.

You might want to return all the “true” values from the rows in some cases. But, your table might contain non-negative values, which indicates ‘true’. In this situation, you can use the WHERE clause with the ‘is’ operator instead of the ‘=’ operator. Check bellow-

SELECT * FROM MySQLBOOL WHERE boolean_col is true;Code language: SQL (Structured Query Language) (sql)

As you can see, we get all the rows from the table in which the values from the boolean_col column are true (non-zero).

Conclusion

We have learned what BOOL and BOOLEAN data types in MySQL are and how to use them in the queries. Apart from the given examples, you can try other methods as well to achieve the same or different results depending on the problem statement. MySQL has stated, “We intend to implement full boolean type handling, in accordance with standard SQL, in a future MySQL release”, but it really is not necessary because we can implement boolean in various ways. We highly recommend playing with different data types in MySQL. See you next tutorial!

References

MySQL official documentation of mapping available data type and actual data type.

Stackoverflow thread on- different methods to store boolean values in MySQL.