這篇文章主要介紹“MySQL中的自增主鍵怎么修改”的相關(guān)知識(shí)，小編通過(guò)實(shí)際案例向大家展示操作過(guò)程，操作方法簡(jiǎn)單快捷，實(shí)用性強(qiáng)，希望這篇“MySQL中的自增主鍵怎么修改”文章能幫助大家解決問(wèn)題。

創(chuàng)新互聯(lián)是一家專注網(wǎng)站建設(shè)、網(wǎng)絡(luò)營(yíng)銷(xiāo)策劃、微信小程序開(kāi)發(fā)、電子商務(wù)建設(shè)、網(wǎng)絡(luò)推廣、移動(dòng)互聯(lián)開(kāi)發(fā)、研究、服務(wù)為一體的技術(shù)型公司。公司成立十年以來(lái)，已經(jīng)為成百上千成都不銹鋼雕塑各業(yè)的企業(yè)公司提供互聯(lián)網(wǎng)服務(wù)。現(xiàn)在，服務(wù)的成百上千客戶與我們一路同行，見(jiàn)證我們的成長(zhǎng)；未來(lái)，我們一起分享成功的喜悅。

一、自增值保存在哪兒？

不同的引擎對(duì)于自增值的保存策略不同

1.MyISAM引擎的自增值保存在數(shù)據(jù)文件中

2.InnoDB引擎的自增值，在MySQL5.7及之前的版本，自增值保存在內(nèi)存里，并沒(méi)有持久化。每次重啟后，第一次打開(kāi)表的時(shí)候，都會(huì)去找自增值的最大值max(id)，然后將max(id)+步長(zhǎng)作為這個(gè)表當(dāng)前的自增值

select max(ai_col) from table_name for update;

在MySQL8.0版本，將自增值的變更記錄在了redo log中，重啟的時(shí)候依靠redo log恢復(fù)重啟之前的值

二、自增值修改機(jī)制

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

1.如果插入數(shù)據(jù)時(shí)id字段指定為0、null或未指定值，那么就把這個(gè)表當(dāng)前的AUTO_INCREMENT值填到自增字段

2.如果插入數(shù)據(jù)時(shí)id字段指定了具體的值，就直接使用語(yǔ)句里指定的值

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

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

2.如果X>=Y，就需要把當(dāng)前自增值修改為新的自增值

新的自增值生成算法是：從auto_increment_offset（初始值）開(kāi)始，以auto_increment_increment（步長(zhǎng)）為步長(zhǎng)，持續(xù)疊加，直到找到第一個(gè)大于X的值，作為新的自增值

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

創(chuàng)建一個(gè)表t，其中id是自增主鍵字段、c是唯一索引，建表語(yǔ)句如下：

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

假設(shè)，表t里面已經(jīng)有了(1,1,1)這條記錄，這時(shí)再執(zhí)行一條插入數(shù)據(jù)命令：

insert into t values(null, 1, 1);

執(zhí)行流程如下：

1.執(zhí)行器調(diào)用InnoDB引擎接口寫(xiě)入一行，傳入的這一行的值是(0,1,1)

2.InnoDB發(fā)現(xiàn)用于沒(méi)有指定自增id的值，獲取表t當(dāng)前的自增值2

3.將傳入的行的值改成(2,1,1)

4.將表的自增值改成3

5.繼續(xù)執(zhí)行插入數(shù)據(jù)操作，由于已經(jīng)存在c=1的記錄，所以報(bào)Duplicate key error（唯一鍵沖突），語(yǔ)句返回

對(duì)應(yīng)的執(zhí)行流程圖如下：

在這之后，再插入新的數(shù)據(jù)行時(shí)，拿到的自增id就是3。出現(xiàn)了自增主鍵不連續(xù)的情況

唯一鍵沖突和事務(wù)回滾都會(huì)導(dǎo)致自增主鍵id不連續(xù)的情況

四、自增鎖的優(yōu)化

自增id鎖并不是一個(gè)事務(wù)鎖，而是每次申請(qǐng)完就馬上釋放，以便允許別的事務(wù)再申請(qǐng)

但在MySQL5.0版本的時(shí)候，自增鎖的范圍是語(yǔ)句級(jí)別。也就是說(shuō)，如果一個(gè)語(yǔ)句申請(qǐng)了一個(gè)表自增鎖，這個(gè)鎖會(huì)等語(yǔ)句執(zhí)行結(jié)束以后才釋放

MySQL5.1.22版本引入了一個(gè)新策略，新增參數(shù)innodb_autoinc_lock_mode，默認(rèn)值是1

1.這個(gè)參數(shù)設(shè)置為0，表示采用之前MySQL5.0版本的策略，即語(yǔ)句執(zhí)行結(jié)束后才釋放鎖

2.這個(gè)參數(shù)設(shè)置為1

• 普通insert語(yǔ)句，自增鎖在申請(qǐng)之后就馬上釋放

• 類似insert … select這樣的批量插入數(shù)據(jù)的語(yǔ)句，自增鎖還是要等語(yǔ)句結(jié)束后才被釋放

3.這個(gè)參數(shù)設(shè)置為2，所有的申請(qǐng)自增主鍵的動(dòng)作都是申請(qǐng)后就釋放鎖

