11.3.5 The ENUM TypeENUM类型

An ENUM is a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation time.ENUM是一个字符串对象，其值从表创建时列规范中显式枚举的允许值列表中选择。

See Section 11.3.1, “String Data Type Syntax” for ENUM type syntax and length limits.有关ENUM类型语法和长度限制，请参阅第11.3.1节，“字符串数据类型语法”。

The ENUM type has these advantages:ENUM类型具有以下优点：

and these potential issues to consider:以及需要考虑的潜在问题：

Creating and Using ENUM Columns创建和使用枚举列

An enumeration value must be a quoted string literal. 枚举值必须是带引号的字符串文字。For example, you can create a table with an ENUM column like this:例如，您可以创建一个具有如下ENUM列的表：

CREATE TABLE shirts (
    name VARCHAR(40),
    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),
  ('polo shirt','small');
SELECT name, size FROM shirts WHERE size = 'medium';
+---------+--------+
| name    | size   |
+---------+--------+
| t-shirt | medium |
+---------+--------+
UPDATE shirts SET size = 'small' WHERE size = 'large';
COMMIT;

Inserting 1 million rows into this table with a value of 'medium' would require 1 million bytes of storage, as opposed to 6 million bytes if you stored the actual string 'medium' in a VARCHAR column.如果将值为'medium'的100万行插入此表，则需要100万字节的存储空间，而如果将实际字符串'medium'存储在VARCHAR列中，则需要600万字节的存储空间。

Index Values for Enumeration Literals枚举文字的索引值

Each enumeration value has an index:每个枚举值都有一个索引：

  • The elements listed in the column specification are assigned index numbers, beginning with 1.列规范中列出的元素被分配了索引号，从1开始。

  • The index value of the empty string error value is 0. 空字符串错误值的索引值为0。This means that you can use the following SELECT statement to find rows into which invalid ENUM values were assigned:这意味着您可以使用以下SELECT语句查找分配了无效枚举值的行：

    mysql> SELECT * FROM tbl_name WHERE enum_col=0;

  • The index of the NULL value is NULL.NULL值的索引是NULL。

  • The term “index” here refers to a position within the list of enumeration values. 这里的“索引”是指枚举值列表中的位置。It has nothing to do with table indexes.它与表索引无关。

For example, a column specified as ENUM('Mercury', 'Venus', 'Earth') can have any of the values shown here. 例如，指定为ENUM('Mercury', 'Venus', 'Earth')的列可以具有此处显示的任何值。The index of each value is also shown.还显示了每个值的索引。

Value值Index索引
NULLNULL
''0
'Mercury'1
'Venus'2
'Earth'3

An ENUM column can have a maximum of 65,535 distinct elements.ENUM列最多可以有65535个不同的元素。

If you retrieve an ENUM value in a numeric context, the column value's index is returned. 如果在数值上下文中检索ENUM值，将返回列值的索引。For example, you can retrieve numeric values from an ENUM column like this:例如，可以从ENUM列中检索数值，如下所示：

mysql> SELECT enum_col+0 FROM tbl_name;

Functions such as SUM() or AVG() that expect a numeric argument cast the argument to a number if necessary. 如果需要，SUM()或AVG()等需要数字参数的函数会将参数转换为数字。For ENUM values, the index number is used in the calculation.对于ENUM值，在计算中使用索引号。

Handling of Enumeration Literals枚举文字的处理

Trailing spaces are automatically deleted from ENUM member values in the table definition when a table is created.创建表时，将自动从表定义中的ENUM成员值中删除尾随空格。

When retrieved, values stored into an ENUM column are displayed using the lettercase that was used in the column definition. 检索时，存储到ENUM列中的值将使用列定义中使用的大小写显示。Note that ENUM columns can be assigned a character set and collation. 请注意，可以为ENUM列分配字符集和排序规则。For binary or case-sensitive collations, lettercase is taken into account when assigning values to the column.对于二进制或区分大小写的排序规则，在为列赋值时会考虑大小写。

If you store a number into an ENUM column, the number is treated as the index into the possible values, and the value stored is the enumeration member with that index. 如果将数字存储到ENUM列中，则该数字将被视为可能值的索引，并且存储的值是具有该索引的枚举成员。(However, this does not work with LOAD DATA, which treats all input as strings.) （但是，这不适用于将所有输入视为字符串的LOAD DATA。）If the numeric value is quoted, it is still interpreted as an index if there is no matching string in the list of enumeration values. 如果该数值加了引号，则如果枚举值列表中没有匹配的字符串，该数值仍将被解释为索引。For these reasons, it is not advisable to define an ENUM column with enumeration values that look like numbers, because this can easily become confusing. 由于这些原因，不建议使用类似于数字的枚举值定义ENUM列，因为这样很容易造成混淆。For example, the following column has enumeration members with string values of '0', '1', and '2', but numeric index values of 1, 2, and 3:例如，以下列的枚举成员的字符串值为'0'、'1'和'2'，但数字索引值为1、2和3：

numbers ENUM('0','1','2')

