ENUM and SET types in MySQL - Deep Dive

Overview - ENUM and SET types

What is it?

ENUM and SET are special data types in MySQL used to store predefined lists of values. ENUM allows a column to have one value chosen from a list of allowed options. SET allows a column to store any combination of multiple values from a predefined list. These types help keep data consistent by limiting what can be stored.

Why it matters

Without ENUM and SET, databases might store inconsistent or incorrect values, making data unreliable and harder to analyze. They simplify validation by restricting inputs to known options, reducing errors and improving data quality. This is especially useful for fields like status, categories, or tags where only certain values make sense.

Where it fits

Before learning ENUM and SET, you should understand basic data types like strings and integers, and how databases store data. After this, you can explore more advanced data validation, indexing, and normalization techniques to organize data efficiently.

Mental Model

Core Idea

ENUM and SET types let you store fixed lists of allowed values in a column, with ENUM choosing one value and SET allowing multiple values combined.

Think of it like...

Think of ENUM as a single-choice question on a form where you pick one answer, and SET as a checklist where you can tick multiple boxes from a list.

┌───────────────┐       ┌───────────────┐
│   ENUM type   │       │   SET type    │
├───────────────┤       ├───────────────┤
│ Allowed values│       │ Allowed values│
│ (e.g. Red,   │       │ (e.g. Red,    │
│ Green, Blue) │       │ Green, Blue)  │
├───────────────┤       ├───────────────┤
│ Stores one   │       │ Stores any    │
│ value from   │       │ combination   │
│ list         │       │ of values     │
└───────────────┘       └───────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding ENUM basics

Concept: ENUM stores a single value from a predefined list of strings.

In MySQL, ENUM is defined by listing allowed values, for example: ENUM('small', 'medium', 'large'). When you insert data, only these values are accepted. Internally, MySQL stores ENUM values as numbers representing the position in the list, but you see the string.

Result

You can store only one of the allowed values in the column, ensuring consistent data entries.

Understanding ENUM's single-choice nature helps prevent invalid data and simplifies queries by limiting possible values.

2

Foundation

Understanding SET basics

Concept: SET stores zero or more values from a predefined list, combined together.

SET is defined like SET('a', 'b', 'c'). You can store any combination like 'a', 'b,c', or even none. Internally, MySQL stores SET values as bits in a number, each bit representing one allowed value.

Result

You can store multiple selected options in one column, useful for tags or features.

Knowing SET allows multiple selections in one field helps design flexible data models for multi-choice attributes.

3

Intermediate

How ENUM stores data internally

🤔Before reading on: do you think ENUM stores the full string or a number internally? Commit to your answer.

Concept: ENUM stores values as small integers internally, mapping to the string list.

Each ENUM value is assigned a number starting at 1 for the first value. When you insert 'medium' in ENUM('small','medium','large'), MySQL stores 2 internally. This makes storage efficient and comparisons fast.

Result

ENUM columns use less space than strings and queries can be optimized using the numeric representation.

Understanding ENUM's internal number storage explains why ENUM is efficient and why adding values changes internal mappings.

4

Intermediate

How SET stores data internally

🤔Before reading on: do you think SET stores values as text or as a combined number? Commit to your answer.

Concept: SET stores selected values as bits in an integer, each bit representing one allowed value.

For SET('a','b','c'), 'a' is bit 1, 'b' bit 2, 'c' bit 3. If you store 'a,c', bits 1 and 3 are set, so the number stored is 5 (binary 101). This compact storage allows fast checks and small space usage.

Result

SET columns efficiently store multiple selections and allow bitwise operations in queries.

Knowing SET uses bit flags internally helps understand how to query and manipulate multi-value fields.

5

Intermediate

Using ENUM and SET in queries

Concept: You can filter and compare ENUM and SET values using normal SQL syntax.

For ENUM, you can write WHERE size = 'medium'. For SET, you can use FIND_IN_SET('a', features) to check if 'a' is selected. You can also use bitwise operators on SET columns to test combinations.

Result

You can easily write queries to find rows with specific ENUM or SET values.

Understanding query patterns for ENUM and SET enables effective data retrieval and filtering.

6

Advanced

Limitations and gotchas of ENUM and SET

🤔Before reading on: do you think adding new ENUM values changes existing data? Commit to your answer.

Concept: ENUM and SET have fixed allowed values; changing them can affect stored data and queries.

Adding new ENUM values changes the internal numbering, which can cause confusion if not handled carefully. SET is limited to 64 values because it uses bits. Also, ENUM stores only one value, so it can't represent multiple selections.

Result

You must plan ENUM and SET values carefully and understand their limits to avoid data issues.

Knowing these limitations prevents data corruption and helps choose the right type for your needs.

7

Expert

ENUM and SET in large-scale production

🤔Before reading on: do you think ENUM and SET are always the best choice for fixed lists? Commit to your answer.

Concept: In large systems, ENUM and SET can improve performance but may cause maintenance challenges.

ENUM and SET reduce storage and speed up queries but are less flexible than separate lookup tables. Changing allowed values requires ALTER TABLE, which can be slow on big tables. Experts often use ENUM/SET for small, stable lists and prefer normalized tables for complex or changing data.

Result

You balance performance and flexibility by choosing when to use ENUM/SET versus normalized designs.

Understanding trade-offs helps design scalable, maintainable databases that perform well.

