mysql自增長id用完瞭該怎麼辦

Posted on by

mysql自增長id用完瞭，怎麼辦？

作為一名程序員，在求職面試時，不知你有沒有遇到類似這樣的問題。

張工是一名java程序員，最近到一傢互聯網公司面試，面試官就問瞭他這樣的一個問題。

面試官:"用過mysql吧，你們數據表主鍵id是用自增主鍵還是UUID？"   

張工:"用的是自增主鍵"    

面試官:"為什麼是自增主鍵？"    

張工:"因為采用自增主鍵，數據在物理結構上是順序存儲，性能好"    

面試官:"那自增主鍵達到最大值瞭，用完瞭怎麼辦？"    

張工：“用完瞭就用完瞭，再申請唄”

面試官：“你可以回去等通知瞭”

今天我們就來談一談，這個自增主鍵用完瞭該怎麼辦？

在mysql，int整型的范圍如下int的取值范圍為：-2^31——2^31-1，即-2147483648—2147483647

如圖：

cec642de02c5612df27ac603ab76147b.png

以無符號整型為例，存儲范圍為0～4294967295，約43億。當自增id達到最大值時，這是繼續插入會出現什麼異常呢，

我們來動手實踐下。

首先，創建一張表tb_user，這張表隻包含一個自增id

create table  tb_user(id int unsigned auto_increment primary key) ;

然後向這張表插入一條數據：

insert into tb_user values(null);

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

CREATE TABLE `tb_user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

細心的你會發現 AUTO_INCREMENT 已經變成2，不過這離最大值4294967295遠著呢，要想讓它變成4294967295得插入非常多的記錄，其實不用這麼麻煩，我們可以在創建表的時候，直接聲明AUTO_INCREMENT的初始值。

把我們剛才的創建表語句調整下，先把剛才的表刪除掉，然後在創建表時加上auto_increment = 4294967295

create table tb_user(id int unsigned auto_increment primary key) auto_increment = 4294967295;

 然後同樣往表插入一條記錄

insert into tb_user values(null);

同樣，我們通過show命令，查看表tb_user的表結構：

CREATE TABLE `tb_user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8

通過

select * from tb_user

我們查詢到id 為4294967295，已經是最大值，這時候如果再

當想往表在嘗試插入一條數據時，報一個主鍵沖突異常如下所示。

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

這可以說明，當再次插入時，使用的自增ID還是4294967295，就會報主鍵沖突的異常瞭。

4294967295，這個數字已經可以應付大部分的場景瞭，如果你的服務會經常性的插入和刪除數據的話，還是存在用完的風險。

建議采用bigint unsigned，這個數字就大瞭。

那有什麼辦法解決，答案是肯定的，解決方法也是很簡單的，將Int類型改為BigInt類型，BigInt的范圍如下

-2^63-1到2^63-1

-9223372036854775808  9223372036854775807

60b16171c8fd2871721816a21e683447.png

就算每秒往數據表插入10000條數據，運行100年，來看看數據量有多少

10000*24*3600*365*100=31536000000000

這數字距離BigInt的上限還差的遠，因此你將自增ID設為BigInt類型，就可以解決問題瞭。

如果你在面試中是這樣回答面試官的。

你:"這還不簡單，把自增主鍵的類型改為BigInt類型就可以解決瞭！"

面試官:"你在線上怎麼修改列的數據類型的？"   

你:"alter table tb_user change id  id bigint;"

面試官：“你有實際操作經驗嗎？”

你：“…………沒有實際操作過”

需要註意的是，這種方式在myl5.6+才開始支持，mysql支持在線修改數據庫表，在修改表的過程中，對絕大部分操作，原表可讀，也可以寫。

對於修改數據類型這種操作，是不支持並發的DML操作！也就是說，如果你直接使用alter這樣的語句在線修改表數據結構，會導致這張表無法進行更新類操作(delete、update、insert)。所以，想在生產線上執行修改表結構這樣的方案是不可行的。

那有沒有更好的方式，對於這個問題，我們以後再做討論。

不知你有沒有留意到這樣一種情況，雖然主鍵自增ID是從0開始的，也就是說，現在可以用的范圍為0～2147483647，但實際數據中有些id的值並不是連續的。

要是實際生產表出現單表超過上億的數據量瞭，這時候想再往數據表寫數據，性能肯定是受影響瞭，得趕緊考慮分庫分表瞭。

一旦分庫分表瞭，我們就不能依賴於每個表的自增id來全局唯一標識這些數據瞭。此時，我們就需要提供一 個全局唯一的id號生成策略來支持分庫分表的環境。

所以在實際中，根本等不到自增主鍵用完的情況。

較友好的回答不妨參考這樣的

面試官:"那自增主鍵達到最大值瞭，用完瞭怎麼辦？"   

你:這問題沒遇到過，因為自增主鍵我們用int類型，一般達不到最大值，就要考慮分表分庫瞭。

要是面試官窮追不舍，繼續問你有關分庫分表的要點，你也就可以針對性地回答，說明你完全有這方面的開發經驗，相信能為這次面試加分。

總結：

mysql數據庫表的自增 ID 達到上限之後，這時候再申請它的值就不會在改變瞭，如果繼續插入數據就會導致報主鍵沖突異常。

因此在做數據字典設計時，要根據業務的需求來選擇合適的字段類型。

到此這篇關於mysql自增長id用完瞭該怎麼辦的文章就介紹到這瞭,更多相關mysql自增長id內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：