enum mysql java_臭名昭著的 MySQL ENUM 类型 （ 下 ）

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

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

千万不要使用数字作为枚举值，因为这样容易混淆它们的字面值和内部索引值。

在 ORDER BY 语句中使用 ENUM 更要注意

创建和使用 ENUM 数据类型的一些问题

枚举值字面量和内部索引的问题

处理枚举值字面量的一些问题

ENUM 类型中的 NULL 或空值问题

ENUM 类型的排序问题

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 开始

注意：下标并不是从 0 开始，而 0 则具有其它的意义

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

SELECT * FROM tbl_name WHERE enum_col=0;

NULL 值的索引为 NULL

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')

如果我们在插入数据或者更新数据时指定存储 2 ，因为会被解释为索引值，所以实际存储的枚举值为 '1' ( 索引为 2 的值 )。

而如果我们存储 '2' ，因为枚举值字面量 '2' 存在，所以存储的值也为 2 。

但如果我们存储 '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

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

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

如果一个 ENUM 列添加了 NULL 约束，那么这个 ENUM 列就允许 NULL 值，且默认的值就是 NULL

如果一个 ENUM 列添加了 NOT NULL 约束，那么它的默认值就是第一个枚举值。

ENUM 枚举值的排序问题

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

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

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

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

指定 ENUM 列的排序顺序使用字母顺序表

或者使用 ORDER BY CAST (col AS CHAR) 或 ORDER BY CONCAT(col) 确保 enum 列按词法排序而不是索引编号排序

ENUM 数据类型的一些限制

枚举值不能是表达式，即使该表达式用于计算字符串值。

例如，下面的建表语句是无效的，会执行失败，因为 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 值，很容易混淆枚举字面量和底层索引值 ( 可能不相同 )

ENUM 列定义中的重复值会导致警告，如果启用了严格的 SQL 模式，则会出错