MySQL中的主鍵自增機制詳情

更新時間：2022年08月26日 09:18:46 作者：昨天的風(fēng)

這篇文章主要介紹了MySQL中的主鍵自增機制詳情，MySQL提供了主鍵自增機制AUTO_INCREMENT.?對主鍵使用,?保證了主鍵的唯一性，下文詳細介紹需要的小伙伴可以參考一下

主鍵自增

MySQL 提供了主鍵自增機制 AUTO_INCREMENT. 對主鍵使用, 保證了主鍵的唯一性.

注意：自增長必須與主鍵字段配合使用。

默認的主鍵的起始值為 1, 每次增量為 1, 也可以手動指定其自增起始值 auto_increment_offset 和自增步長 auto_increment_increment.

-- 設(shè)置主鍵自增
CREATE TABLE USER(
    id INT UNSIGNED AUTO_INCREMENT,
    ...
)

-- 在創(chuàng)建表時指定自增起始值
CREATE TABLE xx{

}ENGINE=INNODB auto_increment=100 DEFAULT CHARSET=utf8;

-- 修改自增起始值
alter table people  AUTO_INCREMENT = 20;

-- 基于會話級別：
show session variables like 'auto_inc%';　　-- 查看步長
set session auto_increment_increment=2;   -- 修改自增步長
　　
-- 基于全局級別：（會影響下一次登錄的值）
show global variables like 'auto_inc%';　	-- 查看步長
set global auto_increment_increment=5;	-- 修改自增步長

注意：

在 InnoDB 存儲引擎中, 自增長值的列必須是索引, 同時必須是索引的第 1 個列. 如果不是第 1 個列, 則 MySQL 數(shù)據(jù)庫會拋出異常. 對于 MyISAM 無此要求。
自增長每次遞增 1, 說明是數(shù)值型, 可以是整數(shù), 也可以是浮點數(shù).
由于種種原因, 自增值可以保證增長趨勢, 但并不能保證連續(xù).
不會影響自增長的命令：DELETE FROM xxx;
可以影響自增長的命令：TRUNCATE TABLE xxx;

使用起來倒是很簡單，但是對于主鍵自增機制的這些問題，你了解嗎？

自增主鍵保存在哪里？
自增主鍵如何實現(xiàn)自增的？
自增主鍵是什么時候自增的？
自增主鍵一定是連續(xù)自增的嗎？
自增主鍵可以人為修改嗎？

自增主鍵保存在哪里

首先需要知道的是，自增主鍵機制是存儲引擎實現(xiàn)的，所以不同的存儲引擎對于自增值的保存策略不同.

MyISAM 的自增值保存在數(shù)據(jù)文件中.
InnoDB 的自增值, 保存在內(nèi)存里, 一直到了 MySQL 8.0 后, 才有了自增值的持久化的能力, 也就是才保存到文件中，實現(xiàn)了如果發(fā)生重啟, 表的自增值可以恢復(fù)為 MySQL 重啟前的值.

具體是：在 MySQL <= 5.7 時, 自增值保存在內(nèi)存里, 沒有持久化. 當(dāng) MySQL 重啟后, 第一次打開某個數(shù)據(jù)表的時候, 都會去找該表中主鍵字段的自增值的最大值 max(id), 然后將 max(id)+1 作為這個表當(dāng)前的自增值.

但是這樣就會存在一個問題，比如 : 如果一個表的 id 最大是 10, 此時的 AUTO_INCREMENT=11. 當(dāng)刪除 id=10 的記錄時, 此時 AUTO_INCREMENT 還是 11. 但如果馬上重啟 MySQL, 重啟后這個表的 AUTO_INCREMENT 就變?yōu)?10 了. ( maxid = 9, 9+1=10 ) 即 MySQL 重啟可能會修改表的 AUTO_INCREMENT 的值.

在 MySQL 8.0 版本, 將自增值的變更記錄在了 redo log 中, 重啟的時候依靠 redo log 恢復(fù)重啟之前的值. 所以不會出現(xiàn)上述問題.

自增值修改機制

在 MySQL 中, 如果字段 id 被定義為 AUTO_INCREMENT, 在插入一行數(shù)據(jù)的時候, 自增值的操作如下：

如果插入數(shù)據(jù)時, id 字段指定為 0, null 或未指定值, 那么就把這個表當(dāng)前的 AUTO_INCREMENT 值給自增字段, ( 沒給值, 則使用自增長值 )
如果插入數(shù)據(jù)時, id 字段指定了具體的值, 就直接使用 SQL 語句里指定的值. ( 給定了值, 就使用給定值 )
根據(jù)要插入的值和當(dāng)前自增值的大小關(guān)系, 自增值的變更結(jié)果也會有所不同.

假設(shè), 要插入的值是 X, 當(dāng)前的自增值是 Y.

如果 X < Y, 那么這個表的自增值不變.

如果 X ≥ Y, 就需要把當(dāng)前表的自增值修改為新的自增值. 新的自增值生成方式是：從 auto_increment_offset 開始, 以 auto_increment_increment 為步長, 持續(xù)疊加, 直到找到第一個大于 X 的值, 作為新的自增值. 也就是，這種情況下步長也參與了影響。

自增值的修改時機

假設(shè), 有一個表 t , 有 A, B, C 三個字段, 字段 A 是主鍵, 且自增, 字段 C 有唯一約束.

當(dāng)前表 t 中有一條記錄為 (1,1,1) , 此時執(zhí)行一條插入語句 insert into t values(null, 1, 1);

那么這個語句的執(zhí)行流程就是：

執(zhí)行器調(diào)用 InnoDB 引擎的接口，寫入一行, 傳入的這一行的值是 (null,1,1);
InnoDB 發(fā)現(xiàn)用戶沒有指定自增 id 的值, 所以會獲取表 t 當(dāng)前的自增值 2,
然后 InnoDB 會將傳入的行的值改成 (2,1,1);
然后將表的自增值改成 3,
然后執(zhí)行插入數(shù)據(jù)操作, 由于 C 字段已經(jīng)存在 = 1 的記錄, 所以報 Duplicate key error, 并返回.

最后的結(jié)果可以看到, 這個表的自增值改成 3, 是在真正執(zhí)行插入數(shù)據(jù)的操作之前進行的.

這個語句真正執(zhí)行的時候, 因為碰到唯一鍵 C 沖突, 所以 id=2 這一行并沒有插入成功, 但也沒有將自增值再改回去. 所以, 在這之后, 再插入新的數(shù)據(jù)行時, 拿到的自增 id 就是 3. 也就是說, 出現(xiàn)了自增主鍵不連續(xù)的情況.

總結(jié)：自增主鍵不連續(xù)有如下兩種情況 :