Which data type to use for storing boolean values in MySQL ?

In MySQL, there is no native BOOLEAN data type, but you can use TINYINT(1) to store boolean values (TRUE/FALSE or 1/0). MySQL aliases BOOL or BOOLEAN to TINYINT(1) internally, so you can use either keyword for clarity.

Options for Storing Boolean Values

Data Type	Description	Example Usage
`TINYINT(1)`	Stores `1` (TRUE) or `0` (FALSE). Default and recommended method.	`is_active TINYINT(1) DEFAULT 0`
`BOOL`	Alias for `TINYINT(1)`. Use for readability (e.g., `column_name BOOL`).	`is_verified BOOL NOT NULL`
`ENUM`	Use `ENUM('TRUE', 'FALSE')` for human-readable strings (less efficient than `TINYINT`).	`status ENUM('TRUE', 'FALSE') DEFAULT 'TRUE'`
`BIT(1)`	Stores a single bit (`0` or `1`). Space-efficient but requires conversion in queries.	`flag BIT(1) DEFAULT 0`

Best Practice: Use `TINYINT(1)` or `BOOL`

Example Table:

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    is_active TINYINT(1) DEFAULT 0,  -- 0 = FALSE, 1 = TRUE
    is_verified BOOL NOT NULL         -- BOOL is an alias for TINYINT(1)
);

Inserting Boolean Values:

-- Insert TRUE (1) or FALSE (0)
INSERT INTO users (id, username, is_active, is_verified)
VALUES (1, 'alice', 1, TRUE);

Querying:

-- Get all active users
SELECT * FROM users WHERE is_active = 1;

-- Check verification status
SELECT * FROM users WHERE is_verified = TRUE;

Key Notes

TRUE/FALSE Aliases:

TRUE is equivalent to 1.
FALSE is equivalent to 0.
Example: WHERE is_verified = TRUE is the same as WHERE is_verified = 1.

Handling NULL:

Use DEFAULT constraints to avoid ambiguity (e.g., DEFAULT 0 for FALSE).
Example: is_active TINYINT(1) DEFAULT 0.

Avoid ENUM or BIT:

ENUM adds overhead for string comparisons and storage.
BIT(1) requires conversion (e.g., SELECT flag + 0 FROM users to see 0/1).

Compatibility:

Most programming languages (e.g., Python, Java) map TINYINT(1) to boolean types seamlessly.

When to Use Alternatives

ENUM('TRUE', 'FALSE'): Only if you need human-readable values in the database (not recommended for performance).
BIT(1): If storage optimization is critical (1 bit vs. 1 byte for TINYINT).

Summary

Use TINYINT(1) or BOOL for simplicity and compatibility.
Avoid ENUM or BIT unless you have specific requirements.
Always enforce constraints (e.g., CHECK(is_active IN (0, 1)) in MySQL 8.0.16+).