面試官問：MySQL 的自增 ID 用完了，怎么辦？

點擊關注“SQL數(shù)據(jù)庫開發(fā)”，

設為“置頂或星標”，第一時間送達干貨

來源：程序猿面試指南

既然這塊知識點不清楚，那回頭就自己動手實踐下。

首先，創(chuàng)建一個最簡單的表，只包含一個自增 id，并插入一條數(shù)據(jù)。

create table t0(id int unsigned auto\_increment primary key) ;insert into t0 values(null);

通過 show 命令 show create table t0; 查看表情況

CREATE TABLE \`t0\` (  \`id\` int(10) unsigned NOT NULL AUTO\_INCREMENT,  PRIMARY KEY (\`id\`)) ENGINE=InnoDB AUTO\_INCREMENT=2 DEFAULT CHARSET=utf8

可以發(fā)現(xiàn) AUTO_INCREMENT 已經(jīng)自動變成 2，這離用完還有很遠，我們可以算下最大當前聲明的自增 ID 最大是多少，由于這里定義的是 intunsigned，所以最大可以達到 2 的 32 冪次方 - 1 = 4294967295

這里有個小技巧，可以在創(chuàng)建表的時候，直接聲明 AUTO_INCREMENT 的初始值

create table t1(id int unsigned auto\_increment primary key)  auto\_increment = 4294967295;insert into t1 values(null);

同樣，通過 show 命令，查看 t1 的表結構

CREATE TABLE \`t1\` (  \`id\` int(10) unsigned NOT NULL AUTO\_INCREMENT,  PRIMARY KEY (\`id\`)) ENGINE=InnoDB AUTO\_INCREMENT=4294967295 DEFAULT CHARSET=utf8

可以發(fā)現(xiàn)，AUTO_INCREMENT 已經(jīng)變成 4294967295 了，當想再嘗試插入一條數(shù)據(jù)時，得到了下面的異常結果

17:28:03    insert into t1 values(null) Error Code: 1062. Duplicate entry '4294967295' for key 'PRIMARY'    0.00054 sec

說明，當再次插入時，使用的自增 ID 還是 4294967295，報主鍵沖突的錯誤。

4294967295，這個數(shù)字已經(jīng)可以應付大部分的場景了，如果你的服務會經(jīng)常性的插入和刪除數(shù)據(jù)的話，還是存在用完的風險，建議采用 bigint unsigned，這個數(shù)字就大了。

不過，還存在另一種情況，如果在創(chuàng)建表沒有顯示申明主鍵，會怎么辦？

如果是這種情況，InnoDB 會自動幫你創(chuàng)建一個不可見的、長度為 6 字節(jié)的 row_id，而且 InnoDB 維護了一個全局的 dictsys.row_id，所以未定義主鍵的表都共享該 row_id，每次插入一條數(shù)據(jù)，都把全局 row_id 當成主鍵 id，然后全局 row_id 加 1

該全局 row_id 在代碼實現(xiàn)上使用的是 bigint unsigned 類型，但實際上只給 row_id 留了 6 字節(jié)，這種設計就會存在一個問題：如果全局 row_id 一直漲，一直漲，直到 2 的 48 冪次 - 1 時，這個時候再 + 1，row_id 的低 48 位都為 0，結果在插入新一行數(shù)據(jù)時，拿到的 row_id 就為 0，存在主鍵沖突的可能性。

所以，為了避免這種隱患，每個表都需要定一個主鍵。

最后給大家分享我寫的SQL兩件套：《SQL基礎知識第二版》和《SQL高級知識第二版》的PDF電子版。里面有各個語法的解釋、大量的實例講解和批注等等，非常通俗易懂，方便大家跟著一起來實操。

有需要的讀者可以下載學習，在下面的公眾號「數(shù)據(jù)前線」(非本號)后臺回復關鍵字：SQL，就行
數(shù)據(jù)前線

后臺回復關鍵字：1024，獲取一份精心整理的技術干貨
后臺回復關鍵字：進群，帶你進入高手如云的交流群

記得點「贊」和「在看」↓

愛你們