MySQL Boolean

A Boolean is the simplest data type that always returns two possible values, either true or false. It can always use to get a confirmation in the form of YES or No value.

MySQL does not contain built-in Boolean or Bool data type. They provide a TINYINT data type instead of Boolean or Bool data types. MySQL considered value zero as false and non-zero value as true. If you want to use Boolean literals, use true or false that always evaluates to 0 and 1 value. The 0 and 1 represent the integer values.

Execute the following statement to see the integer values of Boolean literals:

snippet

Mysql> Select TRUE, FALSE, true, false, True, False;

After successful execution, the following result appears:

MySQL Boolean Example

We can store a Boolean value in the MySQL table as an integer data type. Let us create a table student that demonstrates the use of Boolean data type in MySQL:

snippet

mysql> CREATE TABLE student (
    studentid INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(40) NOT NULL,
    age VARCHAR(3),
    pass BOOLEAN
);

In the above query, we can see that the pass field is defined as a Boolean when showing the definition of a table; it contains TINIINT as follows:

snippet

mysql> DESCRIBE student;

Let us add two new rows in the above table with the help of following query:

snippet

mysql> INSERT INTO student(name, pass) VALUES('Peter',true), ('John',false);

When the above query executed, immediately MySQL checks for the Boolean data type in the table. If the Boolean literals found, it will be converted into integer values 0 and 1. Execute the following query to get the data from the student table:

snippet

Mysql> SELECT  studentid, name, pass FROM student;

You will get the following output where the true and false literal gets converted into 0 and 1 value.

Since MySQL always use TINYINT as Boolean, we can also insert any integer values into the Boolean column. Execute the following statement:

snippet

Mysql> INSERT INTO student(name, pass) VALUES('Miller',2);

You will get the following result:

In some cases, you need to get the result in true and false literals. In that case, you need to execute the if() function with the select statement as follows:

snippet

Mysql> SELECT  studentid, name, IF(pass, 'true', 'false') completed FROM student1;

It will give the following output:

MySQL Boolean Operators

MySQL also allows us to use operators with the Boolean data type. Execute the following query to get all the pass result of table student.

snippet

SELECT studentid, name, pass FROM student1 WHERE pass = TRUE;

This statement returns the following output:

The above statement only returns the pass result if the value is equal to 1. We can fix it by using the IS operator. This operator validates the value with the Boolean value. The following statement explains this:

snippet

SELECT studentid, name, pass FROM student1 WHERE pass is TRUE;

After executing this statement, you will get the following result:

If you want to see the pending result, use IS FALSE or IS NOT TRUE operator as below:

snippet

SELECT studentid, name, pass FROM student1 WHERE pass IS FALSE;

OR,

SELECT studentid, name, pass FROM student1 WHERE pass IS NOT TRUE;

You will get the following output: