MySQL8自增主鍵變化圖文詳解

2022-04-18 10:00:45

一、簡述

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。