文章目录

参考
描述
枚举类型
- - 枚举类型
  - 创建枚举类型
  - 优劣
  - - - 优点
      - 缺点
枚举值与其索引
- - 映射
  - 检索索引
  - 插入的数值与数值字符串都将被解释为索引
  - 由枚举值索引带来的优势
  - 枚举值的比较
  - 枚举值的排序
枚举值不可为表达式
约束与枚举列的默认值
- - NULL 约束
  - NOT NULL 约束
  - DEFAULT
尾部空格的自动清除
严格模式与非严格模式
- - Strict Mode 与 Non-Strict Mode
  - sql-mode
  - 无效值的处理方式
  - - - 严格模式
      - 非严格模式
      - 检索枚举列中的无效值
  - 相同枚举值的处理方式
枚举值排序的特殊情形

参考

项目	描述
MySQL 官方文档	The ENUM Type
搜索引擎	Google 、Bing

描述

项目	描述
DataGrip	2023.1.1
MySQL	8.0.33 MySQL Community Server - GPL

枚举类型

在 MySQL 中，枚举类型属于字符串类型的一种，但是与一般的字符串类型不同，它具有一个预定义的值域。在创建表时，您可以指定枚举类型的可能值，然后在 插入 或 更新 数据时，只能选择这些预定义值之一。

创建枚举类型

在 MySQL 中，可以使用 ENUM 关键字来创建枚举类型。下面是创建枚举类型的基本语法：

CREATE TABLE table_name (column_name ENUM('value1', 'value2', 'value3', ...)
);

其中：

table_name 是要创建的表的名称，column_name 是要创建的枚举列的名称。在 ENUM 关键字后面的括号中，列出了枚举类型的预定义值，以逗号分隔。
在 MySQL 中，ENUM 类型的预定义值的数量是有限的，最多可以有 65535 个预定义值。

举个栗子

# 创建一个数据库用于测试
CREATE DATABASE db_test;# 使用数据库 db_test
USE db_test;# 在当前数据库中创建表 tb
CREATE TABLE tb
(name VARCHAR(15),# 创建一个枚举列，该列仅支持使用预定义值 male 和 femalesex  ENUM ('male', 'female')
);# 向表 tb 中插入数据
INSERT INTO tb
VALUES ('RedHeart', 'male'),('TwoMoons', 'female');# 从表 tb 中查询所有数据
SELECT *
FROM tb;

查询结果

+----------+--------+
| name     | sex    |
+----------+--------+
| RedHeart | male   |
| TwoMoons | female |
+----------+--------+
2 rows in set (0.00 sec)

注：

枚举类型的预定义值仅支持为 字符串，使用其他类型的值作为枚举类型的预定义值时，MySQL 将抛出异常错误。

优劣

优点

优点	描述
数据完整性	枚举类型限制列中存储的值仅限于预定义的选项之一，确保数据的完整性，`防止插入或更新不正确的值`。
易于理解和维护	枚举类型使用预定义的选项，使 `数据的含义更加清晰和易于理解`。开发人员可以轻松查看和理解列中的可能值，从而简化数据库的维护。
性能优化	`枚举类型的内部实现使用整数值表示选项`，相比于存储字符串值，它可以提供更好的性能。枚举类型所需的存储空间更小，并且在比较和索引操作中更高效。

缺点

如果您预计将来可能需要更改或添加选项，枚举类型可能不太适合，因为在枚举类型中 添加或修改预定义值需要更改表结构。

修改表结构的过程可能会引入一些复杂性和风险，特别是在有大量数据和与枚举列相关联的其他表之间存在关联关系时。因此，在预计将来可能需要更改或添加选项的情况下，使用枚举类型可能并不是最佳选择。

相比之下，如果您使用 查找表 或 标志位（BIT） 等其他方法来处理动态选项，您可以更轻松地进行更改和添加选项，而无需修改表结构。这些方法提供了更大的灵活性和可扩展性，适用于变化频繁的选项。

枚举值与其索引

映射

在 MySQL 中，枚举类型中的枚举值对应的索引是指每个枚举值在枚举类型内部的 位置。当定义枚举类型时，每个枚举值都被映射到一个 整数值，这个整数值就是枚举值的索引。

