MySQL自增变量auto_increment踩坑，重启后值丢失
偏执的IT工匠2019-06-26 10:22
MySQL在建表时，推荐使用自增列作为表的主键，这样的表在某些场景下，对提高性能有一定的帮助。但是自增列有一个坑，并且这个坑存在了很久，一直到MySQL 8.0版本，才修复了这个坑，这个坑就是表的自增列变量auto_increment在MySQL重启后，有可能丢失。
auto_increment踩坑问题复现（MySQL5.7.19)：
创建两张表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;分别往tb1,tb2中写5条记录，SQL：insert into tb1 values(NULL); 重复执行5次insert into tb2 values(NULL); 重复执行5次通过show create table，查看tb1,tb2的auto_increment都为6。删除tb1,tb2中的所有数据delete from tb1;delete from tb2;通过show create table，查看tb1,tb2的auto_increment仍然都为6。重启MySQL通过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值重启后丢失的问题。
举报/反馈
0
0
收藏
分享
