MySQL8自增主键变化图文详解

目录
  • 一、简述
  • 二、mysql自增主键
    • 为什么MySQL8新特性会修改自增主键属性?
    • PvhbOgFI何解决自增主键冲突问题?
  • 三、自增主键测试
    • 1、MySQL5.7自增主键
    • 2、MySQL8自增主键
  • 总结

    一、简述

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

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

    MySQL5.7系统部引擎

    MySQL8自增主键变化图文详解

    MySQL8系统引擎

    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冲突问题

    MySQL8自增主键变化图文详解

    因为在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。

    MySQL8自增主键变化图文详解

    接下来,将表中的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')

    MySQL8自增主键变化图文详解

    MySQL8自增主键变化图文详解

    扫一扫手机访问