(explain to you how MySQL handles Boolean values by using TINYINT(1) internally.)
Introduction to MySQL BOOLEAN data type
MySQL does not have a dedicated Boolean data type. Instead, MySQL uses TINYINT(1) to represent the BOOLEAN data type.
To make it more convenient when defining BOOLEAN column, MySQL offers BOOLEAN or BOOL as the synonym for TINYINT(1).
So instead of defining a BOOLEAN column like this:
column_name TINYINT(1)You can use the BOOL or BOOLEAN keyword as follows:
column_name BOOLIn MySQL, the convention is that zero is considered false, while a non-zero value is considered true.
When working with Boolean literals, you can use the constants true and false case-insensitively, which is equivalent to 1 and 0 respectively. For example:
SELECT true, false, TRUE, FALSE, True, False;Output:
1 0 1 0 1 0MySQL BOOLEAN example
We’ll take an example of using the MySQL BOOLEAN data type.
First, create a new table called tasks:
CREATE TABLE tasks (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
completed BOOLEAN
);The tasks table has three columns id, title, and completed.
The completed is a BOOLEAN column. Since the BOOLEAN is a synonym for TINYINT(1), when you describe the table structure, MySQL shows the TINYINT(1) instead:
DESCRIBE tasks;Output:
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| title | varchar(255) | NO | | NULL | |
| completed | tinyint(1) | YES | | NULL | |
+-----------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)Code language: PHP (php)Second, insert two rows into the tasks table:
INSERT INTO
tasks(title, completed)
VALUES
('Master MySQL Boolean type', true),
('Design database table', false);Before saving data into the Boolean column, MySQL converts it into 1 or 0.
Third, retrieve data from tasks table:
SELECT
id,
title,
completed
FROM
tasks;Output:
+----+---------------------------+-----------+
| id | title | completed |
+----+---------------------------+-----------+
| 1 | Master MySQL Boolean type | 1 |
| 2 | Design database table | 0 |
+----+---------------------------+-----------+
2 rows in set (0.00 sec)Code language: JavaScript (javascript)The output indicates that MySQL converted the true and false to 1 and 0 respectively.
Fourth, because BOOLEAN is TINYINT(1), you can insert values other than 1 and 0 into the BOOLEAN column. For example:
INSERT INTO tasks(title, completed)
VALUES
('Test Boolean with a number', 8);Output:
Query OK, 1 row affected (0.01 sec)Fifth, query data from the tasks table:
SELECT * FROM tasks;Output:
+----+----------------------------+-----------+
| id | title | completed |
+----+----------------------------+-----------+
| 1 | Master MySQL Boolean type | 1 |
| 2 | Design database table | 0 |
| 3 | Test Boolean with a number | 8 |
+----+----------------------------+-----------+
3 rows in set (0.00 sec)Code language: JavaScript (javascript)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;Output:
+----+----------------------------+-----------+
| id | title | completed |
+----+----------------------------+-----------+
| 1 | Master MySQL Boolean type | true |
| 2 | Design database table | false |
| 3 | Test Boolean with a number | true |
+----+----------------------------+-----------+
3 rows in set (0.00 sec)Code language: JavaScript (javascript)Sixth, insert NULL into the completed column:
INSERT INTO tasks(title, completed)
VALUES
('Test Boolean with NULL', NULL);Finally, retrieve data from the tasks table:
SELECT * FROM tasks;Output:
+----+----------------------------+-----------+
| id | title | completed |
+----+----------------------------+-----------+
| 1 | Master MySQL Boolean type | 1 |
| 2 | Design database table | 0 |
| 3 | Test Boolean with a number | 8 |
| 4 | Test Boolean with NULL | NULL |
+----+----------------------------+-----------+
4 rows in set (0.00 sec)Code language: JavaScript (javascript)MySQL BOOLEAN operators
To retrieve all completed tasks from 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)Output:
+----+---------------------------+-----------+
| id | title | completed |
+----+---------------------------+-----------+
| 1 | Master MySQL Boolean type | 1 |
+----+---------------------------+-----------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)The query returned the task with completed value 1. It does not show the task with the completed value 2 because TRUE is 1, not 2.
To fix it, you can use the IS operator:
SELECT
id,
title,
completed
FROM
tasks
WHERE
completed IS TRUE;Code language: SQL (Structured Query Language) (sql)Output:
+----+----------------------------+-----------+
| id | title | completed |
+----+----------------------------+-----------+
| 1 | Master MySQL Boolean type | 1 |
| 3 | Test Boolean with a number | 8 |
+----+----------------------------+-----------+
2 rows in set (0.00 sec)Code language: JavaScript (javascript)In this example, we used the IS operator to test a value against the TRUE value.
To get all the pending tasks, you can 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)Output:
+----+------------------------+-----------+
| id | title | completed |
+----+------------------------+-----------+
| 2 | Design database table | 0 |
| 4 | Test Boolean with NULL | NULL |
+----+------------------------+-----------+
2 rows in set (0.00 sec)Code language: JavaScript (javascript)Summary
- MySQL has no dedicated
BOOLEANdata type. Instead, it usesTINYINT(1)to represent theBOOLEANtype. - Use the
BOOLEANkeyword to declare a column with theBOOLEANtype. TheBOOLEANandTINYINT(1)are synonyms. - By convention, zero is false while non-zero is true.