MySQL BOOLEAN

MySQL does not have a dedicated BOOLEAN data type. Instead, MySQL historically represents boolean values using the TINYINT data type. However, it’s essential to note that the BOOLEAN data type was introduced in MySQL 8.0.3 and later versions to provide a more intuitive and standard way to handle boolean values.

Here’s a brief overview of the BOOLEAN data type in MySQL:

The BOOLEAN data type was introduced in MySQL 8.0.3 to improve the handling of boolean values.
It is an alias for TINYINT(1), meaning it can store integer values ranging from 0 to 255, but typically it is used to represent true (1) or false (0). The MySQL BOOLEAN data type is used to store true/false values. It is a very simple data type, but it is also very useful.

Syntax for BOOLEAN

The BOOLEAN data type can be declared using the BOOLEAN keyword in column definitions.
Example:

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    is_active BOOLEAN
);

Values for BOOLEAN

The BOOLEAN data type primarily uses 1 for true and 0 for false, aligning with traditional boolean representations.
Example:

INSERT INTO example_table (id, is_active) VALUES (1, 1); -- true
INSERT INTO example_table (id, is_active) VALUES (2, 0); -- false

Aliases

As mentioned earlier, BOOLEAN is an alias for TINYINT(1). This means you can use TINYINT(1) interchangeably with BOOLEAN when defining columns.

Boolean Functions and Operators

MySQL provides specific functions and operators for working with boolean values, such as AND, OR, NOT, etc.
Example:

SELECT * 
FROM example_table 
WHERE is_active = TRUE;

Converting Between BOOLEAN and Other Data Types

You can convert between the BOOLEAN data type and other data types using the CAST function. For example, the following SQL statement converts the value of the is_active column to a string:

SELECT CAST(is_active AS CHAR) FROM example_table;

Migration from TINYINT(1)

If you have an existing database using TINYINT(1) to represent boolean values, you can migrate to BOOLEAN for better clarity and standardization.