MySQL 中自增 ID 不连续的原因

sql CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) UNIQUE );

INSERT INTO users (username) VALUES ('alice'); -- id=1 INSERT INTO users (username) VALUES ('bob'); -- id=2 -- 下面这条语句因唯一键冲突而失败 INSERT INTO users (username) VALUES ('alice'); -- 尝试插入失败，但自增值已从2变为3 INSERT INTO users (username) VALUES ('charlie'); -- id=4，跳过了 3

在这个场景中，`3` 被永久跳过，因为申请自增值的操作发生在实际插入数据之前，并且不会因为语句执行失败而回滚。

### 2. 显式删除记录

`DELETE` 操作删除某些行后，自增计数器并不会回退。新插入的行只会基于当前计数器继续向后分配 ID，不会复用被删除的 ID。

```sql
DELETE FROM users WHERE id = 2;
INSERT INTO users (username) VALUES ('dave'); -- id=5，而不是 2

这是最直观的不连续原因，通常也是预期内的行为。

3. 事务回滚（InnoDB 特性）

在 InnoDB 存储引擎中，如果在一个事务中执行了插入操作，随后该事务被回滚（ROLLBACK），已经分配的自增值不会被回收。这样做的目的是为了保证多个并发事务之间不会因为自增值的“复用”而产生冲突，从而避免复杂的锁竞争。

START TRANSACTION;
INSERT INTO users (username) VALUES ('eve'); -- 假设此时自增值从 5 变成 6
ROLLBACK;                                    -- 回滚，但自增值仍然是 7（准备给下一个插入）

注意：即使是单条 INSERT 语句（自动提交事务），如果因为外键约束或者语句级错误导致回滚，同样会消耗自增值。

4. INSERT ... ON DUPLICATE KEY UPDATE 的“特异”行为

在处理 ON DUPLICATE KEY UPDATE 时，MySQL 的早期版本（5.7 之前，且 innodb_autoinc_lock_mode 配置为某些模式）可能会出现即使最终执行的是更新操作，也仍会消耗一个自增值的现象。虽然新版本默认行为有所改善，但在部分配置下仍然可能造成 ID 空洞。

-- 假设表中已经存在 username='alice' 的记录
INSERT INTO users (username) VALUES ('alice')
ON DUPLICATE KEY UPDATE username = 'alice_updated';
-- 这条语句实际执行了更新，但某些模式中仍会预先消耗一个自增值，导致下一个 INSERT 的 ID 跳跃。

如果你使用的是 MySQL 5.7 及以上且 innodb_autoinc_lock_mode=1 或 2，ON DUPLICATE KEY UPDATE 通常不会浪费 ID，但在批量插入场景仍需留意。

5. INSERT IGNORE 与批量插入的间隙

和 ON DUPLICATE KEY UPDATE 类似，INSERT IGNORE 遇到重复键时会忽略插入，但同样可能已经申请了自增值。另外，在执行 INSERT INTO ... SELECT 或批量 INSERT 多行时，MySQL 会一次性分配一段连续的自增值。如果批量插入的预估数量大于实际成功插入的数量，就会在序列中留下空洞。

INSERT IGNORE INTO users (username) VALUES ('alice'); -- 实际被忽略，但自增值增加

6. auto_increment_increment 和 auto_increment_offset 的设置

这两个系统变量主要用于多主复制或需要 ID 区间隔离的场景：

• auto_increment_increment：自增步长。
• auto_increment_offset：自增起始偏移量。

如果步长被设置为大于 1，那么自增 ID 自然会呈现跳跃和不连续。例如，步长为 3，偏移量为 1，生成的 ID 序列会是 1, 4, 7, 10...。

可通过以下命令查看当前设置：

SHOW VARIABLES LIKE 'auto_inc%';