首頁  >  文章  >  資料庫  >  mysql自增長id用完了該如何解決

mysql自增長id用完了該如何解決

王林
王林轉載
2023-05-31 16:52:241247瀏覽

mysql自增長id用完了,怎麼辦?

身為程式設計師,在求職面試時,不知你有沒有遇到類似這樣的問題。

張工是java程式設計師,最近到網路公司面試,面試官就問了他這樣的一個問題。

面試官:"用過mysql吧,你們資料表主鍵id是用自增主鍵還是UUID?"   

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

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

張工:"因為採用自增主鍵,資料在物理結構上是順序存儲,性能好"    

#面試官:"那自增主鍵達到最大值了,用完了怎麼辦?"    

張工:「用完了就用完了,再申請唄」

面試官:「你可以回去等通知了」

今天我們就來談一談,這個自增主鍵用完了該怎麼辦?

在mysql,int整數型的範圍如下int的值範圍為:-2^31——2^31-1,即-2147483648—2147483647

#如圖:

mysql自增長id用完了該如何解決

舉個例子,無符號整數可以儲存範圍從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

mysql自增長id用完了該如何解決

#就算每秒往資料表插入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用完了該如何解決的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述:
本文轉載於:yisu.com。如有侵權,請聯絡admin@php.cn刪除