默认情况下，MySQL会将第一个枚举值的索引设置为 1，第二个枚举值的索引设置为 2，依此类推。在内部，MySQL 使用这些整数值来存储和比较枚举列的值。

检索索引

如果您在数值上下文中检索枚举值，将返回列值的索引。例如，您可以像这样从枚举列中检索数字值：

SELECT enum_col+0 FROM tbl_name;

举个栗子

CREATE DATABASE db_test;USE db_test;CREATE TABLE tb
(name VARCHAR(15),sex  ENUM ('male', 'female')
);INSERT INTO tb
VALUES ('RedHeart', 'male'),('TwoMoons', 'female');SELECT name, sex
FROM tb;SELECT name, sex + 0
FROM tb;

查询结果

# SELECT name, sex
# FROM tb;
+----------+--------+
| name     | sex    |
+----------+--------+
| RedHeart | male   |
| TwoMoons | female |
+----------+--------+
2 rows in set (0.00 sec)# SELECT name, sex + 0
# FROM tb;
+----------+---------+
| name     | sex + 0 |
+----------+---------+
| RedHeart |       1 |
| TwoMoons |       2 |
+----------+---------+
2 rows in set (0.00 sec)

插入的数值与数值字符串都将被解释为索引

在 MySQL 中，对于 ENUM 列，插入的值可以是一个数值或数值字符串（使用引号包裹的数值），而不仅仅是非数值字符串。

当插入的值是一个 数值字符串 或 数值 时，MySQL会尝试将其转化为一个整数，并将其视为枚举值的索引。

举个栗子

CREATE DATABASE db_test;USE db_test;CREATE TABLE tb
(name VARCHAR(15),sex  ENUM ('male', 'female')
);# 将数值或数值字符串插入枚举列时，
# 数值与数值字符串都将被解析为枚举值的索引。
INSERT INTO tb
VALUES ('RedHeart', 1),('TwoMoons', '2');SELECT *
FROM tb;

执行效果

+----------+--------+
| name     | sex    |
+----------+--------+
| RedHeart | male   |
| TwoMoons | female |
+----------+--------+
2 rows in set (0.00 sec)

由枚举值索引带来的优势

存储空间
由于枚举类型内部使用 整数值 来表示枚举值，相比存储相应的字符串值，它可以 节省存储空间。整数值通常需要较少的字节数来存储。
比较和排序效率
由于枚举值被映射到整数索引，比较 和 排序 枚举列的值通常比直接比较和排序字符串值更高效。整数比较是直接的数值比较（相比之下，字符串比较需要比较每个字符转换为编码的结果），速度更快。

需要注意的是，虽然枚举值的索引对于 MySQL 内部处理枚举类型很重要，但在使用枚举列时，我们不需要直接操作这些索引。我们可以直接使用枚举值进行查询、比较和排序操作，而 MySQL 会在内部将其处理为相应的索引值。

枚举值的比较

在枚举列中，枚举值的比较是通过比较其对应的整数索引来实现的。每个枚举值都被映射到一个整数值，这个整数值是其在枚举类型中的索引位置。

CREATE TABLE tb (status ENUM('active', 'inactive', 'pending')
);

在这个示例中，枚举列 status 包含三个枚举值：active、inactive 和 pending。这些枚举值分别被映射到整数索引 0、1 和 2。

当对枚举列进行比较时，实际上是在比较整数索引。例如，以下查询将返回status 列值为 active 的行：

SELECT * FROM tb WHERE status = 'active';

在执行此查询时，MySQL 首先会将字符串 active 与枚举列中的每个枚举值进行比较。因为 active 对应的整数索引是 0，MySQL实际上会执行以下比较操作：

SELECT * FROM tb WHERE status = 0;

MySQL 将比较整数索引 0 和枚举列中每个值的索引，如果匹配则返回相应的行。

通过将枚举值映射到整数索引，比较过程变得更加高效。MySQL 可以直接进行整数比较，而不需要解析和比较字符串的每个字符，这提高了比较操作的性能。

枚举值的排序

枚举值在排序时是基于其对应的整数索引进行排序的。由于枚举值被映射到整数索引，对枚举列进行排序实际上是对整数索引进行排序。

