编程宝库 - 技术改变世界

编程宝库

Search

程序设计

Python中的Super详解

Vue 组件实战

MySQL8自增主键变化

一、简述

MySQL版本从5直接大跃进到8，相信MySQL8一定会有很多令人意想不到的改进，如果不想只会CRUD可以看看。

比如系统表引擎的变化-全部换成事务型的InnoDB。

MySQL5.7系统部引擎

MySQL8系统引擎

上图可以看到，MySQL5.7的系统表引擎有MEMORY、InnnoDB和MyISAM三种，但MySQL8的系统表引擎都换成了InnoDB。MySQL8新特性还有很多，接下来进入正题康康它的自增主键。

二、MySQL自增主键

为什么MySQL8新特性会修改自增主键属性？

在MySQL8.0之前，自增主键 AUTO_INCREMENT 的值如果大于max(primary key) +1，那么在MySQL重启后，则会重置 AUTO_INCREMENT = max(primary key)+1 的值，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的一些问题。

MySQL官网解释自增ID冲突问题

因为在MySQL5.7中，对于自增主键的分配规则是由InnoDB数据字典内部一个计数器来决定的，而该计数器维护在了内存中，并不会持久化到磁盘中，此时硬盘中并无数据，当数据库重启的时候，该计数器会被初始化为: auto_increment = max(primary key)+1。

如何解决自增主键冲突问题？

这个问题一直到MySQL8.0才解决。 8.0版本将会对 AUTO_INCREMENT 值进行持久化，所以即使MySQL重启后该值也不会改变。即其将自增主键的计数器持久化到了重做日志中，每次计数器发生改变都会将其写入到重做日志中，如果这个时候数据库重启了，那么InnoDB数据字典会根据重做日志中的信息来初始化计数器的内存值，就可以恢复到了上次关闭数据库前的状态，通过自增ID持久化来避免8.0之前可能会出现的问题。

三、自增主键测试

分别在MySQL5和MySQL8上进行自增主键测试。

1、MySQL5.7自增主键

在MySQL5.7中的，这里我们先创建一个数据表，这个数据表中设置一个自增列。

CREATE TABLE t_test_auto_increment_tjt(
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `test_key` varchar(50) NOT NULL COMMENT '名称',
  `test_value` varchar(50) DEFAULT NULL COMMENT '值',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='测试主键自增表';

然后向自增主键表中添加了4条记录，表中的四条添加的记录的id字段值就分别为: 1、2、3、4。

INSERT INTO t_test_auto_increment_tjt(id, test_key, test_value) VALUES
('0','吞噬星空','停更'),
('0','水斗大陆','可以停播了'),
('0','武神主宰','装B还得看尘少'),
('0','完美世界','yyds')

插入数据的SQL添加的是0，其实就是默认赋值，表 t_test_auto_increment_tjt 中的自增列是不可以添加0或者null的，那么这个时候表中的四条添加的记录的id字段值就分别为: 11、2、3、4。

接下来，将表中的id为4的字段删除。

DELETE FROM t_test_auto_increment_tjt WHERE id = 4

然后，继续在表中添加一条记录，执行之后我们可以发现，此时自增主键的ID结果是5。

INSERT INTO t_test_auto_increment_tjt(id, test_key, test_value) VALUES ('0','完美世界','yyds-YYDS')

因为我们前面已经将表中id为4的记录删除了，这个时候下一次自增的时候即使表中没有id为4的字段了，但是这个时候我们也不会添加4，而是添加5。其实这个时候就是自增主键的值auto_increment 大于了max(primary key)+1。再接下来，将表中的id为5的记录删除。

DELETE FROM t_test_auto_increment_tjt WHERE id = 5

最后，重启MySQL数据库，再向表中添加一条记录。

INSERT INTO t_test_auto_increment_tjt(id, test_key, test_value) VALUES ('0','完美世界','yyds-YYDS-restart')

上图可以看到，重启后重启后重启后执行的结果中添加的记录的id值为 : 4，按之前的操作来看4和5已经被删除了，那么添加的就应该是6，为什么是4呢？

因为在MySQL5.7中，自增主键的分配规则是由InnoDB数据字典内部一个计数器来决定的，而该计数器维护在了内存中，并不会持久化到磁盘中，此时硬盘中并无数据，当数据库重启之后该计数器会被初始化为: auto_increment = max(primary key)+1，所以记录的id=4，而不是6。