10、MySQL ENUM 类型 ( 下 )

使用MySQL ENUM 作为列的数据类型有两个主要的好处,一是使得数据更紧凑进而节省空间,二是更好的可阅读性。但是,享受这些好处的同时,也要承担 ENUM 所带来的各种负面影响。

我们罗列几个主要的负面在此,并在接下来的内容中对它们做一一解释

1、 千万不要使用数字作为枚举值,因为这样容易混淆它们的字面值和内部索引值;
2、 在ORDERBY语句中使用ENUM更要注意;
3、 创建和使用ENUM数据类型的一些问题;
4、 枚举值字面量和内部索引的问题;
5、 处理枚举值字面量的一些问题;
6、 ENUM类型中的NULL或空值问题;
7、 ENUM类型的排序问题;
8、 ENUM类型的一些限制;

创建和使用 ENUM 数据类型

如果要将某一列指定为 ENUM 类型,可以使用 ENUM 关键字,且每个枚举值都需要使用单引号 ( ' ) 引起来,例如下面的建表语句

CREATE TABLE shirts (
    name VARCHAR(40),
    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);

创建了表之后,我们就可以往表中插入值了,对于 ENUM 类型,值原则上来说必须是定义表结构所指定的枚举值之一

INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),
  ('polo shirt','small');

数据插入成功后,我们就可以使用 SQL SELECT 语句来查询数据

SELECT name, size FROM shirts WHERE size = 'medium';

结果如下

+---------+--------+
| name    | size   |
+---------+--------+
| t-shirt | medium |
+---------+--------+

如果要更新 ENUM 列,所传递的值也必须是定义时的枚举值之一

UPDATE shirts SET size = 'small' WHERE size = 'large';

如果在插入和更新值时所传递的值并不在枚举值列表中,那么就会抛出上一章节中开始那部分的问题。

ENUM 枚举值字面量和内部索引

ENUM 类型中的任何一个枚举值都有一个内部的数字索引:

1、 所以在创建表结构时指定的枚举值都会分配一个内部索引,索引的下标从1开始;

>   注意:下标并不是从 0 开始,而 0 则具有其它的意义

2、 空字符串错误值的索引为0,这样,我们可以直接使用0值来查询那些插入的或更新的无效的枚举值;

    SELECT * FROM tbl_name WHERE enum_col=0;

3、 NULL值的索引为NULL;
4、 ENUM最多只能包含65,535个不同的枚举值;

当然了,这里的术语 「 索引 」 指的是枚举值列表中的位置。它与表索引无关。

我们使用一个范例来解释下上面的几条规则,例如,假设某一列的类型为 ENUM('Mercury', 'Venus', 'Earth') ,那么该列存储的实际值则为

枚举值 索引
NULL NULL
'' 空字符串 0
'Mercury' 1
'Venus' 2
'Earth' 3

如果在数字上下文中检索 ENUM 值,则返回列值的索引。例如,我们可以像这样从 ENUM 列中检索数值

SELECT enum_col+0 FROM tbl_name;

当在ENUM 列上使用 SUM() 或 AVG() 等聚合函数时,因为这些函数的参数必须是一个数字,所以 MySQL 会自动使用它们的索引值作为参数。也就是说,对于需要计算的场景,都会使用内部索引。其实,真实的枚举值,只有在插入或者显示或者查询时才会用到。

ENUM 字面量的处理

在创建表结构时,MySQL 会自动删除 ENUM 枚举值的尾随空格,例如会把 'medium ' 转换成 'medium'。

检索时,MySQL 会自动将存储的内部索引转换为定义时指定的相应的 enum 枚举值字面量。

因此,需要注意的是,可以为 ENUM 列分配字符集和排序规则。对于二进制或区分大小写的排序规则,在为列分配值时会考虑使用字母顺序。

如果将数字存储到 ENUM 列中,则将该数字视为可能值的索引,并且存储的值是具有该索引的枚举成员 (当然了,这条规则对 LOAD DATA 无效,因为 LOAD DATA 会把所有的值都视为字符串 )。

如果引用了数值,即使枚举值列表中没有匹配的字符串,但它仍会被解释为索引。

因为这个原因,所以,不建议使用看起来像数字的枚举值来定义 ENUM 列,因为这很容易让人感到困惑,分不清传递(引用) 的到底是枚举值字面量还是内部索引。

例如,以下列的枚举成员的字符串值为 '0'、'1' 和 '2',而数字索引值为 1 、2 和 3

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

1、 如果我们在插入数据或者更新数据时指定存储2,因为会被解释为索引值,所以实际存储的枚举值为'1'(索引为2的值);
2、 而如果我们存储'2',因为枚举值字面量'2'存在,所以存储的值也为2;
3、 但如果我们存储'3',因为枚举值字面量'3'并不存在,那么它就会被视为是内部索引3,进而存储的实际值其实是'2';

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_col 列中的所有 enum 定义

ENUM 中的 NULL 或空 '' 值问题

在某些情况下,ENUM 数据类型的枚举值也可以是空字符串( '' )或 NULL

1、 如果在ENUM列中插入无效值(即,允许值列表中不存在的字符串),则会插入空字符串('')作为特殊错误值,这个特殊错误值空字符串的索引为0,从而与实际的正常的空字符串(索引大于1)区分开来;

当然了,如果启用了严格的 SQL 模式 ( sql_mode ) ,尝试插入无效的 ENUM 值会导致错误

2、 如果一个ENUM列添加了NULL约束,那么这个ENUM列就允许NULL值,且默认的值就是NULL;
3、 如果一个ENUM列添加了NOTNULL约束,那么它的默认值就是第一个枚举值;

ENUM 枚举值的排序问题

因为ENUM 类型存储的是枚举值的内部索引,所以 ENUM 值根据其索引号进行排序,具体显示出来,则取决于定义列是的枚举成员顺序。

例如,如果在定义列时,指定了 'b' 在 'a' 前面 ('b','a'),那么 'b' 的顺序就会在 'a' 之前,且空字符串在非空字符串之前排序,NULL 值在所有其他枚举值之前排序

也就是排序的顺序默认是 NULL '' 'b' 'a'

这是一个大坑啊,为了避免这个坑,为了在 ENUM 列上使用 ORDER BY 子句时防止出现意外结果,则需要做如下选择

1、 指定ENUM列的排序顺序使用字母顺序表;
2、 或者使用ORDERBYCAST(colASCHAR)或ORDERBYCONCAT(col)确保enum列按词法排序而不是索引编号排序;

ENUM 数据类型的一些限制

1、 枚举值不能是表达式,即使该表达式用于计算字符串值;

例如,下面的建表语句是无效的,会执行失败,因为 CONCAT()函数不能用于构造枚举值

CREATE TABLE sizes (
    size ENUM('small', CONCAT('med','ium'), 'large')
);

2、 不能使用用户变量作为枚举值例如下面的语句也是无效的;

SET @mysize = 'medium';

CREATE TABLE sizes (
    size ENUM('small', @mysize, 'large')
);

3、 我们强烈建议不要使用数字用作枚举值,因为它不会通过适当的TINYINT或SMALLINT类型保存在存储上而且,如果你错误地引用ENUM值,很容易混淆枚举字面量和底层索引值(可能不相同);
4、 ENUM列定义中的重复值会导致警告,如果启用了严格的SQL模式,则会出错;