ENUM 和 SET

ENUM

有时可以使用 ENUM（枚举）列代替常规的字符串类型。

• ENUM 列可以存储一组预定义的不同字符串值。

• MySQL 在存储枚举时非常紧凑，会根据列表值的数量压缩到 1 或者 2 字节中。在内部会将每个值在列表中的位置保存为整数。

• ENUM 字段是根据内部整数值排序的，而不是根据字符串本身。

  • 可以通过按照需要的顺序指定 ENUM 成员来解决这个问题。

  • 也可以在查询中使用 FIELD() 函数显式地指定排序顺序，但这会导致 MySQL 无法利用索引消除排序。

FIELD() 函数的用法

-- 数据准备
CREATE TABLE enum_test
(
    enum_char ENUM ("male", "female", "unknown")
);
INSERT INTO enum_test
VALUES ("male"),
       ("female"),
       ("unknown"),
       ("male"),
       ("female"),
       ("unknown");


-- 实验FIELD
SELECT *
FROM enum_test 
ORDER BY field(enum_char, "female", "male", "unknown");

+---------+
|enum_char|
+---------+
|female   |
|female   |
|male     |
|male     |
|unknown  |
|unknown  |
+---------+

Enum 的缺点

• MySQL 将每个枚举值存储为整数，并且必须进行查找以将其转换为字符串表示，因此 ENUM 列有一些开销。 这些开销通常可以被 ENUM 列的小尺寸所抵消，但并不总是如此。特别是，将 CHAR/VARCHAR 列联接到 ENUM 列可能比联接到另一个 CHAR/VARCHAR 列更慢。

• 虽然 ENUM 类型在存储值的方式上非常有效，但更改 ENUM 中的有效值会导致需要做 schema 变更。

SET

如果需要存储多个 true/false 值，可以考虑使用 MySQL 原生的 SET 数据类型，可以将多列组合成一列，这在 MySQL 内部是以一组打包的位的集合来表示的。这样可以更有效地利用存储空间，MySQL 具有 FIND_IN_SET() 和 FIELD() 等函数，使其易于在查询中使用。

示例：访问控制列表（ACL）

每个 SET 元素代表一个值，例如 CAN_READ、CAN_WRITE 或 CAN_DELETE。

下面是使用 SET 列的查询：

-- 准备数据
CREATE TABLE acl_test
(
    username VARCHAR(255),
    priority SET ("CAN_READ", "CAN_WRITE", "CAN_DELETE")
);
INSERT INTO acl_test(username, priority)
VALUES ("zhangsan", "CAN_READ,CAN_WRITE"),
       ("lisi", "CAN_READ,CAN_WRITE,CAN_DELETE"),
       ("wangwu", "CAN_READ,CAN_DELETE");
       
-- 查询
SELECT *
FROM acl_test
WHERE FIND_IN_SET("CAN_DELETE", priority);

+--------+-----------------------------+
|username|priority                     |
+--------+-----------------------------+
|lisi    |CAN_READ,CAN_WRITE,CAN_DELETE|
|wangwu  |CAN_READ,CAN_DELETE          |
+--------+-----------------------------+