An Introduction to MySQL BOOLEAN Data Type

MySQL does not have a native BOOLEAN data type, but it allows you to simulate BOOLEAN behavior using the TINYINT(1) data type. This article will explain how BOOLEAN works in MySQL and its best practices.

1. MySQL BOOLEAN Data Type Overview

MySQL does not have a dedicated BOOLEAN data type.
Instead, BOOLEAN is an alias for TINYINT(1), meaning it can store values 0 (false) and 1 (true).
However, MySQL does not enforce strict boolean constraints, meaning you can store values other than 0 and 1 in a BOOLEAN column.

BOOLEAN is Just an Alias for TINYINT(1)

You can declare a column as BOOLEAN, but internally, it is stored as TINYINT(1).

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    is_active BOOLEAN   -- This is actually stored as TINYINT(1)
);

This is equivalent to:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    is_active TINYINT(1)   -- Stores 0 (false) or 1 (true)
);

2. Using BOOLEAN in MySQL

Inserting Boolean Values

You can insert values using TRUE and FALSE, which MySQL automatically converts to 1 and 0.

INSERT INTO users (is_active) VALUES (TRUE);   -- Stored as 1
INSERT INTO users (is_active) VALUES (FALSE);  -- Stored as 0

✔ TRUE is converted to 1
✔ FALSE is converted to 0

Retrieving Boolean Values

SELECT id, is_active FROM users;

✔ The column will return integer values (0 or 1).

3. Checking and Filtering Boolean Values

Using Boolean Conditions in Queries

SELECT * FROM users WHERE is_active = TRUE;
SELECT * FROM users WHERE is_active = FALSE;

✔ TRUE is equivalent to 1, and FALSE is equivalent to 0.

Handling Unexpected Values

Since TINYINT(1) can store any number between -128 and 127, you should enforce constraints to ensure only 0 or 1 is stored.

ALTER TABLE users ADD CONSTRAINT chk_is_active CHECK (is_active IN (0,1));

✔ Prevents invalid values like 2, -1, or 99 from being inserted.

4. BOOLEAN vs. BIT(1) in MySQL

Data Type	Storage	Values Allowed	Recommended For
BOOLEAN (TINYINT(1))	1 byte	0, 1 (but can store other values)	General true/false storage
BIT(1)	1 bit (stored in a byte)	Strict 0 or 1	Memory-efficient flag storage

✔ Use BOOLEAN for readability (easier to work with in SQL queries).
✔ Use BIT(1) when strict storage and efficiency are required.

5. Best Practices for Using BOOLEAN in MySQL

✅ Use BOOLEAN for true/false values instead of ENUM or VARCHAR.
✅ Ensure constraints (CHECK constraint) are applied to restrict values to 0 and 1.
✅ Use BIT(1) when strict 0/1 storage is needed for space efficiency.
✅ Use indexing if querying boolean values frequently (INDEX(is_active)).

6. Summary

✔ BOOLEAN is stored as TINYINT(1) (0 = FALSE, 1 = TRUE).
✔ MySQL does not enforce strict boolean constraints, so additional constraints may be needed.
✔ Use BIT(1) for memory-efficient storage when needed.

Would you like performance comparisons between BOOLEAN and other data types? 🚀

Souy Soeng

An Introduction to MySQL BOOLEAN Data Type