If you store 2, it is interpreted as an index value, and becomes '1' (the value with index 2). 如果存储2，它将被解释为索引值，并变为'1'（索引为2的值）。If you store '2', it matches an enumeration value, so it is stored as '2'. 如果存储'2'，则它与枚举值匹配，因此存储为'2'。If you store '3', it does not match any enumeration value, so it is treated as an index and becomes '2' (the value with index 3).如果存储'3'，则它与任何枚举值都不匹配，因此它被视为索引并变为'2'（索引为3的值）。

mysql> INSERT INTO t (numbers) VALUES(2),('2'),('3');
mysql> SELECT * FROM t;
+---------+
| numbers |
+---------+
| 1       |
| 2       |
| 2       |
+---------+

To determine all possible values for an ENUM column, use SHOW COLUMNS FROM tbl_name LIKE 'enum_col' and parse the ENUM definition in the Type column of the output.若要确定ENUM列的所有可能的值，请使用SHOW COLUMNS FROM tbl_name LIKE 'enum_col'，并将ENUM定义解析为输出的TYPE列的形式。

In the C API, ENUM values are returned as strings. 在C API中，ENUM值作为字符串返回。For information about using result set metadata to distinguish them from other strings, see C API Basic Data Structures.有关使用结果集元数据将其与其他字符串区分开来的信息，请参见C API基本数据结构。

Empty or NULL Enumeration Values枚举值为空或NULL

An enumeration value can also be the empty string ('') or NULL under certain circumstances:在某些情况下，枚举值也可以是空字符串（''）或NULL：

  • If you insert an invalid value into an ENUM (that is, a string not present in the list of permitted values), the empty string is inserted instead as a special error value. 如果在ENUM中插入无效值（即，允许值列表中不存在的字符串），则会将空字符串作为特殊错误值插入。This string can be distinguished from a “normal” empty string by the fact that this string has the numeric value 0. 此字符串与“普通”空字符串的区别在于此字符串的数值为0。See Index Values for Enumeration Literals for details about the numeric indexes for the enumeration values.有关枚举值的数字索引的详细信息，请参见枚举文字的索引值。

    If strict SQL mode is enabled, attempts to insert invalid ENUM values result in an error.如果启用了严格SQL模式，则尝试插入无效的ENUM值将导致错误。

  • If an ENUM column is declared to permit NULL, the NULL value is a valid value for the column, and the default value is NULL. 如果ENUM列被声明为允许NULL，则NULL值是该列的有效值，默认值为NULL。If an ENUM column is declared NOT NULL, its default value is the first element of the list of permitted values.如果枚举列声明为NOT NULL，则其默认值是允许值列表的第一个元素。

Enumeration Sorting枚举排序

ENUM values are sorted based on their index numbers, which depend on the order in which the enumeration members were listed in the column specification. ENUM值根据其索引号进行排序，索引号取决于列规范中列出枚举成员的顺序。For example, 'b' sorts before 'a' for ENUM('b', 'a'). 例如，对于ENUM('b','a')，'b'排序在'a'之前。The empty string sorts before nonempty strings, and NULL values sort before all other enumeration values.空字符串排序在非空字符串之前，NULL值排序在所有其他枚举值之前。

To prevent unexpected results when using the ORDER BY clause on an ENUM column, use one of these techniques:要防止在枚举列上使用ORDER BY子句时出现意外结果，请使用以下技术之一：

  • Specify the ENUM list in alphabetic order.按字母顺序指定ENUM列表。

  • Make sure that the column is sorted lexically rather than by index number by coding ORDER BY CAST(col AS CHAR) or ORDER BY CONCAT(col).确保该列是按词汇进行排序的，而不是按ORDER BY CAST(col AS CHAR)或按ORDER BY CONCAT(col)进行编码的索引号排序的。

Enumeration Limitations枚举限制

An enumeration value cannot be an expression, even one that evaluates to a string value.枚举值不能是表达式，即使计算结果是字符串值。

For example, this CREATE TABLE statement does not work because the CONCAT function cannot be used to construct an enumeration value:例如，此CREATE TABLE语句不起作用，因为CONCAT函数不能用于构造枚举值：

CREATE TABLE sizes (
    size ENUM('small', CONCAT('med','ium'), 'large')
);

You also cannot employ a user variable as an enumeration value. 也不能将用户变量用作枚举值。This pair of statements do not work:这对语句不起作用：

SET @mysize = 'medium';

CREATE TABLE sizes (
    size ENUM('small', @mysize, 'large')
);

We strongly recommend that you do not use numbers as enumeration values, because it does not save on storage over the appropriate TINYINT or SMALLINT type, and it is easy to mix up the strings and the underlying number values (which might not be the same) if you quote the ENUM values incorrectly. 我们强烈建议您不要使用数字作为枚举值，因为它不会在适当的TINYINT或SMALLINT类型上节省存储空间，而且如果您错误地引用枚举值，则很容易混淆字符串和基础数字值（可能不相同）。If you do use a number as an enumeration value, always enclose it in quotation marks. 如果使用数字作为枚举值，请始终将其括在引号中。If the quotation marks are omitted, the number is regarded as an index. 如果省略引号，则该数字被视为索引。See Handling of Enumeration Literals to see how even a quoted number could be mistakenly used as a numeric index value.请参阅枚举文字的处理，以了解如何将带引号的数字错误地用作数字索引值。

Duplicate values in the definition cause a warning, or an error if strict SQL mode is enabled.如果启用了严格SQL模式，定义中的重复值将导致警告或错误。