An Introduction to MySQL BOOLEAN Data Type
Summary: this tutorial shows you how to use MySQL BOOLEAN data type to store Boolean values, true and false.
Introduction to MySQL BOOLEAN data type
MySQL does not have built-in Boolean type. However, it uses TINYINT(1)
instead. To make it more convenient, MySQL provides BOOLEAN
or BOOL
as the synonym of TINYINT(1)
.
In MySQL, zero is considered as false, and non-zero value is considered as true. To use Boolean literals, you use the constants TRUE
and FALSE
that evaluate to 1 and 0 respectively. See the following example:
SELECT true, false, TRUE, FALSE, True, False;
-- 1 0 1 0 1 0
Code language: SQL (Structured Query Language) (sql)
MySQL BOOLEAN example
MySQL stores Boolean value in the table as an integer. To demonstrate this, let’s look at the following tasks
table:
CREATE TABLE tasks (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
completed BOOLEAN
);
Code language: SQL (Structured Query Language) (sql)
Even though we specified the completed column as BOOLEAN
, when we show the table definition, it is TINYINT(1)
as follows:
DESCRIBE tasks;
Code language: SQL (Structured Query Language) (sql)
The following statement inserts 2 rows into the tasks
table:
INSERT INTO tasks(title,completed)
VALUES('Master MySQL Boolean type',true),
('Design database table',false);
Code language: SQL (Structured Query Language) (sql)
Before saving data into the Boolean column, MySQL converts it into 1 or 0. The following query retrieves data from tasks
table:
SELECT
id, title, completed
FROM
tasks;
Code language: SQL (Structured Query Language) (sql)
As you see, the true
and false
were converted to 1 and 0.
Because Boolean is TINYINT(1)
, you can insert value other than 1 and 0 into the Boolean column. Consider the following example:
INSERT INTO tasks(title,completed)
VALUES('Test Boolean with a number',2);
Code language: SQL (Structured Query Language) (sql)
It is working fine.
If you want to output the result as true
and false
, you can use the IF
function as follows:
SELECT
id,
title,
IF(completed, 'true', 'false') completed
FROM
tasks;
Code language: SQL (Structured Query Language) (sql)
MySQL BOOLEAN operators
To get all completed tasks in the tasks
table, you might come up with the following query:
SELECT
id, title, completed
FROM
tasks
WHERE
completed = TRUE;
Code language: SQL (Structured Query Language) (sql)
As you see, it only returned the task with completed
value 1. To fix it, you must use IS
operator:
SELECT
id, title, completed
FROM
tasks
WHERE
completed IS TRUE;
Code language: SQL (Structured Query Language) (sql)
In this example, we used the IS
operator to test a value against a Boolean value.
To get the pending tasks, you use IS FALSE
or IS NOT TRUE
as follows:
SELECT
id, title, completed
FROM
tasks
WHERE
completed IS NOT TRUE
Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned how to use the MySQL BOOLEAN
data type, which is the synonym of TINYINT(1)
, and how to manipulate Boolean values.