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:
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:
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:
Let us add two new rows in the above table with the help of following query:
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:
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:
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:
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.
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:
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:
You will get the following output: