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 |
+--------+-----------------------------+