MySQL DDL 隐式提交：「空跑验证」其实已写入生产

写好一份大型数据迁移脚本，我习惯先做"空跑验证"：把所有语句包在 START TRANSACTION ... ROLLBACK 里，跑一遍看计数器，确认没问题再换成 COMMIT。这个习惯在 PostgreSQL 上完全正确。但在 MySQL / MariaDB 上，一旦脚本里有 DDL（ALTER TABLE、CREATE TABLE、DROP TABLE……），那个 ROLLBACK 就成了摆设。

现象

迁移脚本结构大致如下：

START TRANSACTION;
 
ALTER TABLE records
  ADD COLUMN ext_id VARCHAR(40) NULL,
  ADD COLUMN ext_code VARCHAR(20) NULL,
  ADD INDEX idx_ext_id (ext_id);
 
UPDATE records SET ext_id = '...' WHERE id = '...';
-- ... 1000 行 UPDATE / INSERT ...
 
SELECT '计数器验证', COUNT(*) FROM records WHERE ext_id IS NOT NULL;
 
ROLLBACK;  -- 期望：什么都没留下

跑完之后，ROLLBACK 返回 "Query OK, 0 rows affected"——没有报错。但数据库里新列已经存在，所有更新也已持久化。再跑一次，直接报 Duplicate column name，说明上一次根本没有回滚。

根因

MySQL 官方文档 §13.3.3（Statements That Cause an Implicit Commit）明确规定：DDL 语句会立即隐式提交当前事务，并且不会开启新的事务。

触发隐式提交的语句包括：

DDL：CREATE / ALTER / DROP / RENAME / TRUNCATE（表、索引、视图、触发器、存储过程……）
账号管理：GRANT / REVOKE / CREATE USER / DROP USER
管理语句：ANALYZE TABLE / OPTIMIZE TABLE / LOCK TABLES / FLUSH
事务控制：嵌套的 START TRANSACTION / BEGIN 本身也会隐式提交前一个事务

执行到 ALTER TABLE 的瞬间，MySQL 就把之前 START TRANSACTION 开的事务提交了，会话回到 autocommit 状态。后续的 UPDATE / INSERT 每条都是独立自动提交的。最后的 ROLLBACK 面对一个空事务，只能静默返回 "0 rows affected"。

坑：DDL 让 ROLLBACK 变成无声 no-op

START TRANSACTION 之后紧跟 ALTER TABLE，事务当场被隐式提交。后续 DML 全部以 autocommit 模式运行。结尾的 ROLLBACK 什么都回滚不了，却不会抛任何错误。

诊断方式：在 ALTER 之后立即执行 SELECT @@in_transaction;，如果返回 0，说明你已经不在事务里了。

ALTER TABLE records ADD COLUMN ext_id VARCHAR(40) NULL;
SELECT @@in_transaction;  -- 返回 0，事务已被隐式提交

补充：SET autocommit=0 救不了你，SAVEPOINT 也救不了你，框架级的"事务包裹迁移"（Knex / TypeORM / Flyway 的 up()）同样救不了你。

另一个常见误解：MySQL 8 引入的"原子 DDL"并不意味着 DDL 可以参与用户事务。它只是保证 DDL 本身在崩溃恢复时的原子性（元数据 + 数据字典同步），隐式提交行为一点没变。这个坑对从 PostgreSQL 迁过来的工程师尤其致命——PostgreSQL 的大多数 DDL 是真正可回滚的，先入为主的直觉会害了你。

正解

把 DDL 和 DML 拆进两个独立脚本。

step1-alter.sql（DDL，接受不可逆）

-- DDL 独立文件，执行即提交，不包在事务里
-- 如需回滚请手动执行文末的反向 DDL
SET NAMES utf8mb4;
 
ALTER TABLE records
  ADD COLUMN ext_id   VARCHAR(40) NULL DEFAULT NULL AFTER code,
  ADD COLUMN ext_code VARCHAR(20) NULL DEFAULT NULL AFTER ext_id,
  ADD INDEX idx_ext_id (ext_id);
 
-- 紧急回滚配方（手动执行）：
-- ALTER TABLE records
--   DROP INDEX idx_ext_id,
--   DROP COLUMN ext_code,
--   DROP COLUMN ext_id;

step2-dml.sql（纯 DML，默认 ROLLBACK，验证后改 COMMIT）

-- 纯 DML，此文件内禁止出现 ALTER/CREATE/DROP/TRUNCATE
SET NAMES utf8mb4;
START TRANSACTION;
 
UPDATE records SET ext_id = '...' WHERE id = '...';
INSERT INTO records (...) VALUES (...);
-- ... 大量 DML ...
 
-- 内联验证（还在事务里，能看到未提交的写入）
SELECT '有 ext_id 的行数', COUNT(*) FROM records WHERE ext_id IS NOT NULL;
SELECT '总行数',           COUNT(*) FROM records WHERE is_deleted = 0;
 
-- 默认保留 ROLLBACK（空跑验证）
-- 确认计数器正确后，把下面两行对调再执行一次
ROLLBACK;
-- COMMIT;

执行流程：

# 第一步：应用 DDL（不可逆，确认再执行）
mysql -h host -u user -p dbname < step1-alter.sql
 
# 第二步：空跑验证（DML 不会持久化）
mysql -h host -u user -p dbname < step2-dml.sql
# 检查计数器，无报错、无警告
 
# 第三步：翻转最后一行，正式写入
sed -i '' 's/^ROLLBACK;$/COMMIT;/' step2-dml.sql
mysql -h host -u user -p dbname < step2-dml.sql

验证空跑是否真的回滚：

-- 空跑后执行，应等于迁移前的行数
SELECT COUNT(*) FROM records;
 
-- 应返回 0（新列所有值仍为 NULL）
SELECT COUNT(*) FROM records WHERE ext_id IS NOT NULL;

正解：DDL 单独跑，DML 单独验证

DDL 和 DML 必须物理上分到两个文件 / 两个执行阶段。只有纯 DML 脚本才能用 START TRANSACTION ... ROLLBACK 做真正的空跑验证。DDL 文件接受"执行即提交"，配套写好反向 DDL 作为应急回滚方案。

一句话外卖

MySQL 里 DDL 会隐式提交当前事务——凡含 ALTER/CREATE/DROP 的脚本，结尾的 ROLLBACK 都是无声的 no-op；想做真正的空跑验证，必须把 DDL 和 DML 拆成两个独立脚本。