MySQL中的主鍵自增機制詳情

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

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

主鍵自增

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

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

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

-- 設置主鍵自增
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 數據庫會拋出異常. 對于 MyISAM 無此要求。
自增長每次遞增 1, 說明是數值型, 可以是整數, 也可以是浮點數.
由于種種原因, 自增值可以保證增長趨勢, 但并不能保證連續(xù).
不會影響自增長的命令：DELETE FROM xxx;
可以影響自增長的命令：TRUNCATE TABLE xxx;

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

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

自增主鍵保存在哪里

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

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

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

但是這樣就會存在一個問題，比如 : 如果一個表的 id 最大是 10, 此時的 AUTO_INCREMENT=11. 當刪除 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 恢復重啟之前的值. 所以不會出現(xiàn)上述問題.

自增值修改機制

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

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

假設, 要插入的值是 X, 當前的自增值是 Y.

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

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