MySQL Boolean and Enumeration Types: BOOLEAN, ENUM, and SET

Boolean and Enumeration Types

Boolean and enumeration types are used to restrict the possible values a column can store, helping prevent invalid data and improving query efficiency.

• BOOLEAN: Stores true/false values.
• ENUM: Stores a single value from a predefined list.
• SET: Stores zero or more values from a predefined list.

1. BOOLEAN: True or False

In MySQL, BOOLEAN is essentially an alias for TINYINT(1). It stores 0 for false and 1 for true.

Syntax:

BOOLEAN

Example:

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    is_active BOOLEAN
);

INSERT INTO users (id, username, is_active)
VALUES (1, 'alice', TRUE), 
       (2, 'bob', FALSE);

SELECT * FROM users;

Step-by-Step Explanation:

1. A users table is created with is_active as a BOOLEAN.
2. We insert two users: Alice is active (TRUE → 1), Bob is inactive (FALSE → 0).
3. Queries return 1 for TRUE and 0 for FALSE, but using TRUE/FALSE in SQL improves readability.

Use Cases for BOOLEAN:

• Active/inactive status
• Subscription flags
• Feature toggles

2. ENUM: Single Choice from a List

ENUM allows a column to store one value from a predefined set of string values. This is useful for data that must conform to specific categories.

Syntax:

ENUM('value1', 'value2', 'value3', ...)

Example:

CREATE TABLE orders (
    id INT PRIMARY KEY,
    product_name VARCHAR(50),
    order_status ENUM('Pending', 'Shipped', 'Delivered', 'Cancelled')
);

INSERT INTO orders (id, product_name, order_status)
VALUES (1, 'Laptop', 'Pending'),
       (2, 'Phone', 'Shipped');

SELECT * FROM orders;

Step-by-Step Explanation:

1. The order_status column is defined as ENUM with four allowed values.
2. We insert records that match one of the allowed statuses.
3. MySQL enforces the restriction—attempting to insert 'Processing' would result in an error.

Key Features of ENUM:

• Stores a string internally as a numeric index, making storage efficient.
• Useful for columns with a single predefined category.

Use Cases for ENUM:

• Order or ticket status
• Product categories
• Membership types

3. SET: Multiple Choices from a List

SET is similar to ENUM but allows zero or more values from a predefined list. It stores selected values as a bitmask, making it space-efficient.

Syntax:

SET('value1', 'value2', 'value3', ...)

Example:

CREATE TABLE user_roles (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    roles SET('Admin', 'Editor', 'Viewer')
);

INSERT INTO user_roles (id, username, roles)
VALUES (1, 'alice', 'Admin,Editor'),
       (2, 'bob', 'Viewer');

SELECT * FROM user_roles;

Step-by-Step Explanation:

1. The roles column is defined as a SET with three possible values.
2. Alice has two roles: Admin and Editor; Bob has one role: Viewer.
3. Queries return a comma-separated string of the selected values.

Key Features of SET:

• Stores multiple selected values in a compact format.
• Allows querying individual roles using FIND_IN_SET() or bit operations.

Use Cases for SET:

• User permissions and roles
• Feature flags
• Multi-category tags

Quick Comparison of BOOLEAN, ENUM, and SET

Best Practices

1. Use BOOLEAN for simple true/false fields to enhance readability and maintain efficiency.
2. Use ENUM for controlled, single-choice fields where invalid values must be prevented.
3. Use SET for multi-choice fields with a small, predefined set of options.
4. Avoid overusing ENUM and SET for large, dynamic lists—they are best suited for fixed, limited sets.