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:

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:

  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:

mysql> DESCRIBE student;  

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

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:

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:

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:

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.

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:

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:

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: