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

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;

向此表中插入 100 万行值为'medium'的数据将需要 100 万字节的存储，而如果你将实际字符串'medium'存储在VARCHAR列中，则需要 600 万字节。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.

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

例如，指定为 ENUM('Mercury', 'Venus', 'Earth') 的列可以具有此处显示的任意值。每个值的索引也显示如下。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 列可以有最多 65,535 个不同的元素。An ENUM column can have a maximum of 65,535 distinct elements.

如果在数值上下文中检索 ENUM 值，则会返回列值的索引。例如，你可以像这样从 ENUM 列中检索数值：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:

mysql> SELECT enum_col+0 FROM tbl_name;

诸如 SUM() 或 AVG() 之类需要数值参数的函数，如果有必要，会将参数强制转换为数字。对于 ENUM 值，计算中使用索引号。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.

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

当检索时，存储在 ENUM 列中的值将使用列定义中使用的字母大小写显示。请注意，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列中，该数字将被视为可能值的索引，存储的值是具有该索引的枚举成员。（然而，这在多线程系统中与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具有看起来像数字的枚举值的列，因为这很容易引起混淆。例如，下面的列具有字符串值的枚举成员'0', '1'，以及'2'，但数值索引值为1, 2，以及3: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:

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

如果你存储 2，它会被解释为索引值，并变为 '1'（索引为 2 的值）。如果你存储 '2'，它会匹配一个枚举值，因此它被存储为 '2'。如果你存储 '3'，它不匹配任何枚举值，因此会被当作索引处理，并变为 '2'（索引为 3 的值）。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).

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

要确定 ENUM 列的所有可能值，请使用 SHOW COLUMNS FROM tbl_name LIKE 'enum_col' 并解析输出中 Type 列中的 ENUM 定义。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.

在 C API 中，ENUM 值以字符串形式返回。有关使用结果集元数据将它们与其他字符串区分的信息，请参见 C API 基本数据结构。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.

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

ENUM 值根据它们的索引号进行排序，这些索引号取决于枚举成员在列规范中列出的顺序。例如，对于 ENUM('b', 'a')，'b' 排在 'a' 之前。空字符串排在非空字符串之前，而 NULL 值排在所有其他枚举值之前。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 列使用 ORDER BY 子句时防止出现意外结果，请使用以下技术之一：To prevent unexpected results when using the ORDER BY clause on an ENUM column, use one of these techniques:

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

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

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')
);

我们强烈建议您不要使用数字作为枚举值，因为与合适的TINYINT或SMALLINT类型相比，它并不能节省存储空间，而且如果您错误地引用ENUM值，很容易将字符串与底层数字值（可能不相同）混淆。如果确实使用数字作为枚举值，请始终用引号括起来。如果省略引号，数字将被视为索引。请参见枚举字面量的处理，了解即使是带引号的数字也可能被错误地用作数字索引值。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.

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