很多低级开发工程师都想当然觉得自增主键是严格连续递增的,但事实真的如此吗?
创建一个测试表,执行
show create table
SHOW CREATE TABLE tbl_name:显示创建指定命名表的 CREATE TABLE 语句。要使用此语句,必须对该表具有一定的权限。此语句也适用于视图。
更改表的存储引擎时,不适用于新存储引擎的表选项会保留在表定义,以便在必要时将具有先前定义选项的表恢复到原始存储引擎。例如,将存储引擎从 InnoDB 更改为 MyISAM 时,将保留 InnoDB 特定的选项,例如 ROW_FORMAT=COMPACT。
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) ROW_FORMAT=COMPACT ENGINE=InnoDB;
mysql> ALTER TABLE t1 ENGINE=MyISAM;
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` int(11) NOT NULL,
PRIMARY KEY (`c1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT
创建禁用严格模式的表时,若不支持指定的行格式,则使用存储引擎的默认行格式。表的实际行格式在 Row_format 列中报告,以响应
SHOW TABLE STATUS。 SHOW CREATE TABLE 显示在 CREATE TABLE 语句中指定的行格式。
AUTO_INCREMENT=2,表示下一次插入数据时,若需要自动生成自增值,会生成id=2。
这个输出结果容易引起误解:自增值是保存在表结构定义里的。实际上,表的结构定义存在.frm文件,但不会保存自增值。
自增值的保存策略
MyISAM
自增值保存在数据文件中。
InnoDB
自增值保存在内存,MySQL 8.0后,才有了“自增值持久化”能力,即才实现了“若重启,表的自增值可以恢复为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对自增值的保存策略以后,我们再看看自增值修改机制。
自增值的修改策略
若字段id被定义为AUTO_INCREMENT,在插入一行数据时,自增值的行为如下ÿ