為了數(shù)據(jù)的一致性，默認(rèn)設(shè)置為1

如果sessionB申請(qǐng)了自增值以后馬上就釋放自增鎖，那么就可能出現(xiàn)這樣的情況：

• sessionB先插入了兩行數(shù)據(jù)(1,1,1)、(2,2,2)

• sessionA來(lái)申請(qǐng)自增id得到id=3，插入了(3,5,5)

• 之后，sessionB繼續(xù)執(zhí)行，插入兩條記錄(4,3,3)、(5,4,4)

當(dāng)binlog_format=statement的時(shí)候，兩個(gè)session是同時(shí)執(zhí)行插入數(shù)據(jù)命令的，所以binlog里面對(duì)表t2的更新日志只有兩種情況：要么先記sessionA的，要么先記錄sessionB的。無(wú)論是哪一種，這個(gè)binlog拿到從庫(kù)執(zhí)行，或者用來(lái)恢復(fù)臨時(shí)實(shí)例，備庫(kù)和臨時(shí)實(shí)例里面，sessionB這個(gè)語(yǔ)句執(zhí)行出來(lái)，生成的結(jié)果里面，id都是連續(xù)的。這時(shí)，這個(gè)庫(kù)就發(fā)生了數(shù)據(jù)不一致

解決這個(gè)問(wèn)題的思路：

1）讓原庫(kù)的批量插入數(shù)據(jù)語(yǔ)句，固定生成連續(xù)的id值。所以，自增鎖直到語(yǔ)句執(zhí)行結(jié)束才釋放，就是為了達(dá)到這個(gè)目的

2）在binlog里面把插入數(shù)據(jù)的操作都如實(shí)記錄進(jìn)來(lái)，到備庫(kù)執(zhí)行的時(shí)候，不再依賴于自增主鍵去生成。也就是把innodb_autoinc_lock_mode設(shè)置為2，同時(shí)binlog_format設(shè)置為row

如果有批量插入數(shù)據(jù)（insert … select、replace … select和load data）的場(chǎng)景時(shí)，從并發(fā)插入數(shù)據(jù)性能的角度考慮，建議把innodb_autoinc_lock_mode設(shè)置為2，同時(shí)binlog_format設(shè)置為row，這樣做既能并發(fā)性，又不會(huì)出現(xiàn)數(shù)據(jù)一致性的問(wèn)題

對(duì)于批量插入數(shù)據(jù)的語(yǔ)句，MySQL有一個(gè)批量申請(qǐng)自增id的策略：

1.語(yǔ)句執(zhí)行過(guò)程中，第一次申請(qǐng)自增id，會(huì)分配1個(gè)

2.1個(gè)用完以后，這個(gè)語(yǔ)句第二次申請(qǐng)自增id，會(huì)分配2個(gè)

3.2個(gè)用完以后，還是這個(gè)語(yǔ)句，第三次申請(qǐng)自增id，會(huì)分配4個(gè)

4.依次類推，同一個(gè)語(yǔ)句去申請(qǐng)自增id，每次申請(qǐng)到的自增id個(gè)數(shù)都是上一次的兩倍

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);
create table t2 like t;
insert into t2(c,d) select c,d from t;
insert into t2 values(null, 5,5);

insert … select，實(shí)際上往表t2中插入了4行數(shù)據(jù)。但是，這四行數(shù)據(jù)是分三次申請(qǐng)的自增id，第一次申請(qǐng)到了id=1，第二次被分配了id=2和id=3，第三次被分配到id=4到id=7

由于這條語(yǔ)句實(shí)際上只用上了4個(gè)id，所以id=5到id=7就被浪費(fèi)掉了。之后，再執(zhí)行insert into t2 values(null, 5,5)，實(shí)際上插入了的數(shù)據(jù)就是(8,5,5)

這是主鍵id出現(xiàn)自增id不連續(xù)的第三種原因

五、自增主鍵用完了

自增主鍵字段在達(dá)到定義類型上限后，再插入一行記錄，則會(huì)報(bào)主鍵沖突的錯(cuò)誤

CREATE TABLE t ( id INT UNSIGNED auto_increment PRIMARY KEY ) auto_increment = 4294967295;
INSERT INTO t VALUES(NULL);
INSERT INTO t VALUES(NULL);

第一個(gè)insert語(yǔ)句插入數(shù)據(jù)成功后，這個(gè)表的AUTO_INCREMENT沒(méi)有改變（還是4294967295），就導(dǎo)致了第二個(gè)insert語(yǔ)句又拿到相同的自增id值，再試圖執(zhí)行插入語(yǔ)句，報(bào)主鍵沖突錯(cuò)誤

關(guān)于“MySQL中的自增主鍵怎么修改”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)，可以關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，小編每天都會(huì)為大家更新不同的知識(shí)點(diǎn)。

網(wǎng)站名稱：MySQL中的自增主鍵怎么修改
分享網(wǎng)址：http://chinadenli.net/article38/gosssp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供微信公眾號(hào)、商城網(wǎng)站、品牌網(wǎng)站設(shè)計(jì)、網(wǎng)站內(nèi)鏈、軟件開(kāi)發(fā)、

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)