intro
Let's find out everything you need to know about how to define, use, and deal with MySQL boolean columns.
As you may already know, MySQL does not offer a dedicated data type for boolean values. As a workaround, users generally implement it using TINYINT(1). What you may not know is that you can use the BOOL and BOOLEAN aliases to streamline MySQL boolean data handling.
In this guide, you will dig into the history of the BOOL data type in MySQL, explore how to use BOOLEAN in MySQL, and highlight when to use it effectively.
By the end of this tutorial, you will be a master of booleans in MySQL. True or false? True!
The History of the Bool Data Type in MySQL
First of all, you need to know that MySQL does not have a built-in, dedicated boolean data type. Instead, users have generally implemented it through the TINYINT(1) data type. This defines a cell that accepts integer values from -128 to 127 and displays only one digit when values of that type are printed as output. The idea is to use that data type to store boolean values where 0 is considered false and 1 is considered true.
Originally, in MySQL 5.0.3, the official documentation recommended implementing the boolean data type using BIT. In particular, the manual said:
As of MySQL 5.0.3, a BIT data type is available for storing bit-field values.
Up until MySQL 5.0.3, BIT was a synonym for TINYINT, while later versions of MySQL changed the implementation of BIT. Anyway, both still use only one byte of storage.
The problem was that BIT was supported only for the MyISAM storage engine in MySQL 5.0.3. Plus, most MySQL ORM (Object Relational Mapping) libraries did not recognize or could appropriately handle BIT columns. So, TINYINT(1) remained the best way to implement boolean values in MySQL.
For that reason, MySQL 5.5 introduced the BOOL or BOOLEAN types as aliases of TINYINT(1) and described them as follows:
Bool, Boolean: These types are synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true.
Since then, the bool data type in MySQL has been implemented by using the BOOL/BOOLEAN aliases or the TINYINT(1) directly.
How to Use the BOOLEAN Type in MySQL
As learned before, MySQL uses TINYINT(1) to represent the BOOLEAN data type. To make it easier to represent boolean columns, MySQL offers BOOLEAN or BOOL as synonyms for TINYINT(1).
So, you have two ways of defining a boolean column in MySQL.
The first one involves defining the column as a TINYINT(1) directly:
1
column_name TINYINT(1)
The second one is to specify the column as BOOL or BOOLEAN with this syntax:
1
column_name BOOL
2
-- or
3
column_name BOOLEAN
Under the hood, both produce the same result. However, the second approach makes the SQL DDL (Data Definition Language) query easier to read and maintain. This is because the BOOL/BOOLEAN keyword clearly identifies the column as a boolean, while TINYINT(1) does not clearly indicate that the column will be used for that purpose.
Keep in mind that the convention in MySQL is that 0 is considered false, while any other non-zero integer value is considered true. You can verify this through the following query:
1
SELECT
2
IF(0, 'true', 'false'),
3
IF(1, 'true', 'false'),
4
IF(2, 'true', 'false'),
5
IF(-1, 'true', 'false');
Execute the above query in a powerful MySQL database client like DbVisualizer, and you will get the following result:
When working with the MySQL boolean type, do not forget that the literals TRUE and FALSE evaluate to 1 and 0, respectively. These constant names can be written in any letter case, as shown in this query:
1
SELECT true, false, TRUE, FALSE, True, False;
The output will be:
Complete Boolean MySQL Example
Let’s see how to use the BOOL data type in MySQL through a complete example.
Assume you have an e-learning platform and want to provide courses to your users. Create a table where to keep the information about these courses, with a special boolean column to decide whether a course is active or not:
1
CREATE TABLE courses (
2
id INT AUTO_INCREMENT PRIMARY KEY,
3
name VARCHAR(100) NOT NULL,
4
description TEXT,
5
is_active BOOLEAN
6
);
Equivalently, you can write the above CREATE TABLE query as below:
1
CREATE TABLE courses (
2
id INT AUTO_INCREMENT PRIMARY KEY,
3
name VARCHAR(100) NOT NULL,
4
description TEXT,
5
is_active BOOL
6
);
Or you can write the same query as follows:
1
CREATE TABLE courses (
2
id INT AUTO_INCREMENT PRIMARY KEY,
3
name VARCHAR(100) NOT NULL,
4
description TEXT,
5
is_active TINYINT(1)
6
);
Now you may be wondering, what is the DDL query that the database associates with the course table? Thanks to DbVisualizer's DDL discovery feature, you can find that out by opening the courses table in a new tab and reaching the "DDL" section. There, you will see:
As you can tell, the database converted the BOOLEAN type in MySQL to a TINYINT(1):
1
CREATE TABLE
2
courses
3
(
4
id INT NOT NULL AUTO_INCREMENT,
5
name VARCHAR(100) NOT NULL,
6
description text,
7
is_active TINYINT(1),
8
PRIMARY KEY (id)
9
)
10
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_0900_ai_ci;
That means the is_active column actually accepts integer values from -128 to 127 and not only the TRUE and FALSE literals. Verify that with the following query:
1
INSERT INTO courses (course_name, description, is_active)
2
VALUES
3
('Python Programming Essentials', 'Learn fundamental concepts and syntax of Python programming language', 0),
4
('Web Development Bootcamp', 'Master HTML, CSS, and JavaScript to build dynamic and responsive websites', TRUE),
5
('Data Science with R', 'Explore data analysis, visualization, and machine learning with R programming language', 3),
6
('Java for Beginners', 'Begin your journey into Java programming with basic syntax and object-oriented principles', 1),
7
('Introduction to SQL', 'Learn SQL fundamentals to manage and query relational databases efficiently', FALSE);
The above MySQL query works like a charm and inserts 5 rows into the courses table:
The rows have been successfully added to the table
If you now retrieve the raw data in the courses table, you will see:
Note the integer values in the is_active column. Keep in mind that DbVisualizer is able to configure the MySQL 8.0 driver so that it automatically converts TINTYINT(1) values to booleans for easier reading:
DbVisualizer can automatically convert TINYINT(1) values to booleans
MySQL Boolean Operators
Dealing with MySQL data type boolean values can be tricky if you do not use the right operators. Learn more about that!
Note: In this section, we will refer to the courses table defined in the previous chapter.
Equality Operator
Suppose you want to retrieve all active courses. The query you may end up writing is:
1
SELECT *
2
FROM courses
3
WHERE is_active = TRUE;
That would produce the following result:
As you can see, it retrieved only records where is_active contains 1. That is because TRUE is just an alias for 1 in MySQL. To avoid such issues, you need to use the IS operator.
IS Operator
Consider you want to get all active courses. You can achieve that by using IS as follows:
1
SELECT *
2
FROM courses
3
WHERE is_active IS TRUE;
This time, the resulting record set contains all active courses:
That’s because IS TRUE tests all non-zero values to true and 0 to false, as desired.
Best Practices for the MYSQL Boolean Type
Here are some of the most important best practices to apply when dealing with the boolean type in MySQL:
Conclusion
In this guide, you learned the history behind the BOOL data type in MySQL. Then, you saw how to implement boolean in MySQL by using the BOOL and BOOLEAN aliases for TINYINT(1). Thanks to the examples shown here, you also understood when to use the MySQL BOOLEAN type in real-world scenarios.
As shown here, dealing with boolean values in MySQL becomes easier with a powerful database client like DbVisualizer. This comprehensive database client supports several DBMS technologies, has advanced query optimization capabilities, and can generate ERD-type schemas with a single click. Try DbVisualizer for free!
FAQ
Is there a BOOLEAN type in MySQL?
No, MySQL does not have a dedicated BOOLEAN data type. Instead, it uses TINYINT(1) or aliases BOOL or BOOLEAN to represent boolean values. The idea behind these types is to store 0 for false and any non-zero integer value for true. On the contrary, PostgreSQL offers a real BOOLEAN data type. Learn more in our guide on PostgreSQL data types.
What is the most popular data type for implementing boolean in MySQL?
The most popular data type for implementing boolean in MySQL is TINYINT(1). This is commonly used due to its efficient storage (1-byte integers), clear interpretation (0 for false, non-zero for true), and compatibility with several ORM technologies.
What are the MySQL boolean literals?
The MySQL boolean literals are TRUE and FALSE. These literals can be written in any letter case and represent boolean values of 1 (true) and 0 (false) respectively.
Is the MySQL BOOLEAN data type a real dedicated type?
No, the MySQL BOOLEAN data type is not a dedicated type. It is implemented as an alias for TINYINT(1), which means it uses the same underlying storage and behaviors as TINYINT(1) columns in MySQL, rather than being a distinct data type.
What is the difference between BOOL and BOOLEAN in MySQL?
In MySQL, both BOOL and BOOLEAN are synonyms for TINYINT(1). So, they have no functional difference and can be used interchangeably to represent boolean values in database columns.