Under the Hood

ENUM stores each allowed string as a small integer internally, mapping 1 to the first string, 2 to the second, and so on. SET stores each allowed string as a bit position in an integer, allowing multiple bits to be set simultaneously. This compact representation reduces storage and speeds up comparisons. When querying, MySQL translates these internal numbers back to strings for display.

Why designed this way?

ENUM and SET were designed to enforce data integrity by restricting values and to optimize storage and performance. Using integers and bits internally is much faster and smaller than storing full strings repeatedly. Alternatives like separate lookup tables offer flexibility but add complexity and join overhead. ENUM and SET provide a simple, efficient solution for fixed lists.

┌───────────────┐
│ ENUM column   │
├───────────────┤
│ 'small'  → 1  │
│ 'medium' → 2  │
│ 'large'  → 3  │
└─────┬─────────┘
      │ stores integer internally
      ▼
┌───────────────┐
│ Stored value: │
│ 2 (for 'medium')│
└───────────────┘


┌───────────────┐
│ SET column    │
├───────────────┤
│ 'a' → bit 1   │
│ 'b' → bit 2   │
│ 'c' → bit 3   │
└─────┬─────────┘
      │ stores bits internally
      ▼
┌───────────────┐
│ Stored value: │
│ 5 (binary 101)│
│ means 'a,c'   │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does ENUM store the full string or a number internally? Commit to your answer.

Common Belief: ENUM stores the full string value in the database.

Reality: ENUM stores a small integer representing the position of the string in the allowed list, not the full string.

Why it matters: Thinking ENUM stores full strings leads to misunderstandings about storage size and behavior when adding new ENUM values.

Quick: Can SET store unlimited values? Commit to yes or no.

Common Belief: SET can store any number of values from its list without limit.

Reality: SET can store up to 64 different values because it uses bits in an integer to represent selections.

Why it matters: Assuming unlimited SET values can cause design errors and unexpected failures when exceeding the limit.

Quick: Does changing ENUM values affect existing data? Commit to yes or no.

Common Belief: You can add or reorder ENUM values anytime without affecting stored data.

Reality: Adding or reordering ENUM values changes their internal numbering, which can cause existing data to map to different strings.

Why it matters: Ignoring this can corrupt data meaning and cause bugs in applications relying on ENUM values.

Quick: Can ENUM store multiple values like SET? Commit to yes or no.

Common Belief: ENUM can store multiple values selected from its list.

Reality: ENUM stores only one value from its list; it cannot store multiple selections.

Why it matters: Confusing ENUM with SET leads to wrong data modeling and application errors.

Expert Zone

1

ENUM internal numbering depends on the order of values in the definition, so changing order can silently break data meaning.

2

SET values can be combined in any order, but the stored number depends on bit positions, so string order in queries may vary.

3

Using ENUM and SET can improve query speed and reduce storage, but they complicate schema migrations and require careful planning.

When NOT to use

Avoid ENUM and SET when the list of allowed values changes frequently or is very large. Instead, use normalized tables with foreign keys for flexibility and easier maintenance.

Production Patterns

In production, ENUM is often used for stable status fields like 'active', 'pending', 'closed'. SET is used for feature flags or tags where multiple options apply. Experts combine ENUM/SET with application logic to ensure smooth migrations and data integrity.

Connections

Foreign Key Constraints

ENUM and SET provide a simpler, less flexible alternative to foreign keys for fixed lists.

Understanding ENUM/SET helps appreciate trade-offs between simplicity and normalization in database design.

Bitmasking in Programming

SET uses bitmasking internally, similar to how programmers use bits to represent multiple flags.

Knowing bitmasking in programming clarifies how SET stores multiple values efficiently.

User Interface Design

ENUM corresponds to dropdown menus (single choice), SET corresponds to checkboxes (multiple choice).

Recognizing this connection helps design database schemas that align with user input methods.

Common Pitfalls

#1 Adding ENUM values without considering internal numbering.

Wrong approach:

ALTER TABLE products MODIFY size ENUM('small','medium','large','x-large');

Correct approach:

Add new ENUM values at the end and update application logic carefully, or use a lookup table instead.

Root cause:

Misunderstanding that ENUM values are stored as numbers tied to their order, so changing order or adding values in the middle breaks existing data.

#2 Using SET for more than 64 values.

Wrong approach:

CREATE TABLE features (options SET('a','b',...,'z','aa','ab',...)); -- more than 64 values

Correct approach:

Use a normalized many-to-many relationship table instead of SET for large or growing lists.

Root cause:

Not knowing SET is limited by the number of bits in an integer (64 max).

#3 Trying to store multiple values in ENUM.

Wrong approach:

INSERT INTO table (status) VALUES ('active,pending'); -- invalid ENUM value

Correct approach:

Use SET type or separate related tables to store multiple values.

Root cause:

Confusing ENUM's single-value constraint with SET's multi-value capability.

Key Takeaways

ENUM and SET are special MySQL types that restrict column values to predefined lists, improving data consistency.

ENUM stores one value from a list as a small integer internally, while SET stores multiple values as bits in an integer.

These types save space and speed up queries but have limits like fixed value lists and maximum SET size of 64.

Changing ENUM or SET definitions requires care to avoid corrupting existing data due to internal numbering changes.

Experts use ENUM and SET for stable, small lists and prefer normalized tables for flexible or large sets of values.