MySQL高级数据类型之：`MySQL`的`ENUM`和`SET`：其在`SQL`类型设计中的性能优势和底层存储。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL 高级数据类型：ENUM 和 SET 的性能优势与底层存储

各位好，今天我们来深入探讨 MySQL 中两种特殊的数据类型：ENUM 和 SET。它们在 SQL 类型设计中扮演着重要的角色，尤其是在需要表示有限且预定义的值集合时。理解它们的性能优势、底层存储机制以及适用场景，能够帮助我们编写更高效、更具可维护性的 SQL 代码。

1. ENUM：枚举类型的定义与使用

ENUM 类型允许我们定义一个字符串值的集合，并限制列只能存储这些集合中的值。简单来说，它就像编程语言中的枚举类型。

1.1 定义 ENUM 类型

在创建表时，我们可以使用 ENUM 关键字来定义 ENUM 类型的列。例如：

CREATE TABLE shirts (
  id INT PRIMARY KEY AUTO_INCREMENT,
  size ENUM('small', 'medium', 'large', 'x-large')
);

在这个例子中，size 列被定义为 ENUM 类型，它只能存储 ‘small’, ‘medium’, ‘large’, 或 ‘x-large’ 这四个字符串值。

1.2 插入数据

我们可以使用字符串值或对应的索引值来插入数据。

INSERT INTO shirts (size) VALUES ('medium');
INSERT INTO shirts (size) VALUES (3); -- 对应 'large'

1.3 查询数据

查询数据的方式与普通字符串类型相同。

SELECT * FROM shirts WHERE size = 'medium';
SELECT * FROM shirts WHERE size = 3; -- 同样对应 'large'

1.4 ENUM 的特性

有效性检查: ENUM 类型在插入或更新数据时会进行有效性检查。如果尝试插入不在枚举集合中的值，MySQL 会报错，或者根据 SQL 模式采取不同的行为 (例如，插入空字符串 ” 或者 NULL)。
排序: ENUM 类型的值按照它们在定义中的顺序进行排序。例如，在上面的例子中，’small’ < ‘medium’ < ‘large’ < ‘x-large’。
索引: ENUM 列可以被索引，提高查询效率。

2. SET：集合类型的定义与使用

SET 类型与 ENUM 类型类似，但它允许我们存储一个字符串值的集合，而不是单个值。换句话说，SET 列可以包含零个或多个预定义字符串值的组合。

2.1 定义 SET 类型

CREATE TABLE my_table (
  id INT PRIMARY KEY AUTO_INCREMENT,
  my_set SET('a', 'b', 'c', 'd')
);

在这个例子中，my_set 列被定义为 SET 类型，它可以包含 ‘a’, ‘b’, ‘c’, ‘d’ 的任意组合。

2.2 插入数据

插入 SET 类型的数据时，需要使用逗号分隔字符串值。

INSERT INTO my_table (my_set) VALUES ('a,b');
INSERT INTO my_table (my_set) VALUES ('b,d,a'); -- 顺序无关紧要
INSERT INTO my_table (my_set) VALUES (''); -- 空集合

2.3 查询数据

查询 SET 类型的数据需要使用 FIND_IN_SET() 函数或者位操作符。

-- 使用 FIND_IN_SET() 函数
SELECT * FROM my_table WHERE FIND_IN_SET('a', my_set) > 0; -- 查找包含 'a' 的记录

-- 使用位操作符 (需要理解 SET 的底层存储，后面会讲到)
SELECT * FROM my_table WHERE my_set & 1; -- 查找包含 'a' 的记录 (假设 'a' 对应位 1)
SELECT * FROM my_table WHERE my_set & 3; -- 查找包含 'a' 和 'b' 的记录 (1 + 2 = 3)

2.4 SET 的特性

集合操作: SET 类型允许进行集合操作，例如查找包含特定元素的记录。
去重: SET 类型会自动去除重复的值。例如，INSERT INTO my_table (my_set) VALUES ('a,a,b'); 最终 my_set 的值为 ‘a,b’。
顺序无关紧要: SET 中元素的顺序无关紧要。例如，’a,b’ 和 ‘b,a’ 被认为是相同的值。
索引: 与 ENUM 类似，SET 列也可以被索引。

3. ENUM 和 SET 的性能优势

ENUM 和 SET 类型的性能优势主要体现在以下几个方面：

存储空间优化: ENUM 和 SET 类型使用整数来存储值，而不是字符串。这可以显著减少存储空间，尤其是在处理大量数据时。例如，如果一个 ENUM 类型有 256 个不同的值，那么它只需要 1 个字节的存储空间。相比之下，如果使用 VARCHAR 类型存储这些字符串值，可能需要更多的字节。
查询效率提升: 由于 ENUM 和 SET 类型使用整数存储，比较操作可以直接在整数上进行，而不需要进行字符串比较。这可以显著提高查询效率，尤其是对于范围查询和排序操作。
数据一致性保证: ENUM 和 SET 类型在插入或更新数据时会进行有效性检查，确保只有预定义的值才能被存储。这可以有效地防止数据错误，提高数据质量。
简化代码: 使用 ENUM 和 SET 类型可以使代码更简洁、更易读。例如，使用 ENUM 类型可以避免使用大量的 IF 或 CASE 语句来判断值的有效性。

4. ENUM 和 SET 的底层存储

理解 ENUM 和 SET 的底层存储对于优化 SQL 代码至关重要。

4.1 ENUM 的底层存储

