What is the Boolean equivalent in MySQL?

In MySQL, the Boolean equivalent is the TINYINT data type. While MySQL recognizes BOOL and BOOLEAN as valid keywords, they are internally treated as aliases for TINYINT(1).

Understanding Boolean Values in MySQL

MySQL does not feature a distinct native BOOLEAN data type like some other database systems. Instead, it efficiently manages Boolean values by mapping them to numerical representations within a TINYINT column.

• TRUE is evaluated as 1.
• FALSE is evaluated as 0.
• When a TINYINT column is used in a Boolean context (e.g., in a WHERE clause), a value of 0 is interpreted as false, and any non-zero value (typically 1) is interpreted as true.

Data Type Mapping in MySQL

To ensure compatibility and facilitate schema migration from other database systems, MySQL provides aliases for common data types. For Boolean types, this mapping is straightforward:

This means that whether you define a column as BOOLEAN or BOOL, MySQL will store it as a TINYINT(1) column, optimized for storing binary states.

Practical Usage and Examples

Working with Boolean equivalents in MySQL is intuitive, leveraging the TINYINT(1) structure.

Defining Boolean Columns

When creating tables, you can use BOOLEAN or BOOL as the data type for columns intended to store true/false values.

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE, -- Internally stored as TINYINT(1)
    is_featured BOOL NOT NULL -- Also stored as TINYINT(1)
);

In this example, is_active and is_featured columns will both be created as TINYINT(1). The DEFAULT TRUE ensures new products are active by default, and NOT NULL enforces that is_featured must always have a value.

Inserting Boolean Values

You can insert values using the TRUE and FALSE keywords, or their numerical equivalents 1 and 0. MySQL handles the conversion automatically.

INSERT INTO products (product_name, is_active, is_featured) VALUES
('Laptop', TRUE, 1),           -- TRUE is stored as 1
('Mouse', FALSE, 0),          -- FALSE is stored as 0
('Keyboard', 1, TRUE),        -- 1 is stored as 1, TRUE is stored as 1
('Monitor', 0, FALSE);        -- 0 is stored as 0, FALSE is stored as 0

Querying Boolean Columns

Querying these columns is flexible, allowing for direct comparison or shorthand Boolean evaluation.

-- Selects products where 'is_active' is true (i.e., 1)
SELECT product_name, is_active, is_featured
FROM products
WHERE is_active = TRUE;

-- Selects products where 'is_featured' is false (i.e., 0)
SELECT product_name, is_active, is_featured
FROM products
WHERE is_featured = 0;

-- Shorthand for selecting products where 'is_active' is true (non-zero)
SELECT product_name
FROM products
WHERE is_active; -- Equivalent to WHERE is_active <> 0

When evaluating WHERE is_active, MySQL treats any non-zero value in the is_active column as true, and 0 as false.

Benefits of Using TINYINT for Booleans

• Storage Efficiency: A TINYINT(1) column consumes only 1 byte of storage per row, making it highly efficient for storing simple binary states like true/false.
• Flexibility: While primarily used for 0 and 1, a TINYINT column can store a wider range of small integer values (-128 to 127). This inherent flexibility is managed by MySQL's consistent interpretation of 0 as false and non-zero values as true in Boolean contexts.
• Compatibility: By mapping standard BOOL and BOOLEAN keywords to TINYINT, MySQL maintains compatibility with SQL standards and simplifies the process of migrating database schemas from systems that may have a dedicated Boolean type.