Which MySQL Data Type Is Best for Storing Boolean Values?

1. Overview

Storing Boolean values is a basic requirement in most database-driven applications. We often use them to track user preferences, feature toggles, or permissions in a table. So, learning how to reliably store binary logic in MySQL can be critical.

This might seem obvious, but MySQL doesn’t offer a true Boolean data type in the traditional sense. Instead, it uses workarounds that may be confusing and lead to unexpected behavior if misunderstood. Yet, choosing the right data type ensures efficiency and compatibility across applications and environments.

In this tutorial, we’ll explore how MySQL represents Boolean values, what data types we can use instead, and which option makes the most sense for storing true/false data.

2. MySQL Boolean Type Support

In short, yes – we can define columns as BOOLEAN or BOOL. However, unlike other database management systems (DBMS), MySQL considers Boolean an alias for TINYINT(1).

This means, when we define a column as BOOLEAN, MySQL actually creates a TINYINT(1) column that can store values from -128 to 127 (if signed) or from 0 to 255  (if unsigned). Under the hood, MySQL converts FALSE to 0 and TRUE to 1.

Considering this, let’s create a sample users table and insert some data:

CREATE TABLE Users (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  is_active BOOL not null
);

-- insert
INSERT INTO Users VALUES (1, 'Zack', 'Sales', TRUE);
INSERT INTO Users VALUES (2, 'Mark', 'Marketing', FALSE);

Next, we query this table using SELECT and WHERE to retrieve the active users:

SELECT * FROM USERS WHERE is_active is true;
Output:
+-------+------+-------+-----------+
| empId | name | dept  | is_active |
+-------+------+-------+-----------+
|     1 | Zack | Sales |         1 |
+-------+------+-------+-----------+

Thus, we can notice that MySQL indeed stores 1 instead of TRUE.

Of course, we can also do the opposite, and instead of using the Boolean values TRUE and FALSE, we can just type their respective numeric values 1 or 0:

INSERT INTO Users VALUES (1, 'Zack', 'Sales', 1);
     -- OR
SELECT * FROM Users WHERE is_active = 1;

This returns the same result. Both approaches work the same way, so the choice often comes down to code readability.

3. Use TINYINT Directly

Now that we understand how MySQL handles the BOOLEAN data type, we can simply use TINYINT(1) directly instead.

Let’s employ ALTER TABLE to update the USERS table to redefine the is_active column as TINYINT(1) instead of BOOL:

ALTER TABLE Users
MODIFY COLUMN is_active TINYINT(1);

Now, if we rerun the same tests from the previous example, we get identical results. This confirms that we can use BOOLEAN and TINYINT(1) interchangeably in MySQL.

Notably, MySQL can convert Boolean values to and from other integer types like INT, SMALLINT, or BIGINT in the same way. That said, TINYINT(1) remains the most space-efficient choice.

4. Use BIT With 0 or 1

A Boolean represents two possible values (true or false), and MySQL offers a way to store this binary logic with BIT. Technically, BIT(1) can store a single binary value (0 or 1), making it a natural fit for representing Boolean values. However, it’s often less practical in real-world use cases.

Let’s start by updating the Users table to define the is_active column as BIT(1):

ALTER TABLE Users
MODIFY COLUMN is_active BIT(1) NOT NULL;

Next, we add some records:

INSERT INTO USERS VALUES (1, 'Zack', 'Sales', b'1');
INSERT INTO USERS VALUES (2, 'Mike', 'HR', b'0');

Here, the b’value’ notation explicitly tells MySQL we’re inserting a binary literal with a value that contains only 0 and 1.

However, there’s another catch with BIT: by default, MySQL may display BIT values as non-printable characters or binary blob:

SELECT empId, name, dept, is_active FROM Users;
+-------+------+-------+-----------+
| empId | name | dept  | is_active |
+-------+------+-------+-----------+
|     1 | Zack | Sales |           |
|     2 | Mike | HR    |           |
+-------+------+-------+-----------+

So, if we want to retrieve BIT values as visible zeroes and ones, we need to explicitly convert them into a readable format:

SELECT empId, name, dept, BIN(is_active) AS is_active_bin
FROM Users;
+-------+------+-------+---------------+
| empId | name | dept  | is_active_bin |
+-------+------+-------+---------------+
|     1 | Zack | Sales |             1 |
+-------+------+-------+---------------+

In the above query, we use the BIN() function to see the binary representation as a string.

Alternatively, another option is to use CAST():

SELECT empId, name, dept, CAST(is_active AS UNSIGNED) AS is_active_num FROM Users;

The CAST function displays BIT(1) values as numeric 0 or 1. It converts the binary data into unsigned integers.

In practice, BIT can be tricky and even inconsistent across platforms. For example, many developers report normal behavior on Windows but issues on Linux systems. Furthermore, BIT(1) still takes up a full byte, just like TINYINT(1). Considering that, it’s simpler to just use TINYINT(1).

5. Use ENUM With Custom Values

What if we want to use custom Boolean-like values similar to what’s possible in other DBMSs? We can rely on ENUM for that. It enables us to define a specific set of values for a column, like yes and no.

As usual, let’s update the Users table and change the type of the is_active column to ENUM:

ALTER TABLE Users 
MODIFY COLUMN is_active ENUM('yes', 'no') NOT NULL;

After this change, the is_active column accepts either yes or no values. Additionally, MySQL can also take these values as numeric indexes (yes as 1, no as 2).

Let’s insert two rows of data and query the table:

INSERT INTO Users VALUES (1, 'Nick', 'Accounting', yes);
INSERT INTO Users VALUES (2, 'John', 'Marketing', 2);
SELECT * FROM Users
+-------+------+-----------+------------+
| empId | name | dept       | is_active
+-------+------+-----------+------------+
|     1 | Nick | Accounting | yes       |
|     2 | John | Marketing  | no        |
+-------+------+------------+-----------+
Here, the first row uses the defined ENUM value directly (yes). The second row uses its numeric index (2), which MySQL automatically maps to the corresponding label (no). Basically, this shows that we can query ENUM values either by label or by index.

6. Conclusion

In this article, we explored how MySQL interprets Boolean values and the most common data types available to represent true and false logic. Specifically, we found that MySQL uses BOOL and TINYINT(1) interchangeably, learned how to work with the BIT type, and applied ENUM for custom true and false labels.

In conclusion, although we can use multiple data types to represent Boolean values, TINYINT(1) remains the most reliable, space-efficient choice.