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

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

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

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

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

第二種、唯一鍵沖突

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

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

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

第三種、事物回滾

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

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

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

第四種、批量插入

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

• 第一次申請自增時，會分配1個值
• 第二次時，會分配2個值
• 第三次時，會分配4個值
• ...

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

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

舉個例子

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

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

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

總結(jié)

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

最新評論