文章目录
- 创建和使用 ENUM 列
- 枚举字面量的索引值
- 处理枚举字面量
- 空或 NULL 枚举值
- 枚举排序
- 枚举限制
官方文档地址:
枚举是一个字符串对象,其值从允许的值列表中选择,这些值是在表创建的时后在列规范中显式的列举的。
请参阅 11.3.1 字符串数据类型语法,了解ENUM
类型语法和长度限制。
ENUM
类型有以下优点:
- 在列的可能值集有限的情况下压缩数据存储。指定为输入值的字符串将自动编码为数字。
ENUM
类型对存储的要求,请参见 11.7 数据类型存储需求。 - 可读的查询和输出。这些数字在查询结果中被转换回相应的字符串。
这些潜在的问题需要考虑:
- 如果您使枚举值看起来像数字,就像枚举限制中解释的那样,很容易混淆文字值和它们的内部索引号。
- 在
ORDER BY
子句中使用ENUM
列需要特别小心,如枚举排序中解释的那样。
创建和使用 ENUM 列
枚举值必须是带引号的字符串文字。例如,你可以像这样创建一个包含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;
将100
万行值为'medium'
的数据插入表中需要100
万字节的存储空间,而如果将实际字符串'medium'
存储在VARCHAR
列中则需要600
万字节。
枚举字面量的索引值
每个枚举值都有一个索引:
- 列规范中列出的元素被分配索引号,从
1
开始。 - 空字符串错误值的索引值是
0
。这意味着你可以使用下面的SELECT
语句来查找指定了无效ENUM
值的行:
mysql> SELECT * FROM tbl_name WHERE enum_col=0;
-
NULL
值的索引为NULL
。 - 术语“索引”在这里指的是枚举值列表中的一个位置。它与表的索引无关。
例如,指定为ENUM('Mercury', 'Venus', 'Earth')
的列可以有下表显示的任何值。下表还显示了每个值的索引。
值 | 索引 |
|
|
|
|
|
|
|
|
|
|
一个ENUM
列最多可以有65,535
个不同的元素。
如果在数值上下文中检索ENUM
值,则返回列值的索引。例如,你可以像这样从ENUM
列中检索数值:
mysql> SELECT enum_col+0 FROM tbl_name;
如SUM()
或AVG()
等需要数字参数的函数在必要时将参数转换为数字。对于ENUM
值,在计算中使用索引号。
处理枚举字面量
创建表时,尾随空格会自动从表定义中的ENUM
成员值中删除。
检索时,存储在ENUM
列中的值将使用列定义中使用的字母来显示。注意,ENUM列可以被分配一个字符集和排序规则。对于二进制或区分大小写的排序规则,在为列赋值时要考虑字母。
如果将一个数字存储到ENUM
列中,该数字将被视为可能值的索引,存储的值是具有该索引的枚举成员。(但是,这对LOAD DATA
不起作用,LOAD DATA
将所有输入都视为字符串。)如果数值被引用,如果枚举值列表中没有匹配的字符串,它仍然被解释为索引。由于这些原因,不建议定义一个枚举值看起来像数字的枚举列,因为这很容易造成混淆。例如,以下列的枚举成员的字符串值为'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
列的所有可能值,使用SHOW COLUMNS FROM tbl_name LIKE 'enum_col'
,在输出的Type
列可以看到ENUM
的定义。
mysql> SHOW COLUMNS FROM shirts LIKE 'size';
+-------+----------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------------------------------------+------+-----+---------+-------+
| size | enum('x-small','small','medium','large','x-large') | YES | | NULL | |
+-------+----------------------------------------------------+------+-----+---------+-------+
1 row in set
mysql>
空或 NULL 枚举值
枚举值在某些情况下也可以是空字符串(''
)或NULL
:
- 如果您将无效的值插入到
ENUM
中(也就是说,一个没有出现在允许值列表中的字符串),会将空字符串作为一个特殊的错误值插入,这个字符串与“普通”空字符串的区别在于这个字符串的索引值为0
。有关枚举值的数值索引的详细信息,请参阅枚举字面量的索引值。
如果启用了严格的 SQL 模式,尝试插入无效的ENUM
值将导致错误。 - 如果
ENUM
列声明为允许NULL
,则NULL
值为该列的有效值,默认值为NULL
。如果一个ENUM
列声明为非NULL
,它的默认值是列表中允许值的第一个元素。
枚举排序
ENUM
值根据索引号排序,索引号依赖于列规范中列出枚举成员的顺序。例如,对于ENUM('b','a')
, 'b'
在'a'
之前排序。空字符串排序在非空字符串之前,NULL
值排序在所有其他枚举值之前。
为了防止在ENUM
列上使用ORDER BY
子句时出现意外的结果,可以使用以下技巧之一:
- 按照字母顺序指定
ENUM
列表。 - 确保列是按词法排序的,而不是按索引号排序。即
ORDER BY CAST(col AS CHAR)
或ORDER BY CONCAT(col)
。
枚举限制
枚举值不能是表达式,即使表达式的计算结果为字符串值。
例如,下面这个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
类型,它并不节省存储空间,并很容易混淆字符串和底层数字值(也可能不是)如果你没有正确的引用枚举值。如果使用数字作为枚举值,请始终将其用引号括起来。如果省略引号,数字将被视为索引。请参阅枚举字面量的处理,以了解即使是引用的数字也可能被错误地用作数值索引值。
如果启用了严格 SQL 模式,定义中的重复值将导致警告或错误。