编程 给 ENUM 加成员能走 INSTANT,删成员却报 ERROR 1846:判据在字节数和成员位置

2026-11-22 00:03:39

给 ENUM 加成员能走 INSTANT,删成员却报 ERROR 1846:判据在字节数和成员位置

先说结论:MySQL 8.0 里对 ENUM 列执行 MODIFY COLUMN,能不能用 ALGORITHM=INSTANT,不看“改了 ENUM 定义”这件事,而看两件事:存储字节数有没有跨档,成员位置有没有动。末尾追加且不跨档,INSTANT/INPLACE 都可以;跨档、中间插入、删除成员,都会落到 table copy。

结论表

变更存储字节INSTANTINPLACE重建表并发 DML
末尾追加成员,成员数仍在 1–2551 字节不变支持支持否允许
末尾追加成员,成员数仍在 256–655352 字节不变支持支持否允许
末尾追加导致 255 → 256 跨档1 字节变 2 字节不支持不支持是不允许
在成员列表中间插入可能不变不支持不支持是不允许
删除成员可能不变不支持不支持是不允许

手册 17.12.1 Online DDL Operations 里那一行写的是:

OperationInstantIn PlaceRebuilds TablePermits Concurrent DMLOnly Modifies Metadata
Modifying the definition of an ENUM or SET columnYesYesNoYesYes

Rebuilds Table: No 是有条件的,条件写在同页的 Usage Notes 里,不在表格里。

ERROR 1846 (0A000) 的两种结果

同一张表,同样用 MODIFY COLUMN 重写 ENUM 定义,只是成员列表不同,结果完全相反。

删成员,从 14 个减到 4 个:

mysql> ALTER TABLE t1 MODIFY COLUMN c1 ENUM('a','b','c','d'), ALGORITHM=INSTANT;
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY/INPLACE.

报错信息里 Reason: Cannot change column type INPLACE 这句话容易误导,像是类型不兼容。实际情况是这条 DDL 本身需要 table copy,而 ALGORITHM=INSTANT 不允许重建表,所以在解析阶段就被拒了。Try ALGORITHM=COPY/INPLACE 是提示,未指定 ALGORITHM 时 MySQL 会自己回落到 COPY。

追加到 14 个成员,则直接成功:

mysql> ALTER TABLE t1 MODIFY COLUMN c1 ENUM('a','b','c','d','e','f','g','h','i','j','k','l','m','n'), ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.00 sec)

0 rows affected 是真的没动数据行,只改了元数据里的成员定义列表。

判据一:存储字节数是否变化

手册 Usage Notes 原文:

Modifying the definition of an ENUM or SET column by adding new enumeration or set members to the end of the list of valid member values may be performed instantly or in place, as long as the storage size of the data type does not change.

“只要数据类型的存储大小不变”是关键限定。ENUM 的存储大小由成员总数决定,见 13.7 Data Type Storage Requirements:1–255 个成员占 1 字节,256–65535 个成员占 2 字节。ENUM 列最多 65535 个不同元素。

所以一个 ENUM 有 255 个成员时,末尾再加第 256 个,每个值从 1 字节变 2 字节,数据行的物理宽度变了,必须 table copy。反过来 256 个成员减到 255 个也是跨档,同样不行。

手册在 SET 上举了同类例子:给已有 8 个成员的 SET 列再加一个成员,每值所需空间从 1 字节变成 2 字节,这需要 table copy。ENUM 的 255/256 边界是同一个机制。

判据可以简化成一句:先数新定义的成员数落在哪一档,再看旧定义落在哪一档,同档才有 INSTANT 的可能。

判据二:成员位置是否动了

手册原文:

Adding members in the middle of the list causes renumbering of existing members, which requires a table copy.

数据行里存的是索引,不是字符串。在列表中间插入一个成员,后面所有成员的编号整体后移,已有的行如果不重写,索引指向的字符串就全错了。所以中间插入必须把整表按旧定义解码、再按新定义编码,也就是 table copy。

删除成员同理,剩下成员的编号全部前移,只能重建。这也是上面那条 ERROR 1846 的真实原因:14 个减到 4 个,'d' 之后的成员编号全都变了。

末尾追加之所以便宜,是因为已有成员的编号一个都不动,新成员只是往定义列表尾部追加编号,数据行里的旧索引仍然有效。新增成员在已有数据里值为 0(即 ''),只有真的写入才生效。

判据三:ALGORITHM 和 LOCK 的搭配

