我们在建表的时候,通常会加一个id的主键,并将它设置为AUTO_INCREMENT,很明显这个id是自增的,那么自增的id是不是就是连续的呢?先说结论,自增主键不能保证连续递增;那么什么什么情况下自增主键会出现 “空洞”,不连续呢?
为了便于说明,我们创建一个表 t,其中 id 是自增主键字段、c 是唯一索引。
CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;
自增值保存在哪儿?
在这个空表 t 里面执行 insert into t values(null, 1, 1); 插入一行数据,再执行 show create table 命令,就可以看到如下图所示的结果:
可以看到,表定义里面出现了一个 AUTO_INCREMENT=2,表示下一次插入数据时,如果需要自动生成自增值,会生成 id=2。
其实,这个输出结果容易引起这样的误解:自增值是保存在表结构定义里的。实际上,表的结构定义存放在后缀名为.frm 的文件中,但是并不会保存自增值。
不同的引擎对于自增值的保存策略不同:
MyISAM 引擎的自增值保存在数据文件中;
InnoDB 引擎的自增值,其实是保存在了内存里,并且到了 MySQL 8.0 版本后,才有了“自增值持久化”的能力,也就是才实现了“如果发生重启,表的自增值可以恢复为 MySQL 重启前的值”
具体情况是:
一.在 MySQL 5.7 及之前的版本,自增值保存在内存里,并没有持久化。每次重启后,第一次打开表的时候,都会去找自增值的最大值 max(id),然后将 max(id)+1 作为这个表当前的自增值。
举例来说,如果一个表当前数据行里最大的 id 是 10,AUTO_INCREMENT=11。这时候,我们删除 id=10 的行,AUTO_INCREMENT 还是 11。但如果马上重启实例,重启后这个表的 AUTO_INCREMENT 就会变成 10。也就是说,MySQL 重启可能会修改一个表的 AUTO_INCREMENT 的值
二.在 MySQL 8.0 版本,将自增值的变更记录在了 redo log 中,重启的时候依靠 redo log 恢复重启之前的值。
理解了 MySQL 对自增值的保存策略以后,我们再看看自增值修改机制。
自增值修改机制在 MySQL 里面,如果字段 id 被定义为