结论
- MyISAM 不会出现删除掉的 AUTO_INCREMENT 字段值被复用的问题,因为这个最大值会被写磁盘,每次初始化会从磁盘读取
- InnoDB 会出现删除掉的 AUTO_INCREMENT 字段值被复用的问题,因为这个最大值不会写磁盘,每次都是用类似
SELECT MAX(ai_col) FROM table_name FOR UPDATE;
的方式初始化。
参考
InnoDB AUTO_INCREMENT Counter Initialization
In MySQL 5.7 and earlier, the auto-increment counter is stored only in main memory, not on disk. To initialize an auto-increment counter after a server restart, InnoDB would execute the equivalent of the following statement on the first insert into a table containing an AUTO_INCREMENT column.
SELECT MAX(ai_col) FROM table_name FOR UPDATE;
In MySQL 8.0, this behavior is changed.
需要注意的是,5.7 及之前版本才会出现该问题。