MySQL8自增主鍵變化圖文詳解

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

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

一、簡述

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

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

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

MySQL8系統(tǒng)引擎

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

二、MySQL自增主鍵

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

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

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

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

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

這個問題一直到MySQL8.0才解決。

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

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

三、自增主鍵測試

分別在MySQL5和MySQL8上進行自增主鍵測試。

1、MySQL5.7自增主鍵

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

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，其實就是默認賦值，表 t_test_auto_increment_tjt 中的自增列是不可以添加0或者null的，那么這個時候表中的四條添加的記錄的id字段值就分別為: 11、2、3、4。

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

DELETE FROM t_test_auto_increment_tjt WHERE id = 4

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

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

因為我們前面已經(jīng)將表中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數(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)被刪除了，那么添加的就應該是6，為什么是4呢？

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