导致 MySQL 自增主键 ID 不连续的原因

在新建数据表时，我们一般会考虑为每一张表添加一个自增主键，但时间久了去看记录会发现 ID 并不连续，人为删除记录不在我们的讨论范围，今天来对我遇到导致 MySQL 自增主键 ID 不连续的原因做下汇总。

原因 1：唯一键冲突

还原步骤：

-- S1 创建测试表
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_a` (`a`)
) ENGINE=InnoDB;

-- S2 插入记录值：(1, 1)
INSERT INTO t1 (a, b) VALUE (1, 1);

-- S3 再次插入记录值：(1, 1)
INSERT INTO t1 (a, b) VALUE (1, 1);

-- S4 插入记录值：(2, 2)
INSERT INTO t1 (a, b) VALUE (2, 2);

-- S5 查询所有记录
SELECT * FROM t1;

执行 S3 会提示 Duplicate entry ‘1’ for key ‘uk_a’，是因为已经存在 (1, 1) 记录，S5 操作查询所有记录，结果如下图，会发现没有 id = 2 这一行，记录 (2, 2) 的 id 为3。

原因 2：事务回滚

还原步骤：

-- S1 创建测试表
CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_a` (`a`)
) ENGINE=InnoDB;

-- S2 插入记录值：(1, 1)
INSERT INTO t2 (a, b) VALUE (1, 1);

-- S3 插入记录值：(2, 2)，手动回退
BEGIN;
INSERT INTO t2 (a, b) VALUE (2, 2);
ROLLBACK;

-- S4 再次插入记录值：(2, 2)
INSERT INTO t2 (a, b) VALUE (2, 2);

-- S5 查询所有记录
SELECT * FROM t2;

S3 操作手动将事务回退，S4 操作再次插入记录 (2, 2)，S5 操作查询所有记录，结果如下图，会发现没有 id = 2 这一行，记录 (2, 2) 的 id 为3。

原因 3：批量插入数据

还原步骤：

-- S1 创建测试表
CREATE TABLE `t3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_a` (`a`)
) ENGINE=InnoDB;

-- S2 插入记录值：(1, 1)
INSERT INTO t3 (a, b) VALUE (1, 1);

-- S3 插入记录值：(2, 2)
INSERT INTO t3 (a, b) VALUE (2, 2);

-- S4 插入记录值：(3, 3)
INSERT INTO t3 (a, b) VALUE (3, 3);

-- S5 插入记录值：(4, 4)
INSERT INTO t3 (a, b) VALUE (4, 4);

-- S6 创建测试表
CREATE TABLE t4 LIKE t3;

-- S7 向 t4 表中插入 t3 表中的记录
INSERT INTO t4 (a, b) SELECT a, b FROM t3;

-- S8 插入记录值：(5, 5)
INSERT INTO t4 (a, b) VALUE (5, 5);

-- S9 查询所有记录
SELECT * FROM t4;