MySQL中可以設(shè)置auto_increment，作用是新增數(shù)據(jù)時(shí)不需要手動(dòng)添加主鍵，輸入null或未指定值時(shí)就會(huì)把a(bǔ)uto_increment的值賦給自增主鍵。

一般來說，自增主鍵都是連續(xù)的，但是，有四個(gè)場景自增主鍵不是連續(xù)的！(使用的是InnoDB引擎)

第一種、自增初始值和自增步長設(shè)置不為 1

InnoDB引擎有兩個(gè)屬性:auto_increment_offset(初始值)和auto_increment_increment(步長)，一般默認(rèn)為1，即初始值1，每次自增1，所以子增值就是1，2，3，4，5...這就是連續(xù)的.

如果設(shè)置這兩個(gè)屬性，自增值就會(huì)從初始值開始，每次加一次步長得到下一個(gè)數(shù)，比如設(shè)置初始值為3，步長為2，那自增值就是3，5，7，9...這就不是連續(xù)的了.

第二種、唯一鍵沖突

插入數(shù)據(jù)時(shí)，如果有設(shè)置唯一的字段重復(fù)了，那么就會(huì)報(bào)錯(cuò)，插入失敗，但此時(shí)系統(tǒng)的自增值還是會(huì)增加一次，這是由于insert語句的執(zhí)行流程導(dǎo)致的:

• 1.執(zhí)行器調(diào)用引擎準(zhǔn)備插入數(shù)據(jù)(null，1，1)
• 2.發(fā)現(xiàn)沒有主鍵值，獲取自增值2(表里已經(jīng)有數(shù)據(jù)1，1，1，假設(shè)第二個(gè)字段重復(fù))
• 3.將插入的數(shù)據(jù)改成(2，1，1)
• 4.自增值變?yōu)?
• 5.執(zhí)行插入操作，第二個(gè)字段重復(fù)，報(bào) Duplicate key error，插入失敗

這個(gè)流程說明自增是在執(zhí)行插入數(shù)據(jù)之前，所以哪怕語句失敗了，自增值還是會(huì)自增，這時(shí)候再插入數(shù)據(jù)就會(huì)變成(3，2，1)了

第三種、事物回滾

MySQL里有個(gè)rollback，它是TCL語句，叫事務(wù)控制語句，比如commit和rollback，前者是提交，后者是回滾，作用是撤銷當(dāng)前事務(wù)中已經(jīng)進(jìn)行的所有修改，使數(shù)據(jù)庫返回到事務(wù)開始之前的狀態(tài)!

當(dāng)我們執(zhí)行了插入操作后，自增值會(huì)增加，此時(shí)我們回滾，數(shù)據(jù)會(huì)回到插入之前的數(shù)據(jù)，但自增值不會(huì)跟著回到之前的自增值，這么設(shè)計(jì)的原因是為了提高性能.

因?yàn)槿绻藭r(shí)有多個(gè)并行執(zhí)行的事務(wù)(并行執(zhí)行時(shí)會(huì)加鎖)，而回滾也會(huì)回滾自增值的話，就可能會(huì)導(dǎo)致主鍵重復(fù)沖突.為了解決這種沖突，要么就是判斷賦值自增值時(shí)表里是否已經(jīng)有此自增值，要么把鎖的范圍擴(kuò)大到一個(gè)事務(wù)執(zhí)行完并提交，無論是哪種方法，都會(huì)影響性能，所以才會(huì)設(shè)計(jì)自增值不會(huì)回滾。

第四種、批量插入

對于批量插入的數(shù)據(jù)，MySQL有一個(gè)批量申請自增的策略：

• 第一次申請自增時(shí)，會(huì)分配1個(gè)值
• 第二次時(shí)，會(huì)分配2個(gè)值
• 第三次時(shí)，會(huì)分配4個(gè)值
• ...

以此類推，同一個(gè)語句申請自增時(shí)，每次申請的個(gè)數(shù)都是上一次的兩倍

(這里說的批量插入不是insert into table values()，這類語句是可以精準(zhǔn)計(jì)算出需要多少自增值的，不知道需要多少自增值的語句是insert...select、replace … select 和 load data)

舉個(gè)例子

有5個(gè)數(shù)據(jù)需要插入，(1，1)，(2，2)，(3，3)，(4，4)，(5，5)

• 第一次申請，申請一個(gè)id:1
• 第二次，申請兩個(gè)id:2，3
• 第三次，申請四個(gè)id:4，5，6，7

此時(shí)自增值就變成了8，下次插入數(shù)據(jù)時(shí)就會(huì)把8賦值給主鍵！

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評論