Although boolean value is the most common value type you can find in programming languages, MySQL actually has no BOOLEAN data type stored as true or false internally.
Instead, MySQL uses the TINYINT data type for any column with BOOL or BOOLEAN data type.
Consider the following example of CREATE TABLE statement. Notice how the published column is marked as BOOLEAN type:
CREATE TABLE books (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(150) NOT NULL,
published BOOLEAN
);
When you’ve successfully created the table, you can check the implemented data type for the columns using DESCRIBE statement
Notice how the published column has TINYINT type in the result set below:
DESCRIBE books;
-- +-----------+--------------+
-- | Field | Type |
-- +-----------+--------------+
-- | id | int |
-- | title | varchar(150) |
-- | published | tinyint(1) |
-- +-----------+--------------+
The BOOL or BOOLEAN keyword is actually an alias of TINYINT.
Like in other programming languages, MySQL considers the number 0 the same as false, while the number 1 is considered true.
You can actually test this using the SELECT statement as follows:
SELECT true, false;
-- +------+-------+
-- | true | false |
-- +------+-------+
-- | 1 | 0 |
-- +------+-------+
The value of true is actually any number other than 0, but it’s recommended to use the number 1 to make storing boolean values simple and consistent.
You can test this in MySQL by using the IF() function:
SELECT
IF(7, 'true', 'false'),
IF(-7, 'true', 'false'),
IF(0, 'true', 'false');
The returned result set will be as shown below:
+------------------------+-------------------------+------------------------+
| IF(7, 'true', 'false') | IF(-7, 'true', 'false') | IF(0, 'true', 'false') |
+------------------------+-------------------------+------------------------+
| true | true | false |
+------------------------+-------------------------+------------------------+
As you can see, even a negative number is considered true by MySQL.
Even though MySQL uses TINYINT for a BOOLEAN column, you don’t need to insert 0 or 1 for the column when you issue an INSERT statement.
MySQL will automatically convert any true or false value into the corresponding numeric value.
Returning to the books table example, let’s insert two books with one of them unpublished as follows:
INSERT INTO books VALUE
(NULL, "The Three Musketeers", true),
(NULL, "The Sound of Storm", false);
The books table will now have the following data:
+----+----------------------+-----------+
| id | title | published |
+----+----------------------+-----------+
| 1 | The Three Musketeers | 1 |
| 2 | The Sound of Storm | 0 |
+----+----------------------+-----------+
You can also query your table using true or false values as follows:
SELECT * FROM BOOKS WHERE published IS NOT true;
-- +----+--------------------+-----------+
-- | id | title | published |
-- +----+--------------------+-----------+
-- | 2 | The Sound of Storm | 0 |
-- +----+--------------------+-----------+
SELECT * FROM BOOKS WHERE published IS true;
-- +----+----------------------+-----------+
-- | id | title | published |
-- +----+----------------------+-----------+
-- | 1 | The Three Musketeers | 1 |
-- +----+----------------------+-----------+
But the issue with using TINYINT for BOOLEAN type is that the output of the SELECT statement will show 1 or 0 instead of true or false.
You need to use the IF() function to convert the number value into its boolean equivalent:
SELECT id, title,
IF(published, 'true', 'false') AS published
FROM BOOKS;
With the above SQL query, the published column will have either true or false value as shown below:
+----+----------------------+-----------+
| id | title | published |
+----+----------------------+-----------+
| 1 | The Three Musketeers | true |
| 2 | The Sound of Storm | false |
+----+----------------------+-----------+
And that’s how the BOOLEAN type is used in MySQL.
Aside from the TINYINT type, MySQL administrators may also use either the BIT or ENUM type for storing boolean values.
You can learn more about them here:
The TINYINT is the default alias for BOOLEAN type as defined by MySQL, but you can use other types if you know what you’re doing. 😉