ENUM 类型
ENUM
是一个字符串对象,其值是从允许值的列表中选择的值,这些值在表创建时在列规范中显式枚举。具有以下优点:
- 在列的一组可能值有限的情况下,压缩数据存储。您指定为输入值的字符串会自动编码为数字。见“列类型存储需求”为存储需求
ENUM
类型。 - 可读的查询和输出。这些数字将转换回查询结果中的相应字符串。
以及需要考虑的这些潜在问题:
- 如果您使枚举值看起来像数字,则很容易将文字值与其内部索引号混合使用,如Enumeration Limitations中所述。
- 如Enumeration Sorting中所述,
ENUM
在ORDER BY
子句中使用列需要格外小心。
- 创建和使用ENUM列
- 枚举文字的索引值
- 枚举文字的处理
- 空或NULL枚举值
- 枚举校验
- 枚举限制
创建和使用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 , sizeFROM shirtsWHERE size = 'medium'; +--------- +-------- + | name | size | +--------- +-------- + | t -shirt | medium | +--------- +-------- +UPDATE shirtsSET size = 'small'WHERE size = 'large';COMMIT ;
将100万行值插入此表'medium'
将需要100万字节的存储空间,而如果将实际字符串存储'medium'
在VARCHAR
列中则需要600万字节的存储空间。
枚举文字的索引值
每个枚举值都有一个索引:
- 列规范中列出的元素分配有索引号,从1开始。
空字符串错误值的索引值为0。这意味着您可以使用以下
SELECT
语句查找ENUM
分配了无效值的行:mysql>
SELECT *FROM tbl_nameWHERE enum_col=0;NULL
值的索引是NULL
。- 术语“索引”在这里是指枚举值列表中的位置。它与表索引无关。
例如,指定为的列ENUM('Mercury','Venus','Earth')
可以具有此处显示的任何值。还显示每个值的索引。
值 | 指数 |
---|---|
NULL | NULL |
'' | 0 |
'Mercury' | 1个 |
'Venus' | 2 |
'Earth' | 3 |
一ENUM
列最多可包含65,535个不同的元素。
如果ENUM
在数字上下文中检索值,则返回列值的索引。例如,您可以从这样的ENUM
列中检索数值:
mysql>SELECT enum_col +0FROM tbl_name;
诸如SUM()
或AVG()
期望数字参数的函数在必要时将参数强制转换为数字。对于ENUM
值,在计算中使用索引号。
枚举文字的处理
ENUM
创建表时,会从表定义中的成员值中自动删除尾随空格。
检索到时,ENUM
将使用列定义中使用的字母大小写显示存储在列中的值。请注意,ENUM
可以为列分配一个字符集和校验规则。对于二进制或区分大小写的归类,在为列分配值时考虑字母大小写。
如果将数字存储到ENUM
列中,则该数字将被视为可能值的索引,并且存储的值是具有该索引的枚举成员。(然而,这并不能一起工作LOAD DATA
,如果数值是引用,它仍然解释为索引,如果在枚举值的列表中没有匹配的字符串,它把所有的输入为字符串)。由于这些原因,建议不要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
列的所有可能值,请使用并解析输出列中的定义。SHOW COLUMNS FROM tbl_name LIKE 'enum_col'
ENUM
Type
在C API中,ENUM
值以字符串形式返回。有关使用结果集元数据将它们与其他字符串区分开的信息,请参见“ C API数据结构”。
空或NULL枚举值
枚举值也可以是空字符串(''
)或NULL
在某些情况下:
如果您在中插入无效值
ENUM
(即,在允许值列表中不存在的字符串),则会插入空字符串,而不是将其作为特殊错误值。此字符串可以通过将数字值设为0来与“正常”空字符串区分开。有关枚举值的数字索引的详细信息,请参见枚举文字的索引值。如果启用了严格的SQL模式,则尝试插入无效
ENUM
值将导致错误。- 如果
ENUM
声明某列允许NULL
,则该NULL
值为该列的有效值,默认值为NULL
。如果ENUM
声明了列NOT NULL
,则其默认值是允许值列表的第一个元素。
枚举校验
ENUM
值根据它们的索引号校验,索引号取决于列规范中列出的枚举成员的顺序。例如,'b'
在'a'
for 之前校验ENUM('b','a')
。空字符串排在非空字符串之前,NULL
值排在所有其他枚举值之前。
为防止在使用列ORDER BY
上的子句时出现意外结果ENUM
,请使用以下技术之一:
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
类型,很容易混淆,如果你的字符串和基本数值(这可能是不一样的)引用的ENUM
值不正确。如果确实使用数字作为枚举值,请始终将其用引号引起来。如果省略引号,则将该数字视为索引。请参阅枚举文字处理,以了解甚至连带引号的数字都可能被错误地用作数字索引值。
定义中的重复值会导致警告,如果启用了严格的SQL模式,则会导致错误。