考虑以下 ENUM 列的定义：

CREATE TABLE tb (process VARCHAR(15),status ENUM('active', 'inactive', 'pending')
);

在这个例子中，枚举值 active 具有索引 1，inactive 具有索引2，pending' 具有索引 3。因此，默认情况下，对 status 列进行排序时，active 将排在前面，inactive 在其后，pending 排在最后。

举个栗子

CREATE DATABASE db_test;USE db_test;CREATE TABLE tb
(process VARCHAR(15),status  ENUM ('active', 'inactive', 'pending')
);INSERT INTO tb
VALUES ('RedHeart', '3'),('TwoMoons', '1'),('BinaryMoon', '2');SELECT *
FROM tb
ORDER BY status;

执行效果

+------------+----------+
| process    | status   |
+------------+----------+
| TwoMoons   | active   |
| BinaryMoon | inactive |
| RedHeart   | pending  |
+------------+----------+
3 rows in set (0.00 sec)

枚举值不可为表达式

枚举值在MySQL中不能包含表达式。枚举值应该是预定义的常量值，而不是动态计算的表达式（即使这些表达式返回的结果为一个字符串常量）。对此，请参考如下语句：

CREATE TABLE tb
(process VARCHAR(15),status  ENUM ('active', CONCAT('in', 'active'), 'pending')
);

MySQL 在执行上述语句时将产生异常错误。

约束与枚举列的默认值

若枚举列使用约束 NULL ，且在插入过程中省略对枚举值的插入，则默认将插入 NULL 作为枚举值。若枚举列使用约束 NOT NULL，且在插入过程中省略对枚举值的插入，则默认将插入该枚举类型的索引 1 。

当然，你可以通过 DEFAULT 约束来制约 NULL 与 NOT NULL 约束所带来的影响。

NULL 约束

CREATE DATABASE db_test;USE db_test;CREATE TABLE tb
(process VARCHAR(15),# 为枚举列添加 NULL 约束status  ENUM ('active', 'inactive', 'pending') NULL
);INSERT INTO tb (process)
VALUES ('RedHeart'),('TwoMoons'),('BinaryMoon');SELECT *
FROM tb;

查询结果

+------------+--------+
| process    | status |
+------------+--------+
| RedHeart   | NULL   |
| TwoMoons   | NULL   |
| BinaryMoon | NULL   |
+------------+--------+
3 rows in set (0.00 sec)

NOT NULL 约束

CREATE DATABASE db_test;USE db_test;CREATE TABLE tb
(process VARCHAR(15),# 为枚举列添加 NOT NULL 约束status  ENUM ('active', 'inactive', 'pending') NOT NULL
);INSERT INTO tb (process)
VALUES ('RedHeart'),('TwoMoons'),('BinaryMoon');SELECT *
FROM tb;

查询结果

+------------+--------+
| process    | status |
+------------+--------+
| RedHeart   | active |
| TwoMoons   | active |
| BinaryMoon | active |
+------------+--------+
3 rows in set (0.00 sec)

DEFAULT

CREATE DATABASE db_test;USE db_test;CREATE TABLE tb
(process VARCHAR(15),# 为枚举列添加 DEFAULT 约束status  ENUM ('active', 'inactive', 'pending') DEFAULT 'pending'
);INSERT INTO tb (process)
VALUES ('RedHeart'),('TwoMoons'),('BinaryMoon');SELECT *
FROM tb;

查询结果

+------------+---------+
| process    | status  |
+------------+---------+
| RedHeart   | pending |
| TwoMoons   | pending |
| BinaryMoon | pending |
+------------+---------+
3 rows in set (0.00 sec)

注：

在使用 DEFAULT 约束为枚举列指定默认值时，默认值必须为有效的枚举值（不可为数值或数值字符串）。否则，MySQL 将为此抛出异常错误。

尾部空格的自动清除

在 MySQL 中，枚举类型的预定义值在存储时会 自动 去除尾部的空格。当将值插入枚举列之前，MySQL 会 清除 该值的尾部空格，并按照一定的规则将其 转换 为对应的索引值后进行 存储。

