MySQL 自增 ID 跳格问题

在使用 MySQL 数据库时，大家都会使用自增 ID 进行数据的标识和关联。然而，有时候我们会发现自增 ID 会跳格，即已经存在的 ID 号码未使用，新插入数据的 ID 号码不是连续的，这个问题是怎么发生的？

阅读更多：MySQL 教程

自增 ID 原理

MySQL 中的自增 ID 使用的是自增字段，是一种数据类型，在表中使用 INT(11) UNSIGNED 自动计数器，每次插入一条记录，自增字段的值都会自动加 1，从而保证表中的每条记录都具有唯一的自增 ID。

例如，我们创建一个表 users，并在其上添加了一个自增 ID 字段：

CREATE TABLE users (
    id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

在向表中添加数据时，我们只需要向普通的表一样插入数据，但是这个操作会自动记录并返回自增 ID：

INSERT INTO users (name) VALUES ('Jony'), ('Tony'), ('Pony');

执行完上述操作后，我们可以通过查询表的数据来查看自增 ID：

SELECT * FROM users;

执行结果如下：

+----+------+
| id | name |
+----+------+
|  1 | Jony |
|  2 | Tony |
|  3 | Pony |
+----+------+

我们可以看到，自增 ID 是逐步自增的，从 1 开始，没添加一条记录自增 1，直到当前的最大值。这就是自增 ID 的原理。

跳格问题

然而在实际开发中，我们很难保证 ID 号码一定是连续递增的，这就带来了一些问题。比如，在对数据进行删除操作后，表中的某些 ID 就可能没有被使用。在继续添加新数据时，新数据的自增 ID 可能就会跳过这些没有被使用的 ID，造成数据库性能和使用效率下降。

例如，我们删除上述表 users 中的一条记录：

DELETE FROM users WHERE id = 2;

执行结果如下：

+----+------+
| id | name |
+----+------+
|  1 | Jony |
|  3 | Pony |
+----+------+

我们可以看到，刚才删除的那条记录的 ID 是 2，此时表中只剩下 ID 为 1 和 3 的数据。在继续插入数据时，新数据的 ID 可能会从 4 开始，跳过了原来的 2。这会对日后表的处理和查询操作产生问题，特别是在大数据处理时，表现得更加明显。

解决方法

针对这个问题，我们有一些解决方案可以实施。

方案 1：重新排列 ID

我们可以将表中的记录全部导出到一个文件中，然后重新加载到表中来。这样做会让自增 ID 重新按照记录的顺序进行排列，而不存在跳格问题。

导出数据表 users：

SELECT * INTO OUTFILE '/tmp/users.txt' FROM users;

清空数据表 users：

DELETE FROM users;

载入数据表 users：

LOAD DATA INFILE '/tmp/users.txt' INTO TABLE users;

这样，数据表 users 中的记录就重新进行了排序，自增 ID 也重新排列了。

方案 2：手动添加 ID

在某些场景下，我们可以尝试手动添加 ID。例如，用户表是用作过度备份，在进行过度备份的时候，可以手动将数据插入到另一个表中，并制定主键 ID。这样就可以保证 ID 连续不跳格。

例如，我们创建一个备份表 users_backup，手动插入数据，并为其分配连续的 ID：

CREATE TABLE users_backup (
    id INT(11) UNSIGNED NOT NULL,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO users_backup (id, name) VALUES (1, 'Jony'), (2, 'Tony'), (3, 'Pony');

这样，即使原来的 users 表中某些 ID 被删除了，我们也可以通过备份表 users_backup 来恢复数据，并保证 ID 连续不跳格。