MySQL 主鍵 自增 ID 會用完嗎？

• 2019 年 12 月 18 日
• 筆記

MySQL 主鍵 自增 ID 會用完嗎？

首先我們一般創建 MySQL 數據表的時候，大部分情況下會創建一個自增主鍵ID 的欄位，可能你的建表語句如下：

CREATE TABLE IF NOT EXISTS `tb`(     `id` INT UNSIGNED AUTO_INCREMENT,     `title` VARCHAR(100) NOT NULL,     `body` VARCHAR(100) NOT NULL,     PRIMARY KEY ( `id` )  )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

可以看到 ID 為 int 類型，所謂 int 類型，就會有長度限制，那麼我們去看下 他的長度限制是多少 打開 MYSQL 官方網站，我們看下 INT 數據類型會有多少的範圍 https://dev.mysql.com/doc/refman/8.0/en/integer-types.html

可以看到 INT 類型的取值範圍是 -2147483648 – 4294967295。所以 在 MySQL 中 自增 ID 是會用完的。那麼問題來了，加入他的 ID 用完會發生什麼事呢？

我們來驗證下。在 MySQL 命令行中依次輸入以下建表、插入語句看看會發生什麼

DROP TABLE IF EXISTS `tb`;    CREATE TABLE IF NOT EXISTS `tb`(     `id` INT UNSIGNED AUTO_INCREMENT,     PRIMARY KEY ( `id` )  )ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;    INSERT INTO tb VALUES(null);    INSERT INTO tb VALUES(null);

得出的結論是 主鍵衝突，當然這條數據也不會插入進去了。

所以在以後的建表過程中要衡量你的業務會不會超出這個量級。如果會那麼久需要創建 8 位元組的 INT 類型了，他的值最大是 2^64-1

那麼問題又來了，你說 我有些業務是不需要主鍵 、不需要自增編號，我不創建這個欄位，就好了，這樣想恭喜你 回答錯誤.

如果你創建的 InnoDB 表沒有指定主鍵，那麼 InnoDB 會給你創建一個不可見的，長度為 6 個位元組的 row_id。InnoDB 維護了一個全局的 dict_sys.row_id 值，所有無主鍵的 InnoDB 表，每插入一行數據，都將當前的 dict_sys.row_id 值作為要插入數據的 row_id，然後把 dict_sys.row_id 的值加 1。

實際上，在程式碼實現時 row_id 是一個長度為 8 位元組的無符號長整型 (bigint unsigned)。但是，InnoDB 在設計時，給 row_id 留的只是 6 個位元組的長度，這樣寫到數據表中時只放了最後 6 個位元組，所以 row_id 能寫到數據表中的值，就有兩個特徵：

• row_id 寫入表中的值範圍，是從 0 到 2^48-1
• 當 dict_sys.row_id=2^48時，如果再有插入數據的行為要來申請 row_id，拿到以後再取最後 6 個位元組的話就是 0。 也就是說，寫入表的 row_id 是從 0 開始到 2^48-1。達到上限後，下一個值就是 0，然後繼續循環。

當然，2^48-1 這個值本身已經很大了，但是如果一個 MySQL 實例跑得足夠久的話，還是可能達到這個上限的。在 InnoDB 邏輯里，申請到 row_id=N 後，就將這行數據寫入表中；如果表中已經存在 row_id=N 的行，新寫入的行就會覆蓋原有的行。

總結： 自增 ID 用完 會報主鍵衝突、數據插入失敗。

不指定主鍵、默認創建的 row_id 會 覆蓋原有的數據。