mysql迁移必须显式关闭autocommit并用单事务包裹全部dml操作，严禁混入ddl；分批处理大批量数据，每批独立事务；校验需基于一致快照，事务无法保障跨系统一致性。
事务必须显式开启并包裹整个迁移逻辑
MySQL 默认是自动提交模式，AUTOCOMMIT=1，这意味着每条 INSERT、UPDATE 或 DELETE 都会立即落盘。数据迁移时若不手动关掉它，中间出错就无法回滚——哪怕你写了 START TRANSACTION，只要前面有没包住的语句，一致性就断了。
实操建议：
- 脚本开头必须执行
SET AUTOCOMMIT = 0，避免依赖客户端默认行为（不同连接工具可能不同） -
START TRANSACTION放在所有 DML 操作之前，COMMIT或ROLLBACK必须紧贴迁移逻辑结尾 - 不要把建表、加索引等 DDL 语句混进事务块——MySQL 中大多数 DDL 会隐式触发
COMMIT，导致事务提前结束 - 如果迁移涉及多张表，用单个事务包裹全部
INSERT ... SELECT或批量INSERT，别拆成多个小事务
遇到错误时 rollback 不生效的常见原因
写完 ROLLBACK 却发现部分数据已写入，大概率不是语法错，而是被 MySQL 的隐式提交机制“绕过去了”。典型现象：执行完 CREATE TABLE 后再 INSERT，结果 ROLLBACK 只撤回了 INSERT，新表还在。
常见触发隐式提交的操作（它们会让当前事务立刻结束）：
- 任何 DDL：如
CREATE、DROP、ALTER、TRUNCATE - 锁操作：
LOCK TABLES、UNLOCK TABLES - 管理语句：
ANALYZE TABLE、OPTIMIZE TABLE、REPAIR TABLE - 修改全局/会话变量：
SET GLOBAL、SET SESSION（但SET @var不会）
所以迁移脚本里，建表、改结构这些得放在事务外，且确保它们成功后再进事务做数据搬运。
大批量 INSERT 的性能与事务大小权衡
一个事务里塞几百万行 INSERT 看似“最安全”，但实际容易 OOM 或锁表太久，尤其在生产环境。MySQL 的 innodb_log_file_size 和 innodb_buffer_pool_size 会限制单事务能写的日志量，超限直接报 ERROR 1205 (HY000): Deadlock found when trying to get lock 或更隐蔽的 ERROR 1213 (40001)。
实操建议：
- 按主键范围或时间字段分批次迁移，比如每次处理 1 万行，用
WHERE id BETWEEN ? AND ?控制范围 - 每批用独立事务包裹，失败只重试该批，避免全量回滚
- 禁用唯一索引和外键检查（
SET FOREIGN_KEY_CHECKS = 0、SET UNIQUE_CHECKS = 0）仅限迁移前临时关闭，结束后立刻恢复 - 插入后记得
ANALYZE TABLE更新统计信息，否则后续查询可能走错执行计划
脚本中如何可靠判断迁移是否真正完成
光看 COMMIT 成功不等于数据就位。常见陷阱是：事务提交了，但从库还没同步完，或者应用层缓存没清，导致读到旧数据。另外，有些脚本用 SELECT COUNT(*) 校验行数，却忽略 WHERE 条件漏数据、或源表有未提交的脏读。
更靠谱的做法：
- 校验逻辑放在事务外，且用一致快照：先
START TRANSACTION WITH CONSISTENT SNAPSHOT，再查源表和目标表行数+关键字段校验和（如SUM(CRC32(col))） - 避免用
SELECT COUNT(*)对大表硬算，改用SHOW TABLE STATUS的Rows近似值做快速筛查（注意它是估算值，仅作初筛） - 如果启用了 GTID 复制，迁移后可查
SELECT MASTER_POS_WAIT(...)等待从库追上，再对外暴露新数据 - 别依赖客户端返回的 “Query OK” —— 它只表示语句被接受，不保证持久化，特别是开了
innodb_flush_log_at_trx_commit=2时
事务能兜住的是“本库本实例”的原子性，跨库、跨服务、缓存、消息队列这些，得靠额外机制对账，不是加个 START TRANSACTION 就万事大吉。
