ENUM and SET types in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the ENUM data type in MySQL?

ENUM is a string object that can have only one value chosen from a list of allowed values defined when the column is created.

beginner

How does the SET data type differ from ENUM in MySQL?

SET allows storing zero or more values from a predefined list, meaning multiple values can be stored in one column, unlike ENUM which stores only one.

intermediate

What happens if you try to insert a value not listed in an ENUM column?

MySQL inserts an empty string ('') as a special error value if the inserted value is not in the ENUM list.

advanced

How are ENUM and SET values stored internally in MySQL?

ENUM values are stored as numeric indexes representing the position in the list. SET values are stored as bitmaps where each bit represents a possible value.

intermediate

Can you add new values to an ENUM or SET column after the table is created?

Yes, but you must use ALTER TABLE to modify the column definition to include new allowed values.

Which MySQL data type allows storing multiple values from a predefined list in a single column?

A ENUM

B SET

C VARCHAR

D INT

SET allows multiple values from a list; ENUM allows only one.

What value does MySQL store if you insert an invalid ENUM value?

A NULL

B The invalid value as is

C Empty string ('')

D Zero

MySQL stores an empty string ('') as a special error value for invalid ENUM inserts.

How are ENUM values stored internally in MySQL?

A As numeric indexes

B As strings

C As bitmaps

D As JSON

ENUM values are stored as numeric indexes representing their position in the list.

Which statement is true about SET columns?

A They can store multiple values from the list

B They cannot be altered after creation

C They can store only one value from the list

D They store values as text

SET columns can store multiple values chosen from the predefined list.

To add a new allowed value to an ENUM column, you should:

A Insert the new value directly

B Update the ENUM list automatically

C Drop and recreate the table

D Use ALTER TABLE to modify the column

You must use ALTER TABLE to add new values to ENUM or SET columns.

Explain the difference between ENUM and SET types in MySQL and give an example use case for each.

Think about how many values you want to store in one column.

Describe how MySQL stores ENUM and SET values internally and why this matters for performance.

Consider how numbers and bits are faster to process than strings.

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