快捷導(dǎo)航

MySQL的自增ID(主鍵) 用完了的解決方法

更新時(shí)間：2020年09月11日 11:01:49 作者：銘之博客

這篇文章主要介紹了MySQL的自增ID(主鍵) 用完了的解決方法，幫助大家更好的理解和學(xué)習(xí)MySQL數(shù)據(jù)庫，感興趣的朋友可以了解下

在 MySQL 中用很多類型的自增 ID，每個(gè)自增 ID 都設(shè)置了初始值。一般情況下初始值都是從 0 開始，然后按照一定的步長增加（一般是自增 1）。一般情況下，我們都是用int(11)來作為數(shù)據(jù)表的自增 ID，在 MySQL 中只要定義了這個(gè)數(shù)的字節(jié)長度，那么就會(huì)有上限。

MySQL的自增ID(主鍵) 用完了，怎么辦？

如果用 int unsigned (int，4個(gè)字節(jié) ), 我們可以算下最大當(dāng)前聲明的自增ID最大是多少，由于這里定義的是 int unsigned，所以最大可以達(dá)到2的32冪次方 - 1 = 4294967295。

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

create table `test` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295;

SQL插入語句

insert into `test` values (null);

當(dāng)想再嘗試插入一條數(shù)據(jù)時(shí)，得到了下面的異常結(jié)果。

[SQL] insert into `test` values (null);
[Err] 1062 - Duplicate entry '4294967295' for key 'PRIMARY'

說明，當(dāng)再次插入時(shí)，使用的自增ID還是 4294967295，報(bào)主鍵沖突的錯(cuò)誤，這說明 ID 值達(dá)到上限之后，就不會(huì)再變化了。4294967295，這個(gè)數(shù)字已經(jīng)可以應(yīng)付大部分的場景了，如果你的服務(wù)會(huì)經(jīng)常性的插入和刪除數(shù)據(jù)的話，還是存在用完的風(fēng)險(xiǎn)，建議采用 bigint unsigned ，這個(gè)數(shù)字就大了。

bigint unsigned 的范圍是 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型數(shù)據(jù)（所有數(shù)字），存儲(chǔ)大小為 8 個(gè)字節(jié)。

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

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

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

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

總結(jié)

數(shù)據(jù)庫表的自增 ID 達(dá)到上限之后，再申請時(shí)它的值就不會(huì)在改變了，繼續(xù)插入數(shù)據(jù)時(shí)會(huì)導(dǎo)致報(bào)主鍵沖突錯(cuò)誤。因此在設(shè)計(jì)數(shù)據(jù)表時(shí)，盡量根據(jù)業(yè)務(wù)需求來選擇合適的字段類型。

以上就是MySQL的自增ID(主鍵) 用完了的解決方法的詳細(xì)內(nèi)容，更多關(guān)于MySQL 自增ID（主鍵）的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: