MySQL 拾遗

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模式，则会出错；