Enum vs Set in MySQL: Key Differences and Usage

In MySQL, ENUM is a string object that can have only one value chosen from a predefined list, while SET allows storing zero or more values from a predefined list as a combination. ENUM is for single-choice fields, and SET is for multiple-choice fields.

Quick Comparison

This table summarizes the main differences between ENUM and SET in MySQL.

Feature	ENUM	SET
Value Type	Single value from list	Multiple values from list (combination)
Storage	1 or 2 bytes depending on list size	Up to 8 bytes as bitmask
Max Number of Elements	Up to 65,535	Up to 64
Use Case	Single choice fields	Multiple choice fields
Ordering	Values have index order	Values stored as bits, order not guaranteed
Querying	Simple equality checks	Bitwise operations or FIND_IN_SET()

Key Differences

ENUM stores exactly one value from a predefined list of strings. Internally, MySQL stores the index of the chosen value, making it efficient for single-choice fields like status or category. You cannot select multiple ENUM values at once.

SET, on the other hand, stores zero or more values from a predefined list. It uses a bitmask internally, where each bit represents one possible value. This allows storing combinations like 'red' and 'blue' together in one field, useful for tags or features.

While ENUM values have a natural order based on their list position, SET values are stored as bits, so their order is not guaranteed when retrieved. Querying SET fields often requires bitwise functions or FIND_IN_SET(), whereas ENUM queries are straightforward equality checks.

Code Comparison

Here is how you define and use an ENUM column for a single-choice field:

mysql

CREATE TABLE shirts_enum (
  size ENUM('small', 'medium', 'large', 'x-large') NOT NULL
);

INSERT INTO shirts_enum (size) VALUES ('medium');
SELECT * FROM shirts_enum;

Output

+--------+
| size   |
+--------+
| medium |
+--------+

SET Equivalent

Here is how you define and use a SET column for multiple-choice values:

mysql

CREATE TABLE shirts_set (
  colors SET('red', 'green', 'blue', 'yellow') NOT NULL
);

INSERT INTO shirts_set (colors) VALUES ('red,blue');
SELECT * FROM shirts_set;

Output

+-----------+
| colors    |
+-----------+
| red,blue  |
+-----------+

When to Use Which

Choose ENUM when you need to store exactly one value from a fixed list, such as status flags or categories. It is simple and efficient for single-choice fields.

Choose SET when you want to store multiple values from a fixed list in one column, like tags or features that can be combined. It is useful when multiple selections are allowed but the list is limited.

For more complex or dynamic multiple selections, consider using a separate related table instead of SET.

Key Takeaways

ENUM stores one value from a list; SET stores multiple values as a bitmask.

ENUM is best for single-choice fields; SET suits multiple-choice fields with limited options.

Querying ENUM is simpler than SET, which may require bitwise functions.

Use SET only when multiple selections are needed and the list is small and fixed.

For flexible multi-value needs, consider separate tables instead of SET.