ENUM and SET types in MySQL - Step-by-Step Execution

Concept Flow - ENUM and SET types

Define ENUM or SET column with allowed values

↓

Insert or Update row with value(s)

↓

Check if value(s) match allowed list

↓

Store value(s)

↓

Retrieve stored value(s)

ENUM and SET columns store predefined values. Data must match allowed values or insertion fails.

Execution Sample

MySQL

CREATE TABLE shirts (
  size ENUM('small', 'medium', 'large'),
  colors SET('red', 'green', 'blue')
);

INSERT INTO shirts VALUES ('medium', 'red,blue');

Create a table with ENUM and SET columns, then insert a row with valid values.

Execution Table

Step	Action	Input Value	Check Against Allowed Values	Result	Stored Value
1	Create table	size ENUM('small','medium','large'), colors SET('red','green','blue')	N/A	Table created	N/A
2	Insert row	size='medium', colors='red,blue'	Check 'medium' in ENUM list	Valid	size='medium'
3	Insert row	colors='red,blue'	Check 'red' and 'blue' in SET list	Valid	colors='red,blue'
4	Retrieve row	N/A	N/A	Success	size='medium', colors='red,blue'

💡 Insertion stops after validating all values are allowed.

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
size	NULL	'medium'	'medium'	'medium'
colors	NULL	'red,blue'	'red,blue'	'red,blue'

Key Moments - 2 Insights

Why can't I insert a value not listed in ENUM or SET?

Because ENUM and SET only accept predefined values. See execution_table rows 2 and 3 where values are checked and must be valid.

How does SET store multiple values?

SET stores multiple allowed values separated by commas, as shown in execution_table row 3 where 'red,blue' is valid.

Visual Quiz - 3 Questions

Look at the execution_table, what is the stored value of 'size' after step 2?

A 'small'

B 'medium'

C NULL

D 'large'

💡 Hint

Check the 'Stored Value' column in row 2 of execution_table.

At which step does the system check if 'red' and 'blue' are allowed SET values?

A Step 3

B Step 2

C Step 1

D Step 4

💡 Hint

Look at the 'Check Against Allowed Values' column for 'red' and 'blue' in execution_table.

If you try to insert 'extra-large' into size ENUM, what happens?

A It stores 'extra-large' anyway

B It stores NULL silently

C It causes an error and insertion fails

D It converts to 'large'

💡 Hint

Refer to key_moments about invalid values and execution_table exit_note.

Concept Snapshot

ENUM and SET types store predefined values.
ENUM allows one value from a list.
SET allows multiple values from a list.
Invalid values cause insertion errors.
Use ENUM for single choice, SET for multiple choices.

Full Transcript

ENUM and SET are special column types in MySQL that let you store only specific allowed values. ENUM stores one value from a list, like sizes small, medium, or large. SET stores zero or more values from a list, like colors red, green, and blue. When you insert data, MySQL checks if the values match the allowed list. If they do, it stores them; if not, it gives an error. For example, inserting 'medium' into an ENUM size column works because 'medium' is allowed. Inserting 'red,blue' into a SET colors column works because both 'red' and 'blue' are allowed. Trying to insert a value not in the list, like 'extra-large' in size, causes an error and stops the insertion. This helps keep data clean and consistent.

Practice

1. What is the main difference between ENUM and SET types in MySQL?

easy

A. ENUM allows multiple values, SET allows only one value.

B. ENUM stores numbers, SET stores strings.

C. ENUM allows only one value from a list, SET allows multiple values.

D. ENUM is for dates, SET is for text.

Solution

1. Step 1: Understand ENUM type

   ENUM restricts a column to one value from a predefined list.

2. Step 2: Understand SET type

   SET allows storing zero or more values from a predefined list in one column.

3. Final Answer:

   ENUM allows only one value, SET allows multiple values -> Option C

4. Quick Check:

   ENUM = single, SET = multiple [OK]

Hint: Remember: ENUM = one choice, SET = many choices [OK]

Common Mistakes:

• Confusing which type allows multiple values
• Thinking ENUM stores numbers only
• Assuming SET is for single values

