在面試中，大家應該經歷過如下場景

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

你:"用的是自增主鍵"    

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

你:"因為采用自增主鍵，數(shù)據在物理結構上是順序存儲，性能最好，blabla…"    

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

你:"what，沒復習啊！！"    (然后，你就可以回去等通知了！)

這個問題是一個粉絲給我提的，我覺得挺有意(KENG)思(B)!
于是，今天我們就來談一談，這個自增主鍵用完了該怎么辦！

正文

簡單版

我們先明白一點，在mysql中，Int整型的范圍如下

我們以無符號整型為例，存儲范圍為0～4294967295，約43億！我們先說一下，一旦自增id達到最大值，此時數(shù)據繼續(xù)插入是會報一個主鍵沖突異常如下所示

//Duplicate entry '4294967295' for key 'PRIMARY'

那解決方法也是很簡單的，將Int類型改為BigInt類型，BigInt的范圍如下

就算你每秒10000條數(shù)據，跑100年，單表的數(shù)據也才
10000*24*3600*365*100=31536000000000
這數(shù)字距離BigInt的上限還差的遠，因此你將自增ID設為BigInt類型，你是不用考慮自增ID達到最大值這個問題！
然而，如果你在面試中的回答如果是

你:"簡單啊，把自增主鍵的類型改為BigInt類型就好了！"

接下來，面試官可以問你一個更坑的問題！

面試官:"你在線上怎么修改列的數(shù)據類型的？"   

你:"what！我還是回等通知吧！"

怎么改

目前業(yè)內在線修改表結構的方案，據我了解，一般有如下三種

方式一:使用mysql5.6+提供的在線修改功能

所謂的mysql自己提供的功能也就是mysql自己原生的語句，例如我們要修改原字段名稱及類型。

mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

那么，在mysql5.5這個版本之前，這是通過臨時表拷貝的方式實現(xiàn)的。執(zhí)行ALTER語句后，會新建一個帶有新結構的臨時表，將原表數(shù)據全部拷貝到臨時表，然后Rename，完成創(chuàng)建操作。這個方式過程中，原表是可讀的，不可寫。
在5.6+開始，mysql支持在線修改數(shù)據庫表，在修改表的過程中，對絕大部分操作，原表可讀，也可以寫。
那么，對于修改列的數(shù)據類型這種操作，原表還能寫么？來來來，特意去官網找了mysql8.0版本的一張圖

如圖所示，對于修改數(shù)據類型這種操作，是不支持并發(fā)的DML操作！也就是說，如果你直接使用ALTER這樣的語句在線修改表數(shù)據結構，會導致這張表無法進行更新類操作(DELETE、UPDATE、DELETE)。
因此，直接ALTER是不行滴！

那我們只能用方式二或者方式三

方式二:借助第三方工具

業(yè)內有一些第三方工具可以支持在線修改表結構，使用這些第三發(fā)工具，能夠讓你在執(zhí)行ALTER操作的時候，表不會阻塞！比較出名的有兩個

• pt-online-schema-change，簡稱pt-osc
• GitHub正式宣布以開源的方式發(fā)布的工具，名為gh-ost

以pt-osc為例，它的原理如下

1、創(chuàng)建一個新的表，表結構為修改后的數(shù)據表，用于從源數(shù)據表向新表中導入數(shù)據。

2、創(chuàng)建觸發(fā)器，用于記錄從拷貝數(shù)據開始之后，對源數(shù)據表繼續(xù)進行數(shù)據修改的操作記錄下來，用于數(shù)據拷貝結束后，執(zhí)行這些操作，保證數(shù)據不會丟失。

3、拷貝數(shù)據，從源數(shù)據表中拷貝數(shù)據到新表中。

4、rename源數(shù)據表為old表，把新表rename為源表名，并將old表刪除。

5、刪除觸發(fā)器。

然而這兩個有意(KENG)思(B)的工具，居然。。。居然。。。唉！如果你的表里有觸發(fā)器和外鍵，這兩個工具是不行滴！
如果真碰上了數(shù)據庫里有觸發(fā)器和外鍵，只能硬杠了，請看方式三
方式三:改從庫表結構，然后主從切換
此法極其麻煩，需要專業(yè)水平的選手進行操作。因為我們的mysql架構一般是讀寫分離架構，從機是用來讀的。我們直接在從庫上進行表結構修改，不會阻塞從庫的讀操作。改完之后，進行主從切換即可。唯一需要注意的是，主從切換過程中可能會有數(shù)據丟失的情況！

高深版

其實答完上面的問題后，這篇文章差不多完了。但是，還記得我在開頭說的么。這是一個很有意(KENG)思(B)的問題，為什么呢？
假設啊，你的表里的自增字段為有符號的Int類型的，也就是說，你的字段范圍為-2147483648到2147483648。
一切又那么剛好，你的自增ID是從0開始的，也就是說，現(xiàn)在你的可以用的范圍為0～2147483648。
我們明確一點，表中真實的數(shù)據ID，肯定會出現(xiàn)一些意外，ID不一定是連續(xù)的。例如，有如下情形的出現(xiàn)

CREATE TABLE `t` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`id`),
) EN

執(zhí)行下列SQL

insert into t values(null);
// 插入的行是 (1)
begin;
insert into t values(null);
rolllack;
insert into t values(null);
// 插入的行是 (3)

因此，表中的真實id必然會出現(xiàn)斷續(xù)的情況。
好，那這會你的自增主鍵id的數(shù)據范圍為0～2147483648，也就是單表21億條數(shù)據！考慮id會出現(xiàn)斷續(xù)，真實數(shù)據頂多18億條吧。
老哥，都單表18億條了，還不分庫分表？你一旦分庫分表了，就不能依賴于每個表的自增ID來全局唯一標識這些數(shù)據了。此時，我們就需要提供一 個全局唯一的ID號生成策略來支持分庫分表的環(huán)境。

因此在實際中，你根本等不到自增主鍵用完到情形！因此，專業(yè)版回答如下：

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

你:"這問題沒遇到過，因為自增主鍵我們用int類型，一般達不到最大值，我們就分庫分表了，所以不曾遇見過！"

到此這篇關于淺談MySQL中的自增主鍵用完了怎么辦的文章就介紹到這了,更多相關MySQL 自增主鍵用完內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！