Introduction to MySQL SET Data Type

The SET data type in MySQL is a type of data type used to store multiple values in a single field, allowing us to store up to 64 different values. It is commonly used for storing some options among multiple options, such as multiple interests a user can choose, or multiple categories that an article can belong to.

Syntax

The syntax for the SET data type is as follows:

column_name SET(value1, value2, ..., value64)

where column_name specifies the name of the column with the SET data type, and value1 to value64 are the allowed value list.

Use Cases

The SET data type is commonly used in situations where multiple values need to be stored in a single field, such as:

• Storing multiple interests of a user
• Storing multiple categories of an article
• Storing multiple features of a product
• Storing multiple statuses of an order

Examples

Example 1

Let’s say we have a user table that needs to store multiple interests of a user, we can use the SET data type to achieve that. The table creation statement would be as follows:

CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  hobbies SET('reading', 'music', 'sports', 'travel', 'food')
);

Then we can insert some data into the table as follows:

INSERT INTO users (name, hobbies) VALUES ('Alice', 'reading, music');
INSERT INTO users (name, hobbies) VALUES ('Bob', 'sports, travel, food');
INSERT INTO users (name, hobbies) VALUES ('Cathy', 'reading, travel, food');

We can also use SELECT statements to query data, for example:

SELECT * FROM users WHERE FIND_IN_SET('reading', hobbies) > 0;

This will return all users who have selected “reading” as their interests.

Example 2

Another example is storing a product table where each product can have multiple features. We can use the SET data type to store the features of the product. The table creation statement would be as follows:

CREATE TABLE products (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  features SET('color', 'size', 'material', 'weight', 'brand')
);

Then we can insert some data into the table as follows:

INSERT INTO products (name, features) VALUES ('Product A', 'color, size, material');
INSERT INTO products (name, features) VALUES ('Product B', 'color, weight, brand');
INSERT INTO products (name, features) VALUES ('Product C', 'size, material, weight');

We can also use SELECT statements to query data, for example:

SELECT * FROM products WHERE FIND_IN_SET('color', features) > 0;

This will return all products that have the “color” feature.

Conclusion

The SET data type is a convenient way to store multiple values, but it also has some limitations. It can only store up to 64 different values, and due to its unique storage format, it may not be suitable for searching and sorting data. Therefore, when using the SET data type, it is important to carefully consider its use case and ensure proper usage and maintenance.