2. Which of the following is the correct syntax to create a column named colors that can store multiple colors using SET type?

easy

A. colors SET('red', 'green', 'blue')

B. colors ENUM('red', 'green', 'blue')

C. colors SET['red', 'green', 'blue']

D. colors ENUM['red', 'green', 'blue']

Solution

1. Step 1: Recall correct syntax for SET

   The correct syntax uses parentheses and single quotes: SET('value1', 'value2', ...)

2. Step 2: Identify correct option

   colors SET('red', 'green', 'blue') uses SET with parentheses and quoted values correctly.

3. Final Answer:

   colors SET('red', 'green', 'blue') -> Option A

4. Quick Check:

   SET uses parentheses and quotes [OK]

Hint: SET and ENUM use parentheses, not brackets [OK]

Common Mistakes:

• Using square brackets instead of parentheses
• Mixing ENUM syntax with SET
• Omitting quotes around values

3. Given the table products with a column features SET('wifi', 'bluetooth', 'gps'), what will be the result of this query?

SELECT features FROM products WHERE FIND_IN_SET('gps', features);

medium

A. Returns rows where 'gps' is one of the features.

B. Returns rows where features exactly equal 'gps'.

C. Returns all rows regardless of features.

D. Syntax error due to FIND_IN_SET usage.

Solution

1. Step 1: Understand FIND_IN_SET function

   FIND_IN_SET('gps', features) checks if 'gps' is one of the comma-separated values in the SET column.

2. Step 2: Interpret the query result

   The query returns rows where 'gps' is included in the features set, not just exact matches.

3. Final Answer:

   Returns rows where 'gps' is one of the features. -> Option A

4. Quick Check:

   FIND_IN_SET finds value inside SET [OK]

Hint: FIND_IN_SET checks if value is in SET list [OK]

Common Mistakes:

• Thinking FIND_IN_SET matches exact whole column
• Assuming syntax error with FIND_IN_SET
• Confusing SET with ENUM behavior

4. Consider this table creation:

CREATE TABLE users (status ENUM('active', 'inactive', 'pending'));

Which of the following INSERT statements will cause an error?

medium

A. INSERT INTO users (status) VALUES (NULL);

B. INSERT INTO users (status) VALUES ('active');

C. INSERT INTO users (status) VALUES ('pending');

D. INSERT INTO users (status) VALUES ('blocked');

Solution

1. Step 1: Check allowed ENUM values

   The ENUM allows only 'active', 'inactive', or 'pending'. 'blocked' is not allowed.

2. Step 2: Analyze each INSERT

   Inserting 'blocked' will cause an error because it is not in the ENUM list. NULL is allowed if column permits it.

3. Final Answer:

   INSERT with 'blocked' causes error -> Option D

4. Quick Check:

   Only ENUM values allowed, others error [OK]

Hint: Only ENUM list values allowed, others cause error [OK]

Common Mistakes:

• Assuming NULL always causes error
• Thinking any string is allowed
• Confusing ENUM with SET behavior

5. You want to store user preferences for notifications: email, sms, and push. Users can choose any combination. Which column type and definition is best to store this efficiently?

hard

A. Use ENUM('email', 'sms', 'push') to store one preference.

B. Use SET('email', 'sms', 'push') to store multiple preferences.

C. Use VARCHAR(255) and store comma-separated values.

D. Use three separate BOOLEAN columns for each preference.

Solution

1. Step 1: Identify requirement for multiple choices

   Users can select any combination of preferences, so multiple values per row are needed.

2. Step 2: Choose appropriate data type

   SET type allows storing multiple predefined values efficiently in one column.

3. Step 3: Compare other options

   ENUM allows only one value, VARCHAR wastes space and needs parsing, separate BOOLEANs increase columns.

4. Final Answer:

   Use SET('email', 'sms', 'push') for multiple preferences -> Option B

5. Quick Check:

   Multiple choices = SET type [OK]

Hint: Multiple options? Use SET, not ENUM [OK]

Common Mistakes:

• Using ENUM for multiple selections
• Storing comma-separated strings manually
• Adding many boolean columns unnecessarily