An Introduction to MySQL BOOLEAN Data Type

 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 a 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 false, and a non-zero value is considered true. To use Boolean literals, you use the constants TRUE and FALSE that evaluates to 1 and 0 respectively. See the following example:

SELECT true, false, TRUE, FALSE, True, False;
-- 1 0 1 0 1 0

MySQL BOOLEAN example

MySQL stores Boolean values 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
);

Even though we specified the completed column as BOOLEAN, when we show the table definition, it is TINYINT(1) as follows:

DESCRIBE tasks;

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); 

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; 

As you see, the true and false were converted to 1 and 0.

Because Boolean is TINYINT(1), you can insert values 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);

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;

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;

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;

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

In this tutorial, you have learned how to use the MySQL BOOLEAN datatype, which is the synonym of TINYINT(1), and how to manipulate Boolean values.