主鍵自增

MySQL 提供了主鍵自增機(jī)制 AUTO_INCREMENT. 對(duì)主鍵使用, 保證了主鍵的唯一性.

注意：自增長(zhǎng)必須與主鍵字段配合使用。

默認(rèn)的主鍵的起始值為 1, 每次增量為 1, 也可以手動(dòng)指定其自增起始值 auto_increment_offset 和自增步長(zhǎng) auto_increment_increment.

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

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

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

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

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

注意：

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

使用起來(lái)倒是很簡(jiǎn)單，但是對(duì)于主鍵自增機(jī)制的這些問(wèn)題，你了解嗎？

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

自增主鍵保存在哪里

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

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

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

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

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

自增值修改機(jī)制

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

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

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

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

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

自增值的修改時(shí)機(jī)

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

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

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

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

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

這個(gè)語(yǔ)句真正執(zhí)行的時(shí)候, 因?yàn)榕龅轿ㄒ绘I C 沖突, 所以 id=2 這一行并沒(méi)有插入成功, 但也沒(méi)有將自增值再改回去. 所以, 在這之后, 再插入新的數(shù)據(jù)行時(shí), 拿到的自增 id 就是 3. 也就是說(shuō), 出現(xiàn)了自增主鍵不連續(xù)的情況.

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

• 唯一約束沖突導(dǎo)致自增主鍵不連續(xù).
• 事務(wù)回滾也會(huì)導(dǎo)致自增主鍵不連續(xù).

如何修改自增主鍵值

有如下幾種修改方式：

• 使用alter table 表名 AUTO_INCREMENT = ?來(lái)修改自增值的起始值。
• 在創(chuàng)建表時(shí)設(shè)置 AUTO_INCREMENT=? 自增值的起始值。

到此這篇關(guān)于MySQL中的主鍵自增機(jī)制詳情的文章就介紹到這了,更多相關(guān)MySQL主鍵自增內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論