MySQL自增变量auto_increment踩坑，重启后值丢失

转载：https://baijiahao.baidu.com/s?id=1637367920353569708&wfr=spider&for=pc

MySQL在建表时，推荐使用自增列作为表的主键，这样的表在某些场景下，对提高性能有一定的帮助。但是自增列有一个坑，并且这个坑存在了很久，一直到MySQL 8.0版本，才修复了这个坑，这个坑就是表的自增列变量auto_increment在MySQL重启后，有可能丢失。

问题复现（MySQL5.7.19)：

1、创建两张表tb1,tb2，tb1为innodb表，tb2为myisam表，建表语句：

create table tb1(id int auto_increment, primary key(id)) engine=innodb;

create table tb2(id int auto_increment, primary key(id)) engine=myisam;

2、分别往tb1,tb2中写5条记录，SQL：

insert into tb1 values(NULL); 重复执行5次

insert into tb2 values(NULL); 重复执行5次

3、通过show create table，查看tb1,tb2的auto_increment都为6。

4、删除tb1,tb2中的所有数据    delete from tb1;      delete from tb2;

5、通过show create table，查看tb1，tb2的auto_increment仍然都为6。

6、重启MySQL

7、通过show create table，查看tb1,tb2的auto_increment。

tb1 innodb表的auto_increment值为1。

tb2 myisam表的auto_increment值仍然为6。

原因分析：

MySQL innodb表的自增变量的值是内存中的临时值，在MySQL重启后就会丢失，MySQL重启时该值以当前表中自增字段的最大值确定下次自增值，比如上表tb1没有数据，重启后auto_increment值默认为1，从1开始自增。如果表tb1有数据，比如id的最大值为4，重启后，auto_increment值将变为5，也就是max(field_value)+1。

MySQL的这个问题，无形中给业务埋下了坑，某些业务依赖自增ID来生成唯一值，当删除一些记录并重启MySQL后，新生成的ID可能与之前的ID有重复，导致ID冲突，因此任何依赖于auto_increment 值的业务逻辑，都需要慎重，要充分了解MySQL auto_increment的实现方式，避免踩坑。

auto_increment值丢失的问题从MySQL 4.0、5.1、5.5、5.6、5.7版本一直存在，终于在MySQL 8.0 解决了，MySQL 8.0版本对auto_increment值进行持久化，MySQL重启后，该值不再丢失。

总结：

MySQL自增变量auto_increment值重启后丢失，只适用于innodb表，myisam表并不会丢失。

MySQL 8.0 修复了auto_increment值重启后丢失的问题。