ENUM 类型使用整数来存储值。每个枚举值都被分配一个唯一的整数索引，从 1 开始。例如，对于 ENUM('small', 'medium', 'large')，’small’ 对应 1，’medium’ 对应 2，’large’ 对应 3。存储时，数据库会存储这些整数索引，而不是字符串本身。

枚举值	索引
‘small’	1
‘medium’	2
‘large’	3

存储空间的大小取决于枚举值的数量。如果枚举值数量小于 256，则使用 1 个字节存储。如果枚举值数量在 256 到 65535 之间，则使用 2 个字节存储。

4.2 SET 的底层存储

SET 类型也使用整数来存储值，但它使用位掩码的方式来表示集合。每个 SET 元素都被分配一个唯一的位，从右到左，从低位到高位。例如，对于 SET('a', 'b', 'c', 'd')，’a’ 对应 1 (2⁰)，’b’ 对应 2 (2¹)，’c’ 对应 4 (2²)，’d’ 对应 8 (2³)。存储时，数据库会存储一个整数，该整数的二进制表示中，对应于集合中元素的位被设置为 1，其他位被设置为 0。

SET 元素	位值
‘a’	1
‘b’	2
‘c’	4
‘d’	8

例如，如果 my_set 的值为 ‘a,b’，那么存储的整数为 3 (1 + 2)，其二进制表示为 0011。如果 my_set 的值为 ‘b,d’，那么存储的整数为 10 (2 + 8)，其二进制表示为 1010。

存储空间的大小取决于 SET 元素的数量。如果 SET 元素数量小于 64，则使用 8 个字节存储。

5. ENUM 和 SET 的适用场景

ENUM 和 SET 类型适用于以下场景：

表示有限且预定义的值集合: 例如，表示性别（男、女）、状态（启用、禁用）、颜色（红、绿、蓝）等。
需要进行有效性检查: 例如，确保用户只能选择预定义的选项。
需要进行排序: ENUM 类型可以按照定义中的顺序进行排序。
需要进行集合操作: SET 类型可以用于表示多个选项的组合，例如表示用户的权限、产品的标签等。

6. ENUM 和 SET 的限制

ENUM 和 SET 类型也存在一些限制：

ENUM 的最大元素数量: ENUM 类型最多可以包含 65535 个不同的值。
SET 的最大元素数量: SET 类型最多可以包含 64 个不同的值。
修改 ENUM 或 SET 定义的代价: 修改 ENUM 或 SET 类型的定义可能会导致数据丢失或损坏。因此，在设计表结构时，需要仔细考虑枚举值和集合元素的数量和顺序。如果需要经常修改，最好选择其他类型，例如关联表。
可读性: 使用整数索引查询ENUM类型时，可读性较差。应该尽量使用字符串常量进行查询，或对索引值进行注释。

7. 案例分析：使用 ENUM 优化用户状态

假设我们有一个用户表，其中包含一个 status 列，用于表示用户的状态（例如，’active’, ‘inactive’, ‘pending’, ‘blocked’）。

7.1 不使用 ENUM 的方案

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(255) NOT NULL,
  status VARCHAR(20) NOT NULL
);

在这种方案中，status 列使用 VARCHAR 类型存储字符串值。这会导致以下问题：

存储空间浪费: 每个 status 值都需要占用 20 个字节的存储空间。
查询效率低: 字符串比较需要消耗更多的 CPU 资源。
数据一致性难以保证: 用户可以随意输入 status 值，导致数据不一致。

7.2 使用 ENUM 的方案

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(255) NOT NULL,
  status ENUM('active', 'inactive', 'pending', 'blocked') NOT NULL
);

在这种方案中，status 列使用 ENUM 类型存储状态值。这可以带来以下好处：

存储空间优化: 每个 status 值只需要占用 1 个字节的存储空间。
查询效率提升: 整数比较比字符串比较更快。
数据一致性得到保证: 只有预定义的状态值才能被存储。

8. 案例分析：使用 SET 优化用户权限

假设我们有一个用户表，其中包含一个 permissions 列，用于表示用户的权限（例如，’read’, ‘write’, ‘execute’, ‘delete’）。

8.1 不使用 SET 的方案

可以使用多个布尔类型的列，例如 has_read, has_write, has_execute, has_delete，或者使用逗号分隔的字符串来存储权限列表。

使用多个布尔列: 这种方式当权限数量很多时，会增加表的宽度。
使用逗号分隔的字符串: 这种方式难以进行集合操作，查询效率低。

8.2 使用 SET 的方案

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(255) NOT NULL,
  permissions SET('read', 'write', 'execute', 'delete')
);

在这种方案中，permissions 列使用 SET 类型存储权限集合。这可以带来以下好处：

存储空间优化: 使用位掩码的方式存储权限，节省空间。
方便进行集合操作: 可以使用 FIND_IN_SET() 函数或者位操作符进行集合操作。

9. 选择合适的类型，提升数据库效率

ENUM 和 SET 是 MySQL 中强大的数据类型，它们能够有效地表示有限且预定义的值集合。通过理解它们的性能优势、底层存储机制以及适用场景，我们可以编写更高效、更具可维护性的 SQL 代码，从而提升数据库的整体性能。在设计表结构时，请根据实际需求选择合适的数据类型，避免过度设计或者选择不合适的类型，以达到最佳的性能和可维护性。

MySQL 高级数据类型：ENUM 和 SET 的性能优势与底层存储

发表回复 取消回复

发表回复取消回复