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

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

问题复现（MySQL5.7.19)：

1. 创建两张表tb1,tb2，tb1为innodb表，tb2为myisam表，建表语句：
   1. create table tb1(id int auto_increment, primary key(id)) engine=innodb;
   2. create table tb2(id int auto_increment, primary key(id)) engine=myisam;
2. 分别往tb1,tb2中写5条记录，SQL：
   1. insert into tb1 values(NULL); 重复执行5次
   2. insert into tb2 values(NULL); 重复执行5次
3. 通过show create table，查看tb1,tb2的auto_increment都为6。
4. 删除tb1,tb2中的所有数据
   1. delete from tb1;
   2. delete from tb2;
5. 通过show create table，查看tb1,tb2的auto_increment仍然都为6。
6. 重启MySQL
7. 通过show create table，查看tb1,tb2的auto_increment。
   1. tb1 innodb表的auto_increment值为1。
   2. 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重启后，该值不再丢失。

总结：

1. MySQL自增变量auto_increment值重启后丢失，只适用于innodb表，myisam表并不会丢失。
2. MySQL 8.0 修复了auto_increment值重启后丢失的问题。
3. 我相信大部分公司在设计数据表时都有cut_off字段吧，不去删除记录自然不会丢失auto_increment

为避免投诉：转载自https://mbd.baidu.com/newspage/data/landingshare?pageType=1&isBdboxFrom=1&context=%7B%22nid%22%3A%22news_9758538151803614984%22%2C%22sourceFrom%22%3A%22bjh%22%7D

注意：本文归作者所有，未经作者允许，不得转载