MySQL自增長(zhǎng)ID用完了，怎么辦?

作為一名程序員，在求職面試時(shí)，不知你有沒(méi)有遇到類(lèi)似這樣的問(wèn)題。

張工是一名java程序員，最近到一家互聯(lián)網(wǎng)公司面試，面試官就問(wèn)了他這樣的一個(gè)問(wèn)題。

面試官:"用過(guò)mysql吧，你們數(shù)據(jù)表主鍵id是用自增主鍵還是UUID?"

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

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

張工:"因?yàn)椴捎米栽鲋麈I，數(shù)據(jù)在物理結(jié)構(gòu)上是順序存儲(chǔ)，性能好"

面試官:"那自增主鍵達(dá)到最大值了，用完了怎么辦?"

張工：“用完了就用完了，再申請(qǐng)唄”

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

今天我們就來(lái)談一談，這個(gè)自增主鍵用完了該怎么辦?

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

如圖：

以無(wú)符號(hào)整型為例，存儲(chǔ)范圍為0～4294967295，約43億。當(dāng)自增id達(dá)到最大值時(shí)，這是繼續(xù)插入會(huì)出現(xiàn)什么異常呢，

我們來(lái)動(dòng)手實(shí)踐下。

首先，創(chuàng)建一張表tb_user，這張表只包含一個(gè)自增id

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

然后向這張表插入一條數(shù)據(jù)：

insert into tb_user values(null);

通過(guò)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

細(xì)心的你會(huì)發(fā)現(xiàn) AUTO_INCREMENT 已經(jīng)變成2，不過(guò)這離最大值4294967295遠(yuǎn)著呢，要想讓它變成4294967295得插入非常多的記錄，其實(shí)不用這么麻煩，我們可以在創(chuàng)建表的時(shí)候，直接聲明AUTO_INCREMENT的初始值。

把我們剛才的創(chuàng)建表語(yǔ)句調(diào)整下，先把剛才的表刪除掉，然后在創(chuàng)建表時(shí)加上auto_increment = 4294967295

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

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

insert into tb_user values(null);

同樣，我們通過(guò)show命令，查看表tb_user的表結(jié)構(gòu)：

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

通過(guò)

select * from tb_user

我們查詢(xún)到id 為4294967295，已經(jīng)是最大值，這時(shí)候如果再

當(dāng)想往表在嘗試插入一條數(shù)據(jù)時(shí)，報(bào)一個(gè)主鍵沖突異常如下所示。

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

這可以說(shuō)明，當(dāng)再次插入時(shí)，使用的自增ID還是4294967295，就會(huì)報(bào)主鍵沖突的異常了。

4294967295，這個(gè)數(shù)字已經(jīng)可以應(yīng)付大部分的場(chǎng)景了，如果你的服務(wù)會(huì)經(jīng)常性的插入和刪除數(shù)據(jù)的話(huà)，還是存在用完的風(fēng)險(xiǎn)。

建議采用bigint unsigned，這個(gè)數(shù)字就大了。

那有什么辦法解決，答案是肯定的，解決方法也是很簡(jiǎn)單的，將Int類(lèi)型改為BigInt類(lèi)型，BigInt的范圍如下

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

-9223372036854775808 9223372036854775807

就算每秒往數(shù)據(jù)表插入10000條數(shù)據(jù)，運(yùn)行100年，來(lái)看看數(shù)據(jù)量有多少

10000*24*3600*365*100=31536000000000

這數(shù)字距離BigInt的上限還差的遠(yuǎn)，因此你將自增ID設(shè)為BigInt類(lèi)型，就可以解決問(wèn)題了。

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

你:"這還不簡(jiǎn)單，把自增主鍵的類(lèi)型改為BigInt類(lèi)型就可以解決了!"

面試官:"你在線(xiàn)上怎么修改列的數(shù)據(jù)類(lèi)型的?"

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

面試官：“你有實(shí)際操作經(jīng)驗(yàn)嗎?”

你：“…………沒(méi)有實(shí)際操作過(guò)”

需要注意的是，這種方式在myl5.6+才開(kāi)始支持，mysql支持在線(xiàn)修改數(shù)據(jù)庫(kù)表，在修改表的過(guò)程中，對(duì)絕大部分操作，原表可讀，也可以寫(xiě)。

對(duì)于修改數(shù)據(jù)類(lèi)型這種操作，是不支持并發(fā)的DML操作!也就是說(shuō)，如果你直接使用alter這樣的語(yǔ)句在線(xiàn)修改表數(shù)據(jù)結(jié)構(gòu)，會(huì)導(dǎo)致這張表無(wú)法進(jìn)行更新類(lèi)操作(delete、update、insert)。所以，想在生產(chǎn)線(xiàn)上執(zhí)行修改表結(jié)構(gòu)這樣的方案是不可行的。

那有沒(méi)有更好的方式，對(duì)于這個(gè)問(wèn)題，我們以后再做討論。

不知你有沒(méi)有留意到這樣一種情況，雖然主鍵自增ID是從0開(kāi)始的，也就是說(shuō)，現(xiàn)在可以用的范圍為0～2147483647，但實(shí)際數(shù)據(jù)中有些id的值并不是連續(xù)的。

要是實(shí)際生產(chǎn)表出現(xiàn)單表超過(guò)上億的數(shù)據(jù)量了，這時(shí)候想再往數(shù)據(jù)表寫(xiě)數(shù)據(jù)，性能肯定是受影響了，得趕緊考慮分庫(kù)分表了。

一旦分庫(kù)分表了，我們就不能依賴(lài)于每個(gè)表的自增id來(lái)全局唯一標(biāo)識(shí)這些數(shù)據(jù)了。此時(shí)，我們就需要提供一 個(gè)全局唯一的id號(hào)生成策略來(lái)支持分庫(kù)分表的環(huán)境。

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

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

面試官:"那自增主鍵達(dá)到最大值了，用完了怎么辦?"

你:這問(wèn)題沒(méi)遇到過(guò)，因?yàn)樽栽鲋麈I我們用int類(lèi)型，一般達(dá)不到最大值，就要考慮分表分庫(kù)了。

要是面試官窮追不舍，繼續(xù)問(wèn)你有關(guān)分庫(kù)分表的要點(diǎn)，你也就可以針對(duì)性地回答，說(shuō)明你完全有這方面的開(kāi)發(fā)經(jīng)驗(yàn)，相信能為這次面試加分。

總結(jié)：

mysql數(shù)據(jù)庫(kù)表的自增 ID 達(dá)到上限之后，這時(shí)候再申請(qǐng)它的值就不會(huì)再改變了，如果繼續(xù)插入數(shù)據(jù)就會(huì)導(dǎo)致報(bào)主鍵沖突異常。

因此在做數(shù)據(jù)字典設(shè)計(jì)時(shí)，要根據(jù)業(yè)務(wù)的需求來(lái)選擇合適的字段類(lèi)型。