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.