MySQL

A Complete Guide to the MySQL Boolean Type

intro

Let's find out everything you need to know about how to define, use, and deal with MySQL boolean columns.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

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:

Copy
        
1 column_name TINYINT(1)

The second one is to specify the column as BOOL or BOOLEAN with this syntax:

Copy
        
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:

Copy
        
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:

Note how 0 is false and 1, 2, and -1 are true
Note how 0 is false and 1, 2, and -1 are true

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:

Copy
        
1 SELECT true, false, TRUE, FALSE, True, False;

The output will be:

Note that the letter case is not important for the result
Note that the letter case is not important for the result

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:

Copy
        
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:

Copy
        
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:

Copy
        
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:

The DDL of the courses table as retrieved by DbVisualizer
The DDL of the courses table as retrieved by DbVisualizer

As you can tell, the database converted the BOOLEAN type in MySQL to a TINYINT(1):

Copy
        
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:

Copy
        
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
The rows have been successfully added to the 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 values in the is_active column
Note the values in the is_active column

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
DbVisualizer can automatically convert TINYINT(1) values to booleans

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:

Copy
        
1 SELECT * 2 FROM courses 3 WHERE is_active = TRUE;

That would produce the following result:

Note that the is_active equals to 1 in both records
Note that the is_active equals to 1 in both records

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:

Copy
        
1 SELECT * 2 FROM courses 3 WHERE is_active IS TRUE;

This time, the resulting record set contains all active courses:

Note that is_active is true in all records
Note that is_active is true in all records

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:

  1. Choose either TINYINT(1) or BOOL/BOOLEAN for boolean columns across your database schema and stick with it for consistency.
  2. Avoid exotic implementation for boolean in MySQL like CHAR(1) and values like 'Y'/'N' or 'T'/'F'.
  3. Use descriptive names for boolean columns, such as is_active, has_permission, or is_approved.
  4. Set appropriate default values (0 or 1) for boolean columns based on your application's logic to ensure expected behavior when a value is not explicitly provided.
  5. Use the boolean literals TRUE and FALSE in your queries to maintain clarity and readability.
  6. Ensure that your ORM library properly maps boolean columns to boolean values in your programming language.
  7. Use the IS operator instead of = operator when checking for boolean values using the TRUE and FALSE literals.

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.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL EXPLAIN: The Definitive Tool to Optimize Queries

author Antonello Zanini tags MySQL POSTGRESQL SQL 12 min 2024-10-29
title

Everything You Need to Know About SQL Constraints: The What, Why, and How

author Lukas Vileikis tags BEGINNER MySQL SQL 8 min 2024-10-28
title

Changing the root Password in MySQL: A Guide

author Lukas Vileikis tags MySQL SECURITY 6 min 2024-10-22
title

Database Replication in MySQL: Ensuring Data Consistency and Availability

author Ochuko Onojakpor tags Database replication MySQL 12 min 2024-10-21
title

MySQL Backup and Recovery Best Practices: A Guide

author Lukas Vileikis tags BIG DATA MySQL OPTIMIZATION SECURITY SQL 7 min 2024-10-15
title

SQL Not Equal Operator: Definitive Guide with Examples

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-10-14
title

MySQL DISTINCT Clause Explained: Why, How & When

author Lukas Vileikis tags BEGINNER MySQL SQL 7 min 2024-10-10
title

Counter in MySQL: Counting Rows with Ease

author Lukas Vileikis tags MySQL SQL 8 min 2024-10-03
title

SQL OFFSET: Skipping Rows in a Query

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-09-30
title

The MySQL RENAME COLUMN Statement: How, What, and Why

author Lukas Vileikis tags MySQL SQL 6 min 2024-09-26

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