MySQL - InnoDB自增列重复值问题 (Mysql 5.7以下)

问题重现

先从问题入手，重现下这个 bug

MySQL> use test;
MySQL> drop table if exists t1;
MySQL> create table t1(id int auto_increment, a int, primary key (id)) engine=innodb;
MySQL> insert into t1 values (1,2);
MySQL> insert into t1 values (null,2);
MySQL> insert into t1 values (null,2);
MySQL> select * from t1;

+----+------+
| id | a    |
+----+------+
| 1 | 2     |
| 2 | 2     |
| 3 | 2     |
+----+------+

MySQL> delete from t1 where id=2;
MySQL> delete from t1 where id=3;
MySQL> select * from t1;

+----+------+
| id | a    |
+----+------+
| 1 | 2     |
+----+------+

这里我们关闭MySQL，再启动MySQL,然后再插入一条数据

MySQL> insert into t1 values (null,2);
MySQL> select * FROM T1;

+----+------+
| id | a     |
+----+------+
| 1 | 2     |
+----+------+
| 2 | 2     |
+----+------+

我们看到插入了（2,2），而如果我没有重启，插入同样数据我们得到的应该是（4,2)。 上面的测试反映了MySQLd重启后，InnoDB存储引擎的表自增id可能出现重复利用的情况。

自增id重复利用在某些场景下会出现问题。依然用上面的例子，假设t1有个历史表t1_history用来存t1表的历史数据，那么MySQLd重启前,ti_history中可能已经有了（2,2）这条数据，而重启后我们又插入了（2，2），当新插入的(2,2)迁移到历史表时，会违反主键约束。

原因分析

InnoDB 自增列出现重复值的原因：

MySQL> show create table t1\G;

*************************** 1. row ***************************

Table: t1

Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=innodb AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

建表时可以指定 AUTO_INCREMENT值，不指定时默认为1，这个值表示当前自增列的起始值大小，如果新插入的数据没有指定自增列的值，那么自增列的值即为这个起始值。对于InnoDB表，这个值没有持久到文件中。而是存在内存中（dict_table_struct.autoinc）。那么又问，既然这个值没有持久下来，为什么我们每次插入新的值后， show create table t1看到AUTO_INCREMENT值是跟随变化的。其实show create table t1是直接从dict_table_struct.autoinc取得的（ha_innobase::update_create_info）。

知道了AUTO_INCREMENT是实时存储内存中的。那么，MySQLd 重启后，从哪里得到AUTO_INCREMENT呢? 内存值肯定是丢失了。实际上MySQL采用执行类似select max(id)+1 from t1;方法来得到AUTO_INCREMENT。而这种方法就是造成自增id重复的原因。

MyISAM自增值

MyISAM也有这个问题吗？MyISAM是没有这个问题的。myisam会将这个值实时存储在.MYI文件中（mi_state_info_write）。MySQLd重起后会从.MYI中读取AUTO_INCREMENT值（mi_state_info_read)。因此，MyISAM表重启是不会出现自增id重复的问题。

诶 网上很多解决方案 都没说mysql版本问题.

innodb_autoinc_persistent=on
innodb_autoinc_persistent_interval=1

通过mysql 设置这个配置 就可以解决自增键持久化问题. 但前提是 mysql 8.0.13后在支持该参数. 而且8.0.13后默认就是持久化的.....