11.3.5 枚举类型
ENUM
是一个字符串对象,其值是从允许值的列 table 中选择的,这些值在 table 创建时在列规范中显式枚举。
有关ENUM类型的语法和长度限制,请参见第 11.3.1 节“字符串数据类型语法”。
ENUM类型具有以下优点:
-
在列的一组可能值有限的情况下,压缩数据存储。您指定为 Importing 值的字符串会自动编码为数字。有关
ENUM
类型的存储要求,请参见第 11.7 节“数据类型存储要求”。 -
可读的查询和输出。这些数字将转换回查询结果中的相应字符串。
以及需要考虑的这些潜在问题:
-
如果使枚举值看起来像数字,则很容易将 Literals 值与它们的内部索引号混合使用,如Enumeration Limitations中所述。
-
如Enumeration Sorting中所述,在
ORDER BY
子句中使用ENUM
列需要格外小心。
创建和使用 ENUM 列
枚举值必须是带引号的字符串 Literals。例如,您可以创建一个具有ENUM
列的 table,如下所示:
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'
插入到该 table 中将需要 100 万字节的存储空间,而如果将实际字符串'medium'
存储在VARCHAR
列中则需要 600 万字节。
枚举 Literals 的索引值
每个枚举值都有一个索引:
-
列规范中列出的元素分配有索引号,从 1 开始。
-
空字符串错误值的索引值为 0.这意味着您可以使用以下SELECT语句查找分配了无效
ENUM
值的行:
mysql> SELECT * FROM tbl_name WHERE enum_col=0;
-
NULL
值的索引是NULL
。 -
术语“索引”在这里是指枚举值列 table 中的位置。它与 table 索引无关。
例如,指定为ENUM('Mercury', 'Venus', 'Earth')
的列可以具有此处显示的任何值。还显示每个值的索引。
Value | Index |
---|---|
NULL | NULL |
'' | 0 |
'Mercury' | 1 |
'Venus' | 2 |
'Earth' | 3 |
ENUM列最多可包含 65,535 个不同的元素。 (实际限制小于 3000.)在 table 的ENUM和SET列中,一个 table 最多可以包含 255 个唯一元素列 table 定义。有关这些限制的更多信息,请参见.frm 文件结构施加的限制。
如果您在数字上下文中检索ENUM
值,则将返回列值的索引。例如,您可以像这样从ENUM
列中检索数值:
mysql> SELECT enum_col+0 FROM tbl_name;
诸如SUM()或AVG()之类的期望数字参数的函数在必要时将其强制转换为数字。对于ENUM
值,在计算中使用索引号。
枚举 Literals 的处理
创建 table 时,会从 table 定义中的ENUM
成员值中自动删除尾随空格。
检索到后,将使用列定义中使用的字母大小写显示存储在ENUM
列中的值。请注意,可以为ENUM
列分配一个字符集和排序规则。对于二进制或区分大小写的排序规则,在将值分配给列时要考虑字母大小写。
如果将数字存储到ENUM
列中,则该数字将被视为可能值的索引,并且存储的值是具有该索引的枚举成员。 (但是,这不适用于LOAD DATA,后者将所有 Importing 都视为字符串。)如果用数字引号,但如果枚举值列 table 中没有匹配的字符串,则仍将其解释为索引。由于这些原因,不建议使用枚举值定义为数字的ENUM
列,因为这很容易造成混淆。例如,以下列具有枚举成员,其字符串值为'0'
,'1'
和'2'
,但数字索引值为1
,2
和3
:
numbers ENUM('0','1','2')
如果存储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 |
+---------+
要确定ENUM
列的所有可能值,请使用从 tbl_name 中显示列,例如“ enum_col”并在输出的Type
列中解析ENUM
定义。
在 C API 中,ENUM
值作为字符串返回。有关使用结果集元数据将它们与其他字符串区分开的信息,请参见第 27.7.4 节“ C API 数据结构”。
空或 NULL 枚举值
在某些情况下,枚举值也可以是空字符串(''
)或NULL
:
- 如果您将无效值插入
ENUM
(即,在允许值列 table 中不存在的字符串),则会插入空字符串作为特殊错误值。通过将该字符串的数值设为 0,可以将该字符串与“正常”空字符串区分开。有关枚举值的数字索引,请参见枚举 Literals 的索引值。
如果启用了严格的 SQL 模式,则尝试插入无效的ENUM
值将导致错误。
- 如果将
ENUM
列声明为允许NULL
,则NULL
值是该列的有效值,默认值为NULL
。如果将ENUM
列声明为NOT NULL
,则其默认值是允许值列 table 的第一个元素。
Enumeration Sorting
ENUM
值根据其索引号排序,该索引号取决于列规范中列出的枚举成员的 Sequences。例如,'b'
在'a'
之前对ENUM('b', 'a')
排序。空字符串排在非空字符串之前,NULL
值排在所有其他枚举值之前。
为了防止在ENUM
列上使用ORDER BY
子句时出现意外结果,请使用以下一种技术:
-
按字母 Sequences 指定
ENUM
列 table。 -
确保通过编码
ORDER BY CAST(col AS CHAR)
或ORDER BY CONCAT(col)
对列进行词汇排序,而不是按索引号排序。
Enumeration Limitations
枚举值不能是 table 达式,即使是求值为字符串值的 table 达式。
例如,此CREATE TABLE语句不起作用,因为CONCAT
函数不能用于构造枚举值:
CREATE TABLE sizes (
size ENUM('small', CONCAT('med','ium'), 'large')
);
您也不能使用用户变量作为枚举值。这对语句“不起作用”:
SET @mysize = 'medium';
CREATE TABLE sizes (
size ENUM('small', @mysize, 'large')
);
我们强烈建议您不要将数字用作枚举值,因为它不会保存在适当的TINYINT或SMALLINT类型上,并且很容易将字符串和基础数字值(可能不是同样)如果您错误地引用了ENUM
值。如果确实使用数字作为枚举值,请始终将其用引号引起来。如果省略引号,则将该数字视为索引。请参见枚举 Literals 的处理,以查看连带引号的数字也可能被错误地用作数字索引值。
定义中的重复值会导致警告,如果启用了严格的 SQL 模式,则会导致错误。