Mysql 简明教程

MySQL - ENUM

ENUM (枚举) 是一个用户定义的数据类型, 它将值列表存储为字符串。在定义 ENUM 列时指定这些值。用户可以在插入值到此列时从此预定义列表中选择值。

ENUM (Enumerator) is a user defined datatype which stores a list of values as strings. These values are specified when you define the ENUM column. The user can choose values from this predefined list while inserting values into this column.

在 ENUM 列中定义的每个字符串值隐式分配一个从 1 开始的数值。MySQL 在内部使用这些数值来表示 ENUM 值。

Each string value defined in an ENUM column is implicitly assigned a numerical value starting from 1. These numerical values are used internally by MySQL to represent the ENUM values.

The MySQL ENUM Data Type

MySQL ENUM 数据类型允许你在插入或更新操作期间从预定义列表中选择一个或多个值。选定的值以字符串的形式存储在表中, 当你从 ENUM 列中检索数据时, 这些值会以人类可读的格式显示。

The MySQL ENUM data type allow you to select one or more values from a predefined list during insertion or update operations. The selected values are stored as strings in the table, and when you retrieve data from the ENUM column, the values are presented in a human-readable format.

Syntax

以下是为列定义 ENUM 数据类型的语法 −

Following is the syntax to define the ENUM data type on a column −

CREATE TABLE table_name (
   Column1,
   Column2 ENUM ('value1','value2','value3', ...),
   Column3...
);

Note: 一个枚举列最多可以有 65,535 个值。

Note: An enum column can have maximum 65,535 values.

Attributes of ENUM

MySQL 中的 ENUM 数据类型有三个属性。如下所述 −

The ENUM datatype in MySQL has three attributes. The same is described below −

Default − The default value of enum data type is NULL. If no value is provided for the enum field at the time of insertion, Null value will be inserted.
NULL − It works the same as the DEFAULT value if this attribute is set for the enum field. If it is set, the index value is always NULL.
NOT NULL − MySQL will generate a warning message if this attribute is set for the enum field and no value is provided at the insertion time.

Example

首先，让我们创建一个名为 STUDENTS 的表。在此表中，我们使用以下查询在 BRANCH 列中指定 ENUM 字符串对象 −

First of all, let us create a table named STUDENTS. In this table, we are specifying ENUM string object in the BRANCH column using the following query −

CREATE TABLE STUDENTS (
   ID int NOT NULL AUTO_INCREMENT,
   NAME varchar(30) NOT NULL,
   BRANCH ENUM ('CSE', 'ECE', 'MECH'),
   FEES int NOT NULL,
   PRIMARY KEY (ID)
);

以下是所获得的输出 −

Following is the output obtained −

Query OK, 0 rows affected (0.04 sec)

现在，我们检索 STUDENTS 表的结构，显示“BRANCH”字段具有枚举数据类型 −

Now, we retrieve the structure of the STUDENTS table, revealing that the "BRANCH" field has an enum data type −

DESCRIBE STUDENTS;

输出显示 BRANCH 字段的数据类型为 ENUM，用于存储值 ('CSE', 'ECE', 'MECH') −

The output indicates that the BRANCH field’s data type is ENUM, which stores the values ('CSE', 'ECE', 'MECH') −

现在，让我们使用以下 INSERT 查询向 STUDENTS 表中插入记录 −

Now, let us insert records into the STUDENTS table using the following INSERT query −

INSERT INTO STUDENTS (NAME, BRANCH, FEES) VALUES
('Anirudh', 'CSE', 500000),
('Yuvan', 'ECE', 350000),
('Harris', 'MECH', 400000);

在这些插入查询中，我们已对“BRANCH”字段使用了值 ('CSE', 'ECE' 和 'MECH')，这些是有效的枚举值。因此，查询在未出现任何错误的情况下执行 −

In these insertion queries, we have used values ('CSE', 'ECE', and 'MECH') for the "BRANCH" field, which are valid enum values. Hence, the queries executed without any errors −

Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

使用以下查询，我们可以显示表中的所有值 −

Using the below query, we can display all the values in the table −

SELECT * FROM STUDENTS;

以下是 STUDENTS 表的记录 −

Following are the records of STUDENTS table −

Inserting Records with Numerical ENUM Values

我们可以使用相应的数字索引将枚举列表值插入表中的 ENUM 列。数字索引从 1 开始，而不是从 0 开始。

We can insert the enum list values to the ENUM column of table using the respective numeric index. The numeric index starts from 1 but not from 0.

Example

在下面的查询中，我们使用其数字索引将值 'CSE' 从 ENUM 列表插入到 'BRANCH' 列中。由于 'CSE' 在 ENUM 列表中的位置为 1，因此我们在查询中使用 1 作为数字索引。

