MySQL – Boolean

Booleans are the simplest data types since they always return two possible values, true or false. There is always the option of receiving confirmation in the form of a YES or NO response.

It is pertinent to note that MySQL does not contain a built-in Boolean or Bool data type. There is a TINYINT data type available instead of a Boolean or Bool data type. The MySQL database considered value zero as false and non-zero value as true. If you would like to use Boolean literals, use true or false, which will always evaluate to 0 or 1. The 0 and 1 represent integer values.

The following statement can be used to determine the integer value of a Boolean literal:

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

As a result of successful execution, the following result is displayed:

Example

Using MySQL, we can store a Boolean value as an integer data type. The following table employees illustrates the use of the Boolean data type in MySQL:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  is_manager TINYINT(1)
);

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

DESCRIBE employees; 

The following statement can be used to insert data into the Boolean column:

INSERT INTO employees (id, name, is_manager) VALUES (1, 'John', 1);

To insert data into a Boolean column, you can also use the keywords TRUE and FALSE.

INSERT INTO employees (id, name, is_manager) VALUES (1, 'John', TRUE);

Following the execution of the above query, MySQL immediately checks to see if the table contains Boolean data types. Boolean literals will be converted into integer values of 0 and 1 if they are found. The following query should be executed in order to retrieve the customer table data:

SELECT name FROM employees WHERE is_manager = TRUE;