MySQL BOOLEAN: TINYINT(1), TRUE/FALSE, and CHECK Constraints

MySQL BOOLEAN and BOOL are aliases for TINYINT(1). The values TRUE and FALSE are aliases for 1 and 0, but the alias alone does not restrict stored values to just those two numbers.

Syntax

The syntax for creating a BOOLEAN data type is as follows:

column_name BOOLEAN

where column_name is the name of the column to be created.

Use Cases

The BOOLEAN data type is commonly used for storing logical values, such as switch status, completion status, etc.

Examples

Use a BOOLEAN column for a true/false attribute:

Assuming we have a table called employees that contains employee ID, name, and employment status. We can create the table using the following SQL statement:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    is_employed BOOLEAN
);

Then, we can insert some data:

INSERT INTO employees VALUES
(1, 'Alice', TRUE),
(2, 'Bob', FALSE),
(3, 'Charlie', TRUE);

Next, we can query the employees table to see the employment status of each employee:

SELECT name, is_employed FROM employees;

The query result will be:

+---------+-------------+
| name    | is_employed |
+---------+-------------+
| Alice   | 1           |
| Bob     | 0           |
| Charlie | 1           |
+---------+-------------+

Note that MySQL converts TRUE to 1 and FALSE to 0.

= TRUE compares a value with 1, while IS TRUE tests whether it is nonzero. For example, a stored value of 2 does not match value = TRUE, but it does match value IS TRUE:

SELECT value,
       value = TRUE AS equals_true,
       value IS TRUE AS is_true
FROM (
    SELECT 0 AS value
    UNION ALL SELECT 1
    UNION ALL SELECT 2
    UNION ALL SELECT NULL
) AS sample_values;

Enforce only 0 and 1

To enforce only two values, use a CHECK constraint in MySQL 8.0.16 or later:

CREATE TABLE feature_flags (
    enabled BOOLEAN NOT NULL CHECK (enabled IN (0, 1))
);

Earlier MySQL versions parse but ignore CHECK constraints, so validate the rule in the application when supporting those versions. See MySQL’s numeric type syntax and CHECK constraint documentation.

Without a constraint, values such as 2 are valid integers and evaluate as true in a Boolean context. IS TRUE tests truthiness, while = TRUE compares with 1.