mysql innodb事务回滚后自增id不回收是设计使然：语句执行阶段即预分配id，回滚仅撤销数据变更而非已分配id；insert ignore、on duplicate key update等操作均会触发预分配却可能不写入，导致跳号。
事务回滚后自增ID为什么没回收
MySQL InnoDB 的自增ID一旦被分配，就不可撤销——哪怕所在事务最终 ROLLBACK。这不是 bug，而是设计取舍：为了性能，InnoDB 在语句执行阶段就预分配 ID，不等事务提交。回滚只撤销数据变更，不撤销已分配的自增值。
常见错误现象：INSERT INTO t (name) VALUES ('x'); 执行后报错或手动回滚，再插入新记录时发现 ID 从 5 跳到 7，中间缺了 6。
- 根本原因：分配发生在语句解析/执行阶段，早于事务提交判断
- 影响范围：所有显式触发自增列生成的操作，包括
INSERT、INSERT IGNORE、INSERT ... ON DUPLICATE KEY UPDATE - MySQL 8.0+ 持久化了 AUTO_INCREMENT 值（写入 redo log），重启也不会“找回”跳掉的号
哪些操作会触发预分配但不真正写入
只要语句让 MySQL 认为“可能需要新 ID”，就会走自增分配流程，哪怕最终没插入成功。
-
INSERT IGNORE INTO t (name) VALUES ('duplicate')→ 唯一键冲突，ID 已分配但行未写入 -
INSERT INTO t (name) SELECT name FROM other_t WHERE id > 1000→ 即使子查询返回空集，InnoDB 仍可能预分配一批 ID（尤其在innodb_autoinc_lock_mode = 2下） -
INSERT ... ON DUPLICATE KEY UPDATE→ 和INSERT IGNORE共享同一套预分配逻辑，一样跳号 - 显式插入
NULL或省略自增列：必然触发分配；显式指定正整数 ID（如INSERT INTO t (id, name) VALUES (100, 'x')）则完全绕过自增机制
innodb_autoinc_lock_mode 如何放大或缓解跳号
这个参数不改变“跳号是否发生”，但影响跳号的幅度和并发行为。
-
innodb_autoinc_lock_mode = 0（传统锁表模式）：每次分配都锁全表，跳号稳定但并发差；适合低频、强一致性场景 -
innodb_autoinc_lock_mode = 1（默认）：简单 INSERT 用轻量 mutex，批量 INSERT（如INSERT ... SELECT）才锁表；跳号更常见，但性能好 -
innodb_autoinc_lock_mode = 2（交错模式）：并发高时预分配范围更大，跳号更剧烈，且主从复制需binlog_format = ROW，否则可能不一致
查当前值：SELECT @@innodb_autoinc_lock_mode;；改配置需重启或动态 SET（仅限会话级临时调整）。
真正能避免跳号的务实做法
完全杜绝跳号只有两个选择：不用自增主键，或不用任何可能触发预分配的语法。但多数业务可接受 ID 不连续，重点应放在可观测与防误用。
- 监控跳号速率：定期执行
SHOW TABLE STATUS LIKE 't'，比对AUTO_INCREMENT字段增长速度是否异常（比如每小时涨 1000 但实际只插几十条） - 避免用
DELETE + INSERT模拟“更新”：该用UPDATE或ON DUPLICATE KEY UPDATE就别手写两步 - 重置需谨慎：
ALTER TABLE t AUTO_INCREMENT = N必须确保 N 大于当前最大 ID，否则下次插入会报主键冲突 - 高并发唯一写入场景（如设备上报），优先用
INSERT ... ON DUPLICATE KEY UPDATE，别用SELECT + INSERT—— 后者虽不跳号，但多一次查询+加锁开销，反而更容易引发死锁
最常被忽略的一点：应用层如果基于自增 ID 做分页、导出序号或生成短链，要提前确认业务是否真的依赖“连续”。多数时候，ID 只是唯一标识，跳号本身不破坏数据一致性。
