MySQL BOOLEAN: How True and False Really Work

MySQL does not have a true boolean type. BOOLEAN and BOOL are just other names for TINYINT(1). TRUE is 1 and FALSE is 0.

query.sql

SQL

CREATE TABLE tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(50) NOT NULL,
    is_done BOOLEAN NOT NULL DEFAULT FALSE
);
INSERT INTO tasks (title, is_done) VALUES ('Write report', TRUE), ('Send invoice', FALSE), ('Call Asha', 0);
SELECT * FROM tasks;

Output

OUTPUT

+----+--------------+---------+
| id | title        | is_done |
+----+--------------+---------+
|  1 | Write report |       1 |
|  2 | Send invoice |       0 |
|  3 | Call Asha    |       0 |
+----+--------------+---------+
3 rows in set

The values come back as 1 and 0. Most programming languages map them back to true and false (PHP’s PDO returns the number or a string unless you convert it).

Querying it

query.sql

SQL

1
2
3

SELECT title FROM tasks WHERE is_done;
SELECT title FROM tasks WHERE NOT is_done;
SELECT title FROM tasks WHERE is_done = TRUE;

Output

OUTPUT

+--------------+
| title        |
+--------------+
| Write report |
+--------------+
1 row in set

+--------------+
| title        |
+--------------+
| Send invoice |
| Call Asha    |
+--------------+
2 rows in set

+--------------+
| title        |
+--------------+
| Write report |
+--------------+
1 row in set

WHERE is_done and WHERE is_done = TRUE both work. A nuance: TRUE means exactly 1. Because a TINYINT can hold other numbers, is_done = 2 is neither true nor false in the sense of = TRUE:

query.sql

SQL

SELECT 2 = TRUE AS two_equals_true, 2 IS TRUE AS two_is_true;

Output

OUTPUT

+-----------------+-------------+
| two_equals_true | two_is_true |
+-----------------+-------------+
|               0 |           1 |
+-----------------+-------------+
1 row in set

IS TRUE treats any non-zero value as true. = TRUE compares with the number 1. To be safe, store only 0 and 1. A CHECK constraint can enforce it (CHECK Constraints).

Three states: true, false and NULL

A nullable boolean has three values. This is useful when “not answered yet” is a real state:

query.sql

SQL

1
2
3

CREATE TABLE survey (id INT, likes_tea BOOLEAN NULL);
INSERT INTO survey VALUES (1, TRUE), (2, FALSE), (3, NULL);
SELECT id, likes_tea, likes_tea IS NULL AS not_answered FROM survey;

Output

OUTPUT

+------+-----------+--------------+
| id   | likes_tea | not_answered |
+------+-----------+--------------+
|    1 |         1 |            0 |
|    2 |         0 |            0 |
|    3 |      NULL |            1 |
+------+-----------+--------------+
3 rows in set

If “unknown” must not exist, make the column NOT NULL with a default.

Naming flags

Give boolean columns names that read like a question: is_active, has_paid, can_login. Avoid status for a yes/no value.

What not to do

Do not use 'Y'/'N' or 'yes'/'no' text. They need more space, allow typos ('yes ', 'Yes', 'y') and compare slower.
Do not add many boolean flags that really describe one state. If a task can be draft, published or archived, use one status column (ENUM and SET) or a small lookup table, instead of is_draft, is_published and is_archived that can contradict each other.

Many flags in one column: SET and BIT

When you really have a lot of independent flags, MySQL offers BIT(n) and SET. They save space but are harder to query and to change. Start with plain booleans.

info

MariaDB and others

PostgreSQL has a real BOOLEAN type that returns true and false. A MySQL driver may return 1 and 0. If your application expects real booleans, convert explicitly.