CREATE DATABASE db_test;USE db_test;CREATE TABLE tb
(process VARCHAR(15),# 为每一个枚举值的末尾添加三个半角空格status  ENUM ('active   ', 'inactive   ', 'pending   ')
);INSERT INTO tb
# 为每一个插入的值的尾部添加六个半角空格
VALUES ('RedHeart', 'active      '),('TwoMoons', 'active      '),('BinaryMoon', '3      ');SELECT process, CONCAT('(', status, ')')
FROM tb;

对于上述代码，有：

创建表时，在枚举列 status 的每一个预定义值后面都添加了三个半角空格。枚举值在存储于 枚举类型 中前，尾部的空格将会被自动去除。
在插入数据时，每个插入值的尾部都添加了六个半角空格。即使插入值带有额外的尾部空格，MySQL 也会在插入前将其 清除。因此，向枚举列插入的三个值均能够于枚举类型中存储的值进行匹配。在匹配成功后，MySQL 将会把枚举值对应的索引存储于表中。
在查询数据时，通过使用 CONCAT 函数将 status 列的值和括号拼接在一起进行显示。这是为了展示查询结果中的枚举值是否存在尾部的空格。

查询结果

+------------+--------------------------+
| process    | CONCAT('(', status, ')') |
+------------+--------------------------+
| RedHeart   | (active)                 |
| TwoMoons   | (active)                 |
| BinaryMoon | (pending)                |
+------------+--------------------------+
3 rows in set (0.00 sec)

严格模式与非严格模式

Strict Mode 与 Non-Strict Mode

在 MySQL 中，有两种模式 用于控制数据处理的严格性，分别是 严格模式（Strict Mode） 和 非严格模式（Non-Strict Mode），它们对于数据处理和校验的方式有所不同。

严格模式（Strict Mode）
- 在严格模式下，MySQL 会对数据进行严格的校验和处理，确保数据的 完整性 和 一致性。
- 如果数据不符合定义的数据类型、约束条件或其他规则，MySQL会 抛出错误并终止相应的操作。
- 严格模式可以 确保更高的数据质量，但也要求开发者严格遵守定义的数据规范和限制。
非严格模式（Non-Strict Mode）
- 在非严格模式下，MySQL 对数据的校验和处理相对宽松，允许一些不严格的数据转换和处理。
- 如果数据不符合定义的数据类型或约束条件，MySQL 会 尝试自动进行数据转换或截断，而不会抛出错误。
- 非严格模式下的处理方式 可能会导致数据的丢失或截断，所以开发者 需要更加小心地处理数据。

sql-mode

在 MySQ L的最新版本中，默认情况 下严格模式是启用的，但在旧版本中可能默认为非严格模式。开发者可以根据需要在 MySQL 的配置文件或会话级别设置 sql_mode 参数来启用或禁用严格模式。

无效值的处理方式

在 MySQL 中，在 插入值无法与枚举值进行匹配 或 被解析为无效的枚举值索引 时，严格模式和非严格模式下的 MySQL 将会产生不同的反应。

注：

在严格模式和非严格模式下，对于有效的枚举值插入，MySQL 都会将其存储为 对应的枚举值索引。只有在插入的值无法匹配或解析为有效的枚举值索引时，严格模式和非严格模式下的行为有所不同。

严格模式

如果插入的值无法与枚举列的任何预定义值匹配或被解析为无效的枚举值索引，MySQL 将会 抛出异常错误信息，并中止插入操作 以确保数据的完整性和一致性。

非严格模式

如果插入的值无法与枚举列的任何预定义值匹配或被解析为无效的枚举值索引，MySQL 将会在枚举列中存储索引值零。在检索过程中，若从枚举列中检索到的值为索引零，则将其转化为一个空字符串。对此，请参考如下示例：

CREATE DATABASE db_test;USE db_test;CREATE TABLE tb
(process VARCHAR(15),status  ENUM ('active', 'inactive', 'pending')
);# 关闭 MySQL 的严格模式（简单粗暴）
SET sql_mode = '';INSERT INTO tb
VALUES ('RedHeart', 666),('TwoMoons', 'RedHeart'),('BinaryMoon', '520');SELECT process, CONCAT('(', status, ')')
FROM tb;