In the query below, we are inserting the value 'CSE' from the ENUM list into the 'BRANCH' column using its numeric index. Since 'CSE' is located at position 1 in the ENUM list, we use 1 as the numeric index in the query.

INSERT INTO STUDENTS (NAME, BRANCH, FEES) VALUES
('Devi', 1, 380000);

Output

插入查询在未出现任何错误的情况下执行 −

The insertion query executes without any errors −

Query OK, 1 row affected (0.01 sec)

Verification

让我们通过使用以下查询检索表的全部记录来验证上述插入是否成功 −

Let us verify whether the above insertion is successful or not by retrieving all the records of the table using the below query −

SELECT * FROM STUDENTS;

显示的 STUDENTS 表如下 −

The STUDENTS table displayed is as follows −

Inserting Invalid Records

在 MySQL 中，如果我们尝试将不匹配任何指定枚举值的值插入到具有 ENUM 数据类型的列中，则会导致错误。

In MySQL, if we try to insert a value into a column with an ENUM data type that does not match any of the specified enum values, it will result in an error.

Example

在以下查询中，我们引用了枚举列表中不存在的第 6 个值。因此，以下查询将生成错误 −

In the following query, we are referring to the 6th value in enum list, which does not exist. So, the following query will generate an error −

INSERT INTO STUDENTS (NAME, BRANCH, FEES) VALUES
('Thaman', 6, 200000);

Output

正如我们看到的输出，生成了错误，并且没有插入任何新记录 −

As we can see the output, an error is generated, and no new record has been inserted −

ERROR 1265 (01000): Data truncated for column 'BRANCH' at row 1

Filtering Records by Numeric ENUM Value

在 MySQL 中，您可以根据字符串值或数字索引从 ENUM 列中检索记录。数字索引从 1 开始，而不是从 0 开始。

In MySQL, you can retrieve records from an ENUM column based on either the string value or the numeric index. The numeric index starts from 1, not 0.

Example

枚举列表中 1 的数字索引为“CSE”。因此，下面的查询将获取其中 BRANCH 列包含值为“CSE”的记录。

The numeric index of 1 in enum list is 'CSE'. So, the following query will fetch the records where the BRANCH column contains the value as 'CSE'.

SELECT * FROM STUDENTS WHERE BRANCH = 1;

Output

结果输出显示其中“BRANCH”列包含值“CSE”的记录：

The resulting output displays records where the 'BRANCH' column contains the value 'CSE' −

Filtering Records by Human-Readable ENUM Value

在某些情况下，枚举列表将具有大量的值。记住列表中每个值数字索引可能很困难。在这种情况下，在查询中使用 ENUM 项可读字符串值来根据 ENUM 字段值检索记录更加方便。

There can be some instances where the enum list will have large number of values. It can be difficult to remember the numeric index for every value in the list. In such cases, it is more convenient to use the human-readable string value of the ENUM item in your query to retrieve records based on the ENUM field’s value.

Example

在以下查询中，我们正在筛选其中 BRANCH 列包含值“Mech”的记录。

In the following query, we are filtering the records where the BRANCH column contains the value "Mech".

SELECT * FROM STUDENTS WHERE BRANCH = "MECH";

Output

以下是所获得的输出 −

Following is the output obtained −

Disadvantages of ENUM Data Type

以下是 MySQL 中 ENUM 数据类型有以下缺点：

Following are the disadvantages of ENUM data type in MySQL −

If we wish to modify the values in enum list, we need to re-create the complete table using the ALTER TABLE command, which is quite expensive in terms of used resources and time.
It is very complex to get the complete enum list because we need to access the inform_schema database.
Expressions cannot be used with enumeration values. For instance, the following CREATE statement will return an error because it used the CONCAT() function for creating enumeration value −

CREATE TABLE Students (
   ID int PRIMARY KEY AUTO_INCREMENT,
   NAME varchar(30),
   BRANCH ENUM('CSE', CONCAT('ME','CH'))
);

用户变量不能用于枚举值。例如，请看以下查询：

User variables cannot be used for an enumeration value. For instance, look at the following query −

mysql> SET @mybranch = 'EEE';
mysql> CREATE TABLE Students (
   ID int PRIMARY KEY AUTO_INCREMENT,
   NAME varchar(30),
   BRANCH ENUM('CSE', 'MECH', @mybranch)
);

建议不要将数字值用作枚举值。

It is recommended to not use the numeric values as enum values.

Enum Datatypes Using a Client Program

我们还可以使用客户端程序创建 Enum 数据类型的列。

We can also create column of the Enum datatypes using the client program.

Syntax

Example

以下是这些程序 −

Following are the programs −