MySQL 5.7-11.3.5 The ENUM Type

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类型有以下优点:

• Compact data storage in situations where a column has a limited set of possible values. The strings you specify as input values are automatically encoded as numbers. See Section 11.7, “Data Type Storage Requirements” for storage requirements for the ENUM type.

• 在一个列只有有限的可能值集的情况下进行紧凑的数据存储。指定为输入值的字符串将被自动编码为数字。关于ENUM类型的存储要求，请参见11.7节“数据类型存储要求”。

• Readable queries and output. The numbers are translated back to the corresponding strings in query results.

• 可读的查询和输出。这些数字被转换回查询结果中相应的字符串。

and these potential issues to consider:

这些潜在的问题需要考虑:

• If you make enumeration values that look like numbers, it is easy to mix up the literal values with their internal index numbers, as explained in Enumeration Limitations.

• 如果使枚举值看起来像数字，则很容易将文字值与它们的内部索引号混在一起，如枚举限制中所述。

• Using ENUM columns in ORDER BY clauses requires extra care, as explained in Enumeration Sorting.

• 在ORDER BY子句中使用ENUM列需要额外的注意，如枚举排序中解释的那样。

• Creating and Using ENUM Columns

• Index Values for Enumeration Literals

• Handling of Enumeration Literals

• Empty or NULL Enumeration Values

• Enumeration Sorting

• Enumeration Limitations

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.

将100万行插入到这个表中，值为'medium'需要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. This means that you can use the following SELECT statement to find rows into which invalid ENUM values were assigned:空字符串错误值的索引值为0。这意味着您可以使用下面的SELECT语句来查找指定了无效ENUM值的行:

  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. The index of each value is also shown.

例如，指定为ENUM('Mercury'， 'Venus'， 'Earth')的列可以有这里显示的任何值。还显示了每个值的索引。

Value	Index
NULL	NULL
''	0
'Mercury'	1
'Venus'	2
'Earth'	3

An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.) A table can have no more than 255 unique element list definitions among its ENUM and SET columns considered as a group. For more information on these limits, see Limits Imposed by .frm File Structure.

一个ENUM列最多可以有65,535个不同的元素。(实际限制小于3000。)一个表的ENUM列和SET列作为一个组，其中唯一的元素列表定义不能超过255个。

If you retrieve an ENUM value in a numeric context, the column value's index is returned. For example, you can retrieve numeric values from an ENUM column like this:

如果在数字上下文中检索ENUM值，则返回列值的索引。例如，你可以像这样从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. For ENUM values, the index number is used in the calculation.

像SUM()或AVG()这样需要数值实参的函数在必要时将实参转换为数字。对于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. Note that ENUM columns can be assigned a character set and collation. For binary or case-sensitive collations, lettercase is taken into account when assigning values to the column.

检索时，存储在ENUM列中的值将使用列定义中使用的字母大小写显示。注意，可以为ENUM列分配字符集和排序规则。对于二进制或区分大小写的排序规则，在为列赋值时将考虑字母大小写。

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. (However, this does not work with LOAD DATA, which treats all input as strings.) 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. 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:

如果将数字存储到ENUM列中，则该数字将被视为可能值的索引，存储的值是具有该索引的枚举成员。(然而，这对LOAD DATA不起作用，因为它将所有输入都视为字符串。)如果数值被引用，如果枚举值列表中没有匹配的字符串，它仍然被解释为索引。由于这些原因，不建议使用看起来像数字的枚举值来定义ENUM列，因为这很容易造成混淆。例如，以下列的枚举成员的字符串值为'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). If you store '2', it matches an enumeration value, so it is stored as '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).

如果存储2，它会被解释为索引值，并变成'1'(索引为2的值)。如果存储'2'，它会匹配一个枚举值，因此它会存储为'2'。如果存储'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.

要确定枚举列的所有可能值，请使用SHOW COLUMNS FROM tbl_name LIKE 'enum_col'并解析输出的Type列中的ENUM定义。

In the C API, ENUM values are returned as strings. For information about using result set metadata to distinguish them from other strings, see C API Basic Data Structures.

在C API中，ENUM值作为字符串返回。有关使用结果集元数据将它们与其他字符串区分开来的信息，请参阅C API基本数据结构。

Empty or NULL Enumeration Values

空或空枚举值

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. This string can be distinguished from a “normal” empty string by the fact that this string has the numeric value 0. See Index Values for Enumeration Literals for details about the numeric indexes for the enumeration values.  如果将一个无效值插入ENUM(即一个不在允许值列表中出现的字符串)，则将空字符串作为一个特殊的错误值插入。该字符串与“普通”空字符串的区别在于，该字符串具有数值0。有关枚举值的数字索引的详细信息，请参阅枚举文字的索引值。

  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. If an ENUM column is declared NOT NULL, its default value is the first element of the list of permitted values. 如果一个ENUM列被声明为允许NULL, NULL值是该列的一个有效值，并且默认值是NULL。如果一个ENUM列声明为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. For example, 'b' sorts before 'a' for ENUM('b', 'a'). The empty string sorts before nonempty strings, and NULL values sort before all other enumeration values.

枚举值是根据索引号排序的，索引号取决于列规范中列出的枚举成员的顺序。例如，对于ENUM('b'， 'a')， 'b'排序在'a'之前。空字符串在非空字符串之前排序，NULL值在所有其他枚举值之前排序。

To prevent unexpected results when using the ORDER BY clause on an ENUM column, use one of these techniques:

为了防止在ENUM列上使用ORDER BY子句时出现意外结果，可以使用以下技术之一:

• Specify the ENUM list in alphabetic order.

• 按字母顺序指定枚举列表。

• 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. 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模式，定义中的重复值将导致警告或错误。