MySQL8自增主鍵變化圖文詳解

更新時(shí)間：2022年04月18日 08:16:14 作者：濤姐濤哥

眾所周知MySQL 的主鍵可以是自增的,下面這篇文章主要給大家介紹了關(guān)于MySQL8自增主鍵變化的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),需要的朋友可以參考下

一、簡述

MySQL版本從5直接大躍進(jìn)到8，相信MySQL8一定會(huì)有很多令人意想不到的改進(jìn)，如果不想只會(huì)CRUD可以看看。

比如系統(tǒng)表引擎的變化-全部換成事務(wù)型的InnoDB。

MySQL5.7系統(tǒng)部引擎

MySQL8系統(tǒng)引擎

上圖可以看到，MySQL5.7的系統(tǒng)表引擎有MEMORY、InnnoDB和MyISAM三種，但MySQL8的系統(tǒng)表引擎都換成了InnoDB。MySQL8新特性還有很多，接下來進(jìn)入正題康康它的自增主鍵。

二、MySQL自增主鍵

為什么MySQL8新特性會(huì)修改自增主鍵屬性？

在MySQL8.0之前，自增主鍵 AUTO_INCREMENT 的值如果大于max(primary key) +1，那么在MySQL重啟后，則會(huì)重置 AUTO_INCREMENT = max(primary key)+1 的值，這種現(xiàn)象在某些情況下會(huì)導(dǎo)致業(yè)務(wù)主鍵沖突或者其他難以發(fā)現(xiàn)的一些問題。

MySQL官網(wǎng)解釋自增ID沖突問題

因?yàn)樵贛ySQL5.7中，對(duì)于自增主鍵的分配規(guī)則是由InnoDB數(shù)據(jù)字典內(nèi)部一個(gè)計(jì)數(shù)器來決定的，而該計(jì)數(shù)器維護(hù)在了內(nèi)存中，并不會(huì)持久化到磁盤中，此時(shí)硬盤中并無數(shù)據(jù)，當(dāng)數(shù)據(jù)庫重啟的時(shí)候，該計(jì)數(shù)器會(huì)被初始化為: auto_increment = max(primary key)+1。

如何解決自增主鍵沖突問題？

這個(gè)問題一直到MySQL8.0才解決。

8.0版本將會(huì)對(duì) AUTO_INCREMENT 值進(jìn)行持久化，所以即使MySQL重啟后該值也不會(huì)改變。

即其將自增主鍵的計(jì)數(shù)器持久化到了重做日志中，每次計(jì)數(shù)器發(fā)生改變都會(huì)將其寫入到重做日志中，如果這個(gè)時(shí)候數(shù)據(jù)庫重啟了，那么InnoDB數(shù)據(jù)字典會(huì)根據(jù)重做日志中的信息來初始化計(jì)數(shù)器的內(nèi)存值，就可以恢復(fù)到了上次關(guān)閉數(shù)據(jù)庫前的狀態(tài)，通過自增ID持久化來避免8.0之前可能會(huì)出現(xiàn)的問題。

三、自增主鍵測試

分別在MySQL5和MySQL8上進(jìn)行自增主鍵測試。

1、MySQL5.7自增主鍵

在MySQL5.7中的，這里我們先創(chuàng)建一個(gè)數(shù)據(jù)表，這個(gè)數(shù)據(jù)表中設(shè)置一個(gè)自增列。

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')

插入數(shù)據(jù)的SQL添加的是0，其實(shí)就是默認(rèn)賦值，表 t_test_auto_increment_tjt 中的自增列是不可以添加0或者null的，那么這個(gè)時(shí)候表中的四條添加的記錄的id字段值就分別為: 11、2、3、4。

接下來，將表中的id為4的字段刪除。

DELETE FROM t_test_auto_increment_tjt WHERE id = 4

然后，繼續(xù)在表中添加一條記錄，執(zhí)行之后我們可以發(fā)現(xiàn)，此時(shí)自增主鍵的ID結(jié)果是5。

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

因?yàn)槲覀兦懊嬉呀?jīng)將表中id為4的記錄刪除了，這個(gè)時(shí)候下一次自增的時(shí)候即使表中沒有id為4的字段了，但是這個(gè)時(shí)候我們也不會(huì)添加4，而是添加5。其實(shí)這個(gè)時(shí)候就是自增主鍵的值auto_increment 大于了max(primary key)+1。

再接下來，將表中的id為5的記錄刪除。

DELETE FROM t_test_auto_increment_tjt WHERE id = 5

最后，重啟MySQL數(shù)據(jù)庫，再向表中添加一條記錄。

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

上圖可以看到，重啟后重啟后重啟后執(zhí)行的結(jié)果中添加的記錄的id值為 : 4，按之前的操作來看4和5已經(jīng)被刪除了，那么添加的就應(yīng)該是6，為什么是4呢？

因?yàn)樵贛ySQL5.7中，自增主鍵的分配規(guī)則是由InnoDB數(shù)據(jù)字典內(nèi)部一個(gè)計(jì)數(shù)器來決定的，而該計(jì)數(shù)器維護(hù)在了內(nèi)存中，并不會(huì)持久化到磁盤中，此時(shí)硬盤中并無數(shù)據(jù)，當(dāng)數(shù)據(jù)庫重啟之后該計(jì)數(shù)器會(huì)被初始化為: auto_increment = max(primary key)+1，所以記錄的id=4，而不是6。