MySQL ENUM 的数值表示：深入理解内部存储机制
大家好，今天我们来深入探讨 MySQL 中 ENUM 数据类型的数值表示。ENUM 类型在实际应用中非常常见，尤其是在需要存储固定、预定义集合的值时，例如表示性别、状态、等级等。理解 ENUM 的内部存储方式，不仅能帮助我们更好地设计数据库，还能优化查询性能，避免潜在的错误。
1. ENUM 的定义与基本用法
首先，让我们回顾一下 ENUM 的定义和基本用法。ENUM 是一种字符串对象，其值从创建表时指定的允许值列表中选择。
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('small', 'medium', 'large')
);
INSERT INTO shirts (name, size) VALUES ('t-shirt', 'medium');
INSERT INTO shirts (name, size) VALUES ('polo shirt', 'large');
INSERT INTO shirts (name, size) VALUES ('tank top', 'small');
SELECT * FROM shirts;在这个例子中，shirts 表的 size 列被定义为 ENUM 类型，它只允许存储 ‘small’、’medium’ 和 ‘large’ 这三个值。任何尝试插入其他值的操作都会导致错误（除非 STRICT_TRANS_TABLES 模式未启用，在这种情况下，会插入空字符串或 NULL）。
2. ENUM 的数值表示：索引值
ENUM 类型最关键的特性是，在 MySQL 内部，ENUM 值实际上是以数值形式存储的，而不是字符串。这些数值对应于 ENUM 定义中值的索引位置。
- 第一个枚举值对应索引 1。
- 第二个枚举值对应索引 2。
- 依此类推。
因此，在上面的 shirts 表中：
- ‘small’ 对应 1
- ‘medium’ 对应 2
- ‘large’ 对应 3
我们可以通过以下方式来验证这一点：
SELECT name, size, CAST(size AS UNSIGNED) AS size_index FROM shirts;这段代码将 ENUM 值强制转换为无符号整数，从而显示其对应的索引值。
3. 空字符串和 NULL 的处理
除了预定义的值之外，ENUM 类型还可以存储空字符串 ('') 和 NULL。它们的数值表示如下：
- 空字符串 (
'') 的数值表示为 0。 NULL的数值表示为NULL。
这意味着，如果插入一个不在 ENUM 定义列表中的值，并且 STRICT_TRANS_TABLES 模式未启用，MySQL 会尝试将该值转换为空字符串，其数值表示为 0。
例如：
INSERT INTO shirts (name, size) VALUES ('hoodie', 'xlarge'); -- 如果 STRICT_TRANS_TABLES 未启用
SELECT name, size, CAST(size AS UNSIGNED) AS size_index FROM shirts WHERE name = 'hoodie';在 STRICT_TRANS_TABLES 未启用的情况下，size 列的值会变为 ''，size_index 会变为 0。 如果启用了 STRICT_TRANS_TABLES 则会报错。
4. ENUM 的存储大小
ENUM 类型的存储大小取决于 ENUM 定义中值的数量。
- 如果 ENUM 定义包含 255 个或更少的值，则使用 1 个字节存储。
- 如果 ENUM 定义包含 256 到 65535 个值，则使用 2 个字节存储。
这意味着，即使 ENUM 值是字符串，但由于它们以数值形式存储，因此存储空间通常比直接存储字符串更有效率。
5. ENUM 的优势与劣势
优势：
- 存储效率： 由于使用数值表示，ENUM 类型可以节省存储空间，尤其是在存储重复的、预定义的值时。
- 数据完整性： ENUM 类型可以强制数据符合预定义的集合，避免无效值的插入，提高数据质量。
- 查询性能： 在某些情况下，基于数值的比较可能比基于字符串的比较更快。
劣势：
- 可读性： 虽然存储的是数值，但我们通常希望看到的是字符串。因此，在查询结果中，需要将数值转换回字符串，这可能会增加一些复杂性。
- 修改困难： 修改 ENUM 定义（例如，添加或删除值）可能需要修改表结构，并且可能影响现有数据。如果修改枚举顺序，则会导致数据错乱。
- 扩展性： 当枚举数量很大时，ENUM 的维护会变得困难，并且可能会超出存储限制。
6. ENUM 在查询中的应用
我们可以直接使用字符串或数值来查询 ENUM 类型的列。
使用字符串：
SELECT * FROM shirts WHERE size = 'medium';使用数值：
SELECT * FROM shirts WHERE size = 2;这两种查询方式是等价的，MySQL 会自动将字符串转换为对应的数值进行比较。
7. 使用 FIND_IN_SET 函数
FIND_IN_SET 函数可以用来查找 ENUM 值在 ENUM 定义中的位置。
SELECT FIND_IN_SET('medium', 'small,medium,large'); -- 返回 2这个函数在某些情况下可以用于动态地确定 ENUM 值的索引。但是，通常不建议直接使用字符串形式的枚举定义，而是应该直接使用 ENUM 列。
8. ENUM 与其他数据类型的比较
- VARCHAR/CHAR： VARCHAR 和 CHAR 类型可以存储任意字符串，但缺乏数据完整性约束。ENUM 类型可以确保数据符合预定义的集合。
- INT/TINYINT： INT 和 TINYINT 类型可以存储数值，但缺乏语义信息。ENUM 类型可以提供更具描述性的信息，例如 ‘small’、’medium’ 和 ‘large’，而不是简单的 1、2 和 3。
- SET： SET 类型类似于 ENUM，但允许存储多个值的组合。ENUM 只能存储单个值。
9. 何时使用 ENUM？
在以下情况下，可以考虑使用 ENUM 类型：
- 需要存储固定、预定义集合的值。
- 需要强制数据符合预定义的集合，提高数据质量。
- 需要节省存储空间。
- 需要提高查询性能（在某些情况下）。
10. 何时不应该使用 ENUM？
在以下情况下，应该避免使用 ENUM 类型：
- 需要存储的值不是固定的，可能会经常变化。
- 需要存储的值的数量很大，维护困难。
- 需要存储多个值的组合（应该使用 SET 类型）。
- 需要更高的灵活性和可扩展性。
11. ENUM 的最佳实践
- 谨慎选择 ENUM 定义： 仔细考虑需要存储的值，并确保 ENUM 定义完整且准确。
- 避免频繁修改 ENUM 定义： 修改 ENUM 定义可能会影响现有数据，因此应该尽量避免频繁修改。
- 使用字符串进行查询： 虽然可以使用数值进行查询，但为了提高可读性，建议使用字符串进行查询。
- 考虑使用查找表： 如果 ENUM 定义经常变化，或者需要更高的灵活性，可以考虑使用查找表来代替 ENUM 类型。
12. 示例：状态管理
假设我们需要管理订单的状态，状态包括 ‘pending’、’processing’、’shipped’ 和 ‘delivered’。可以使用 ENUM 类型来表示订单状态。
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
order_date DATE,
status ENUM('pending', 'processing', 'shipped', 'delivered')
);
INSERT INTO orders (order_date, status) VALUES ('2023-10-26', 'pending');
INSERT INTO orders (order_date, status) VALUES ('2023-10-27', 'processing');
INSERT INTO orders (order_date, status) VALUES ('2023-10-28', 'shipped');
INSERT INTO orders (order_date, status) VALUES ('2023-10-29', 'delivered');
SELECT order_id, order_date, status FROM orders WHERE status = 'shipped';在这个例子中，ENUM 类型可以确保订单状态始终是有效的值，并且可以节省存储空间。
13. 示例：用户角色
假设我们需要管理用户的角色，角色包括 ‘admin’、’editor’ 和 ‘viewer’。可以使用 ENUM 类型来表示用户角色。
CREATE TABLE users (
user_id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50),
role ENUM('admin', 'editor', 'viewer')
);
INSERT INTO users (username, role) VALUES ('john', 'admin');
INSERT INTO users (username, role) VALUES ('jane', 'editor');
INSERT INTO users (username, role) VALUES ('peter', 'viewer');
SELECT user_id, username, role FROM users WHERE role = 'admin';在这个例子中，ENUM 类型可以确保用户角色始终是有效的值，并且可以简化权限管理。
14. ENUM 与 JSON
在现代应用中，JSON 数据类型变得越来越流行。在某些情况下，我们可以使用 JSON 来存储 ENUM 类型的值。
例如，可以将 ENUM 值存储为 JSON 数组：
CREATE TABLE products (
product_id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(50),
colors JSON
);
INSERT INTO products (product_name, colors) VALUES ('t-shirt', '["red", "blue", "green"]');
SELECT product_name, colors FROM products WHERE JSON_CONTAINS(colors, '"red"');虽然 JSON 可以提供更大的灵活性，但它也缺乏 ENUM 类型的数据完整性约束。因此，在选择使用 ENUM 还是 JSON 时，需要权衡灵活性和数据完整性。
ENUM 的本质：数值索引，节省空间，约束数据
ENUM 类型在 MySQL 内部使用数值索引来表示字符串值，这节省了存储空间。它通过预定义的集合约束数据，提高数据质量，并且提供了在特定场景下优化查询性能的潜力。
何时权衡：数据完整性与灵活性
选择 ENUM 还是其他数据类型（如 VARCHAR 或 JSON）时，需要在数据完整性和灵活性之间进行权衡。ENUM 适合于固定、预定义的值集合，而其他类型则更适合于需要更高灵活性和可扩展性的场景。