未指定 ALGORITHM 时,MySQL 依次尝试 INSTANT → INPLACE → COPY,取第一个能用的。这就是为什么很多 ENUM 变更不加算法子句也能跑,但可能悄悄退化成 COPY,锁表和耗时都按 COPY 算。

指定了不支持的 ALGORITHM 则直接失败,不会回落,例如上面那条显式写 ALGORITHM=INSTANT 的删除操作。

LOCK 子句的约束:仅 LOCK=DEFAULT 允许与 ALGORITHM=INSTANT 搭配,其他 LOCK 子句参数不适用于 INSTANT。实际写法上就是:

ALTER TABLE t1 MODIFY COLUMN c1 ENUM(...), ALGORITHM=INSTANT, LOCK=DEFAULT;

想用 LOCK=NONE 显式表达“允许并发 DML”时,别和 ALGORITHM=INSTANT 混写,交给默认值即可。

判据四:索引语义决定脏数据长什么样

枚举成员按定义顺序从 1 开始编号。ENUM('a','b','c') 的索引就是 'a'=1、'b'=2、'c'=3。

空字符串错误值的索引是 0。非严格 SQL mode 下插入不在列表里的值,MySQL 不报错,写入 '',数值上就是 0。这个值肉眼和合法的空字符串成员长得一样,只能靠数值判断:

SELECT * FROM tbl WHERE enum_col = 0;

严格 SQL mode 下同样的插入直接报错,不会落脏数据。生产库如果关过 strict mode,历史数据里可能已经有一批索引 0 的行。

NULL 的索引是 NULL,与 0 不是一回事,= 0 查不出来,要用 IS NULL。

SELECT enum_col+0 返回索引数字。SUM()、AVG() 这类聚合也会按索引计算,不是在算字符串。

枚举值只能是字面量,不能是表达式或用户变量:

ENUM('small', CONCAT('med','ium'))  -- 不合法

判据五:排序走索引,不走字典序

ENUM 列 ORDER BY 按索引排序,不是按字典序。ENUM('b','a') 里 'b' 的索引是 1、'a' 是 2,所以 'b' 排在 'a' 前面。

顺序规则:NULL 排最前,''(索引 0)排在所有非空字符串之前,其余按成员定义顺序。

要字典序得显式转换:

ORDER BY CAST(col AS CHAR)
ORDER BY CONCAT(col)

这条在把 ENUM 当状态字段用时最容易踩:定义顺序是业务写的枚举顺序,不是字母顺序,报表里直接 ORDER BY status 出来的顺序跟预期对不上。

判据六:数字字面量会被当成索引

列定义是 numbers ENUM('0','1','2'),字符串值是 '0'、'1'、'2',但索引是 1、2、3。插入时传数字和传字符串走的是两条路径:

mysql> INSERT INTO t (numbers) VALUES(2),('2'),('3');
mysql> SELECT * FROM t;
+---------+
| numbers |
+---------+
| 1       |
| 2       |
| 2       |
+---------+

逐条对:

  • 存 2(数字):没有匹配的字符串成员,按索引处理,索引 2 对应的值是 '1'。
  • 存 '2'(字符串):匹配到成员 '2',索引 2,存进去就是 '2'。
  • 存 '3'(字符串):无匹配成员,退化成索引 3,对应 '2'。

三行结果里两行是 '2',来源完全不同:一行是字符串精确匹配,一行是越界索引兜底。这也是为什么 ENUM 列的插入值要统一加引号,数字不加引号时行为依赖成员值长什么样,非常难排查。

中间插入为什么不“错乱”但很贵

数据行存的是索引。COPY 重建时,MySQL 按旧定义把索引解码成字符串,再按新定义重新编码,所以重建完成后每个字符串值都是对的,业务读到的值不会变。

真正会错的是绕过 MySQL 的那一层:

  • 应用代码或配置里硬编码了索引数字,比如“3 表示已完成”。
  • 报表、离线任务用 col+0 落库并做了分组统计,成员顺序一改,同一数字对应的业务含义就换了。
  • 缓存里存的是数值而不是字符串。

这类问题在 DDL 执行成功之后才暴露,且不会有任何报错。变更 ENUM 定义时,除了确认 ALGORITHM 能不能走 INSTANT,还得同步排查一遍所有按数值消费这列的地方。整数索引是 MySQL 内部编码,不该出现在业务契约里。

复制全文 生成海报 枚举值 ENUM MySQL Online DDL ALTER TABLE 数据库

推荐文章

程序员茄子在线接单