Learning Focus

Use this lesson to understand Enum, Set with practical syntax and examples.

MySQL ENUM and SET Data Types: Complete Guide

---

ENUM Type

What is ENUM?

• Single-value selector from a predefined list of string options
• Stored as an integer index (1-2 bytes) for space efficiency
• Example: ENUM('small', 'medium', 'large')

Key Features

Feature	Details
Syntax	ENUM('val1', 'val2', ...)
Max Values	65,535 (practically limited by row size)
Storage	1-2 bytes (depends on number of options)
Default	First value if NOT NULL, else NULL

Example

CREATE TABLE shirts (
    size ENUM('xs', 's', 'm', 'l', 'xl') NOT NULL
);

INSERT INTO shirts (size) VALUES ('m');  -- Valid
INSERT INTO shirts (size) VALUES ('xxl'); -- Error!

When to Use ENUM

[OK] Good for:

• Fixed lists (e.g., statuses: 'draft', 'published', 'archived')
• Categories with stable options
• Replacing tiny lookup tables

[X] Avoid for:

• Lists that might change frequently
• Multi-language/multi-word values

---

SET Type

What is SET?

• Multi-value selector storing combinations of predefined strings
• Stored as a bitmask (1-8 bytes)
• Example: SET('read', 'write', 'execute')

Key Features

Feature	Details
Syntax	SET('val1', 'val2', ...)
Max Members	64
Storage	1-8 bytes (1 byte for 1-8 members, 2 for 9-16, etc.)
Default	Empty string (no values selected)

Example

CREATE TABLE user_permissions (
    username VARCHAR(50),
    perms SET('read', 'write', 'delete')
);

INSERT INTO user_permissions VALUES
    ('admin', 'read,write,delete'),
    ('guest', 'read');

When to Use SET

[OK] Good for:

• Multiple-choice attributes (e.g., tags, permissions)
• Options that can be combined
• Compact storage of multiple flags

[X] Avoid for:

• Lists exceeding 64 options
• Frequently changing options

---

ENUM vs SET Comparison

Feature	ENUM	SET
Values	Single	Multiple
Storage	1-2 bytes	1-8 bytes
Max Options	65,535	64
Index	Integer	Bitmask
Typical Use	Single selection	Multiple selections

---

Common Operations

ENUM Functions

-- Get numeric index:
SELECT size + 0 FROM shirts;

-- Filter by index:
SELECT * FROM shirts WHERE size = 2;  -- Returns 's'

-- Get all possible values:
SHOW COLUMNS FROM shirts LIKE 'size';

SET Functions

-- Find entries with 'write' permission:
SELECT * FROM user_permissions WHERE FIND_IN_SET('write', perms);

-- Check for exact match:
SELECT * FROM user_permissions WHERE perms = 'read,write';

-- Count selected options:
SELECT BIT_COUNT(perms) FROM user_permissions;

---

Storage Details

ENUM Storage

Number of Options	Storage
1-255	1 byte
256-65,535	2 bytes

SET Storage

Number of Members	Storage
1-8	1 byte
9-16	2 bytes
...	...
57-64	8 bytes

---

Common Issues

ENUM Problems

1. Adding new values requires ALTER TABLE:

   ALTER TABLE shirts MODIFY size ENUM('xs','s','m','l','xl','xxl');
   

2. Case sensitivity depends on collation:

   INSERT INTO shirts (size) VALUES ('M');  -- Works if collation is case-insensitive
   

SET Problems

1. Order matters in comparisons:

   SELECT 'read,write' = 'write,read';  -- Returns FALSE
   

2. Duplicate values are ignored:

   INSERT INTO user_permissions VALUES ('user', 'read,read');  -- Stores 'read'
   

---

Best Practices

For ENUM

Yes Use for static lists (e.g., countries, blood types)

Yes Always define a NOT NULL default if needed

Yes Avoid numeric-looking values (ENUM('0', '1') can conflict with indexes)

For SET

Yes Use for multi-select options under 64 items

Yes Sort values alphabetically when inserting

Yes Use bitwise operators for complex checks:

WHERE perms & 1  -- Check first bit (read)

Alternatives

• Lookup tables: More flexible but require joins
• JSON arrays: For dynamic lists (MySQL 5.7+)
• VARCHAR with separators: Easier to modify but less efficient

---

Example Use Cases

ENUM Example: Order Status

CREATE TABLE orders (
    id INT PRIMARY KEY,
    status ENUM('pending', 'shipped', 'delivered', 'cancelled')
);

SET Example: Article Tags

CREATE TABLE articles (
    id INT PRIMARY KEY,
    tags SET('tech', 'politics', 'sports', 'entertainment')
);

INSERT INTO articles VALUES (1, 'tech,sports');

---

Summary

• ENUM -> Single-choice from a fixed list (space-efficient)
• SET -> Multiple-choice from a small list (bitmask storage)
• Best for stable, limited options where schema changes are rare

---

Next Topics

1. Date & Time Types (DATE, TIME, DATETIME)
2. JSON Data Type
3. Full-Text Search

Concept Map

flowchart LR
    A[Schema Context] --> B[Enum, Set]
    B --> C[Query Pattern]
    C --> D[Validation]
    D --> E[Production Use]

Common Pitfalls

Pitfall	Consequence	Prevention
Executing queries without validating sample rows	Logic errors reach production data or reports	Start with SELECT ... LIMIT 10 and inspect edge cases
Ignoring NULL and duplicate behavior	Aggregations and filters return misleading results	Test with NULL, duplicates, and empty sets explicitly
Using advanced syntax before checking schema	Queries fail due to missing columns/indexes	Verify structure with DESCRIBE table_name; and adapt query design

Quick Reference

CREATE TABLE sample_enum_set (id INT PRIMARY KEY, value ENUM_SET);
DESCRIBE sample_table;
SHOW CREATE TABLE sample_table;

What's Next

• Previous: CHAR - Review the previous lesson to reinforce context.
• Next: Text Types - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.