What is SET in MySQL: Definition and Usage

In MySQL, SET is a string data type that allows storing zero or more predefined values chosen from a list of allowed options. It is useful for storing multiple selections in a single column efficiently by representing them as a combination of bits.

How It Works

The SET data type in MySQL lets you store multiple values from a predefined list in one column. Imagine a checklist where you can tick several boxes; each box is an option in the SET. Internally, MySQL stores these selections as bits, which makes it very space-efficient.

For example, if you have a SET of colors like 'red', 'green', and 'blue', you can store any combination such as 'red,blue' or just 'green'. When you query the data, MySQL returns the selected values as a comma-separated string.

This is different from a normal string because the values must be from the allowed list, and you can store multiple choices in one field without using separate rows or tables.

Example

This example shows how to create a table with a SET column and insert multiple values into it.

sql

CREATE TABLE user_preferences (
  id INT PRIMARY KEY AUTO_INCREMENT,
  favorite_fruits SET('apple', 'banana', 'cherry', 'date') NOT NULL
);

INSERT INTO user_preferences (favorite_fruits) VALUES
('apple,banana'),
('cherry'),
('banana,date');

SELECT * FROM user_preferences;

Output

id | favorite_fruits
---|-----------------
1  | apple,banana
2  | cherry
3  | banana,date

When to Use

Use SET when you need to store multiple choices from a fixed list in a single column, such as user preferences, tags, or features enabled. It is ideal when the list of options is small and known in advance.

For example, a survey might ask users to select all hobbies they enjoy, or a product might have multiple available colors. Using SET simplifies queries and saves space compared to storing multiple rows or using a separate join table.

Key Points

• SET stores multiple predefined values in one column.
• Values are stored efficiently as bits internally.
• Only allowed values from the list can be stored.
• Returned as comma-separated strings when queried.
• Best for small fixed lists of options.

Key Takeaways

MySQL SET type stores multiple predefined values in one column efficiently.

Values must be chosen from a fixed list defined when creating the table.

SET is useful for storing multiple selections like preferences or tags.

Data is returned as comma-separated strings representing selected options.

Use SET for small, fixed lists to simplify data storage and queries.