Introduction to MySQL ENUM Data Type

The ENUM data type is a special data type in MySQL used to represent a set of predefined values. When defining an ENUM data type, you need to specify a list of possible values, and only these values can be used when inserting or updating data. The ENUM data type can be used to save storage space and provide data integrity guarantees.

Syntax

The syntax for creating an ENUM data type is as follows:

ENUM('value1', 'value2', ..., 'valueN')

where value1 to valueN are the possible enumerated values, with a maximum of 65535 values. The ENUM data type can also be defined using the SET keyword, but this article will focus on the case of using enumerated values.

Use Cases

The ENUM data type can be used in situations where you need to restrict the values of a field, such as gender, marital status, region, etc. In these cases, you can use the ENUM data type to ensure that only predefined values can be stored in the database.

Examples

Here are some examples of using the ENUM data type:

Example 1

Create a table named user with an ENUM type field named gender:

CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    gender ENUM('male', 'female')
);

Insert data:

INSERT INTO user VALUES (1, 'Alice', 'female');
INSERT INTO user VALUES (2, 'Bob', 'male');
INSERT INTO user VALUES (3, 'Charlie', 'other');

Query data:

SELECT * FROM user;

Result:

+----+---------+--------+
| id | name    | gender |
+----+---------+--------+
|  1 | Alice   | female |
|  2 | Bob     | male   |
|  3 | Charlie | NULL   |
+----+---------+--------+

From the result, we can see that the gender field of the first and second rows of data is set to female and male respectively, while the gender field of the third row of data is set to NULL because 'other' is not a predefined enumerated value.

Example 2

Create a table named product with an ENUM type field named color:

CREATE TABLE product (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    color ENUM('red', 'green', 'blue', 'yellow')
);

Insert data:

INSERT INTO product VALUES (1, 'Apple', 'red');
INSERT INTO product VALUES (2, 'Banana', 'yellow');
INSERT INTO product VALUES (3, 'Carrot', 'orange');

Query data:

SELECT * FROM product;

Result:

+----+--------+--------+
| id | name   | color  |
+----+--------+--------+
|  1 | Apple  | red    |
|  2 | Banana | yellow |
|  3 | Carrot | orange |
+----+--------+--------+

Conclusion

In MySQL, the ENUM data type is a useful data type that allows us to define a set of optional values and ensures that only one of these values can be stored in a column. This greatly simplifies the restriction and validation of column values, and also facilitates data handling and management.

When we need to define a column that can only take a specific set of values, ENUM is a great choice.

When we need to define a column that can only take a specific set of values, ENUM is a great choice. It not only helps to prevent input errors, but also improves the readability and maintainability of data. However, there are some limitations with ENUM type data, such as the inability to expand the list of options.

In conclusion, based on the different business scenarios and data requirements, we can choose different data types to define columns in MySQL. Through the introduction in this article, it is believed that readers now have a deeper understanding of the ENUM data type, and hopefully this can help everyone better design and manage MySQL databases.