如何避免SQL中因插入失败导致的自增ID断号？

这个问题我之前帮不少开发者捋过——InnoDB自增ID跳号确实是个容易踩的坑，咱们先搞清楚根源，再看对应的解决办法：

InnoDB的自增计数器是在事务启动前就预分配好ID值的，哪怕后续因为用户名重复触发唯一键冲突、导致事务回滚，已经预分配的ID也不会被回收。这是InnoDB为了保证并发插入性能做的设计——如果每次回滚都要重置计数器，会带来大量锁竞争和性能损耗。

1. 原子性检查+插入（推荐，不影响性能） #

别用先查询再插入的方式（这种有并发漏洞，比如两个请求同时查发现用户名可用，然后同时插入还是会冲突），改用INSERT ... SELECT结合NOT EXISTS的原子操作，只有当用户名不存在时才执行插入：

INSERT INTO your_table (username, col1, col2)
SELECT 'target_username', 'val1', 'val2'
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM your_table WHERE username = 'target_username');

这种方式下，只有插入成功时自增ID才会递增，冲突的请求不会触发ID预分配，自然不会跳号。

2. 调整自增锁模式（不推荐高并发场景） #

如果你的系统并发量很低，可以把InnoDB的自增锁模式改成传统模式（innodb_autoinc_lock_mode=0），这种模式会用表级锁，只有事务提交后才会递增计数器，回滚时会回收ID。但代价是批量插入的性能会大幅下降，因为所有插入操作都要排队等锁。

修改方式：

• 临时生效：SET GLOBAL innodb_autoinc_lock_mode = 0;（重启MySQL后失效）
• 永久生效：在my.cnf/my.ini里加innodb_autoinc_lock_mode=0，然后重启服务。

3. 自定义序列（适合必须要连续ID的场景） #

如果业务强要求ID必须绝对连续（比如某些单据编号），那自增ID就不适合了，建议自己维护一个序列表：

1. 先创建序列表：

CREATE TABLE id_sequence (
  table_name VARCHAR(50) PRIMARY KEY,
  next_id INT NOT NULL DEFAULT 1
);
INSERT INTO id_sequence (table_name) VALUES ('your_table');

2. 每次插入前用事务获取并递增ID：

BEGIN;
SELECT next_id INTO @new_id FROM id_sequence WHERE table_name = 'your_table' FOR UPDATE;
UPDATE id_sequence SET next_id = next_id + 1 WHERE table_name = 'your_table';
INSERT INTO your_table (id, username, col1) VALUES (@new_id, 'target_username', 'val1');
COMMIT;

这种方式能保证ID完全连续，但会引入额外的事务和锁操作，性能比自增ID差，只适合对ID连续性要求极高的场景。

其实大多数业务场景下，自增ID跳号完全不影响系统功能——ID只是个唯一标识，不需要连续。强行追求连续ID反而可能带来性能瓶颈，所以优先考虑第一种方法，或者直接接受跳号的情况也没问题。

内容的提问来源于stack exchange，提问作者sdf