查询结果

+------------+--------------------------+
| process    | CONCAT('(', status, ')') |
+------------+--------------------------+
| RedHeart   | ()                       |
| TwoMoons   | ()                       |
| BinaryMoon | ()                       |
+------------+--------------------------+
3 rows in set (0.00 sec)

检索枚举列中的无效值

无效值的索引值为 0。这意味着您可以使用以下 SELECT 语句查找被赋予无效枚举值的行：

SELECT * FROM tbl_name WHERE enum_col=0;

举个栗子

CREATE DATABASE db_test;USE db_test;CREATE TABLE tb
(process VARCHAR(15),# 枚举值列表中支持所有字符串，包括空字符串。# 枚举值列表中的空字符串将转换为对应的枚举值索引。# 而枚举值索引 0 将被转换为空字符串。status  ENUM ('active', 'inactive', 'pending', '')
);# 关闭 MySQL 严格模式（简单粗暴）
SET sql_mode = '';INSERT INTO tb
VALUES ('RedHeart', 1),('TwoMoons', 'RedHeart'),('BinaryMoon', '');# 通过枚举类型的默认值机制向枚举列中插入 NULL 值
INSERT INTO tb (process)
VALUES ('ZZH');SELECT process, CONCAT('(', status, ')')
FROM tb;# 检索枚举列中的无效值
SELECT process, CONCAT('(', status, ')')
FROM tb WHERE status = 0;

查询结果

# SELECT process, CONCAT('(', status, ')')
# FROM tb;
+------------+--------------------------+
| process    | CONCAT('(', status, ')') |
+------------+--------------------------+
| RedHeart   | (active)                 |
| TwoMoons   | ()                       |
| BinaryMoon | ()                       |
| ZZH        | NULL                     |
+------------+--------------------------+
4 rows in set (0.00 sec)# SELECT process, CONCAT('(', status, ')')
# FROM tb WHERE status = 0;
+----------+--------------------------+
| process  | CONCAT('(', status, ')') |
+----------+--------------------------+
| TwoMoons | ()                       |
+----------+--------------------------+
1 row in set (0.00 sec)

相同枚举值的处理方式

在创建枚举列的过程中，不要为同一枚举列指定多个相同的枚举值。在 MySQL 的 严格模式 下，MySQL 将为此抛出错误。

枚举值排序的特殊情形

枚举值根据它们的索引号进行排序，索引号取决于在列定义中列出枚举成员的顺序。若对包含 NULL 值的枚举列进行排序，NULL 将会排在所有其他枚举值之前。对此，请参考如下示例：

CREATE DATABASE db_test;USE db_test;CREATE TABLE tb
(process VARCHAR(15),status  ENUM ('active', 'inactive', 'pending', '')
);SET sql_mode = '';INSERT INTO tb
VALUES ('RedHeart', 1),('TwoMoons', 'RedHeart'),('BinaryMoon', '');INSERT INTO tb (process)
VALUES ('ZZH');SELECT process, CONCAT('(', status, ')')
FROM tb ORDER BY status;

查询结果

+------------+--------------------------+
| process    | CONCAT('(', status, ')') |
+------------+--------------------------+
| ZZH        | NULL                     |
| TwoMoons   | ()                       |
| RedHeart   | (active)                 |
| BinaryMoon | ()                       |
+------------+--------------------------+
4 rows in set (0.00 sec)

存储之道：MySQL 中的字符串数据类型之 ENUM

文章目录

参考

描述

枚举类型

枚举类型

创建枚举类型

优劣

优点

缺点

枚举值与其索引

映射

检索索引

插入的数值与数值字符串都将被解释为索引

由枚举值索引带来的优势

枚举值的比较

枚举值的排序

枚举值不可为表达式

约束与枚举列的默认值

NULL 约束

NOT NULL 约束

DEFAULT

尾部空格的自动清除

严格模式与非严格模式

Strict Mode 与 Non-Strict Mode

sql-mode

无效值的处理方式

严格模式

非严格模式

检索枚举列中的无效值

相同枚举值的处理方式

枚举值排序的特殊情形

相关文章