服务器之家
您的位置： 首页 数据库 mysql

记一次项目中 mysql auto_increment 回退导致的问题

100人浏览   2025-03-30 00:20:34


在测试环境发生一个比较奇怪的问题，最终定位原因是 mysql 服务重启后 auto_increment 回退引起的。在此记录一下。


1、问题

具体问题如下：

1、有两张表，一张主表 t_A，一张记录表 t_B；

2、记录表 t_B 是主表 t_A 的每次插入数据的历史记录；

3、有问题的方法是加事务的，方法中每次操作是：

①、先查主表 t_A；

②、复制主表数据插入到记录表 t_B，包括主键ID也是主表的；

③、物理删除主表 t_A 数据；

④、插入前端传入的新数据到主表 t_A 中；

4、问题发生在第 ④ 步，插入新数据后主表 t_A 主键ID变小了，即发生了 auto_increment 回退了；

5、当再执行该方法时，复制主表 t_A 的数据插入记录表 t_B 时发现主表 t_A 中的主键ID 在记录表 t_B 中已经存在了，从而导致记录表 t_B 主键冲突；


2、原因

问题排查时不知道为什么主表 t_A 中的主键ID比记录表 t_B 中的主键ID 小，和同事讨论才知道在某种情况下 mysql auto_increment 自增值会回退变小，和 DBA 沟通才知道前一天测试环境的 mysql 服务集群确实重启过。

mysql 服务重启后 auto_increment 最大值被清理掉了，因为 auto_increment 最大值是保存在内存中的，服务重启后内存中的数据就被清理掉了，当再往表里插入数据时 mysql 会查表中最大的主键ID，在这基础上再来自增；

所以第一次给的定论是，就是因为mysql 服务重启了导致 auto_increment 回退变小了，导致往主表插入数据时自增ID变小了。

但是经查日志发现，mysql 服务重启后的隔天第一次执行有问题的方法时是成功的，第 ① 步查出的主表 t_A 数据中主键ID是比记录表 t_B 要大的，但是第 ④ 步往主表 t_A 插入新数据为啥自增ID变小了。

也就是说问题变成了：mysql 服务重启后主表 t_A 存在正常数据（比如主键 ID 是最大的 100），但是在第 ③ 步物理删除和第 ④ 步插入新数据后 auto_increment 自增ID咋还变小了（比如新数据的主键ID是 95 ）？

对于新问题的解释原因是，mysql 服务重启后内存中 auto_increment 还是没有的，在执行第 ④ 步插入数据时 mysql 服务会查表中的最大主键ID 以此来继续自增，并维护新的 auto_increment 到内存中。

对于这样的解释本人还是有疑问，第 ③ 步执行物理删除数据 mysql 服务不会去维护 auto_increment 吗，难道必须是在第 ④ 步重新插入数据才去维护 auto_increment ？

因此做如下实验来研究确认一下。


3、auto_increment 回退再自增研究

Mysql 版本是 5.7，据说 mysql 8之后就没这个问题了


3.1、建一张测试表

CREATE TABLE `auto_increment_test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


3.2、插入基础数据

INSERT INTO auto_increment_test (`name`)
VALUES ('A'),('B'),('C');

SELECT * FROM auto_increment_test ;


实验一、mysql 服务不重启，auto_increment 情况

结论是：auto_increment 不会回退，即使删除了数据，还是继续自增下去，实验步骤如下。

1）插入更多数据

2）删除部分数据

3）再插入新数据


实验二、mysql 服务重启后，直接插入新数据，auto_increment 情况

结论是：auto_increment 会回退，实验步骤如下。

1）在实验一的基础上删除部分数据

2）重启 mysql 服务

3）什么也不操作，直接插入新数据


实验三、mysql 服务重启后，先删除再插入新数据，auto_increment 情况

结论是：auto_increment 没有回退，实验步骤如下。

1）在实验二的基础上删除部分数据，之后再插入部分新数据

2）重启 mysql 服务

3）先删除再插入新数据


实验四、mysql 服务重启后，先查询一次再插入新数据，auto_increment 情况

结论是：auto_increment 回退了，实验步骤如下。

1）在实验三的基础上删除部分数据

2）重启 mysql 服务

3）先查询一次再插入新数据


实验五、mysql 服务重启后，show create table，auto_increment 情况

结论是：auto_increment 会回退，实验步骤如下。

1）在实验四的基础上删除部分数据

2）重启 mysql 服务

3）show create table

相关推荐