在之前有篇文章中愕难，和大家探討了在MySOL數(shù)據(jù)庫中早龟，一個表的自增id用完，再插入數(shù)據(jù)有什么問題猫缭？評論處有大佬建議我另開一篇再說一下表的自增id在用完的情況下葱弟，用replace into、insert ignore以及insert... on duplicate會發(fā)生什么猜丹，當(dāng)時不假思索就回復(fù)了安排芝加，結(jié)果這一拖就快一個月了。
海岳尚可傾射窒，吐諾終不移藏杖。今天想到這件事，就立馬先把標(biāo)題寫出來了脉顿，這樣就能督促自己把這篇文章完成蝌麸，你以為我是不想失信于尤慕大佬嗎？是的艾疟，我不想祥楣！但同時开财，我更不想失信于自己！
言歸正傳误褪，開始我們今天的主題责鳍，當(dāng)Mysql的一張表自增id用完了，此時再以各種姿勢向表里插入數(shù)據(jù)會發(fā)生些什么呢兽间？上一篇文章覺得說的不太好历葛，有一些我想介紹的知識點(diǎn)沒有講到，所以通過這篇跟大家一起通過實(shí)踐詳細(xì)的探討一番嘀略。
先創(chuàng)建一個簡單的表恤溶，只包含一個自增字段id，像這樣帜羊，勾選上自動遞增和無符號
此時我們可以看到右邊的建表語句咒程，注意一點(diǎn)，類型int(10)后面多了一個unsigned關(guān)鍵字讼育，不知道你們是否都知道unsigned是什么意思帐姻，又是怎么出來的，如果不知道奶段，就要好好看下去了饥瓷，這是一個很重要的知識點(diǎn)。
unsigned
首先痹籍，勾選上左側(cè)的無符號才會出現(xiàn)unsigned類型呢铆。那unsigned是什么意思呢？整型的每一種都有無符號（unsigned）和有符號（signed）兩種類型蹲缠，在默認(rèn)情況下聲明的整型變量都是有符號的類型（char有點(diǎn)特別）棺克。由于在計(jì)算機(jī)中,整數(shù)是以補(bǔ)碼形式存放的。根據(jù)最高位的不同线定，如果是1,有符號數(shù)的話就是負(fù)數(shù)逆航；如果是無符號數(shù),則都解釋為正數(shù)。同時在相同位數(shù)的情況下,所能表達(dá)的整數(shù)范圍變大渔肩。另外因俐，unsigned若省略后一個關(guān)鍵字，大多數(shù)編譯器都會認(rèn)為是unsigned int周偎。有沒有不知所云或者不明覺厲抹剩，簡單點(diǎn)解釋就是，在mysql中創(chuàng)建表時蓉坎，如果整型使用了unsigned類型澳眷，那么該數(shù)據(jù)項(xiàng)就永遠(yuǎn)是正整數(shù)，每種數(shù)值類型的名稱和取值范圍如下表所示：
下面通過sql實(shí)例演示一下蛉艾，我們先去掉勾選無符號钳踊，建表語句變成
這時候已經(jīng)沒有unsigned了衷敌，我們向表里插入兩條數(shù)據(jù)，一條正數(shù)一條負(fù)數(shù)：
INSERT INTO t VALUES (1);
結(jié)果顯示插入成功
表里也有數(shù)據(jù)：
接下來我們修改表結(jié)構(gòu)拓瞪，勾選上無符號使用unsigned類型缴罗，像最初那樣
這時候我們再向表里插入兩條數(shù)據(jù)，一條正數(shù)一條負(fù)數(shù)：
INSERT INTO t VALUES (1);
看看執(zhí)行結(jié)果：
發(fā)現(xiàn)-1不能插入表中祭埂，報(bào)錯超出了范圍值面氓，因?yàn)槭褂昧藆nsigned之后，int類型的值范圍變成了0到4294967295（0 到232 - 1）4個字節(jié)蛆橡，參考上面的表格：
介紹了unsigned無符號類型之后舌界，那大家應(yīng)該都知道了使用了int unsigned類型字段數(shù)據(jù)理論上最大為4294967295，那么接下來我們就挑戰(zhàn)極限泰演，看看當(dāng)一張表自增id用完了呻拌，此時再以各種姿勢向表里插入數(shù)據(jù)會發(fā)生些什么？
姿勢一：insert into
我們先直接暴力輸出睦焕，在創(chuàng)建表的時候藐握，直接將AUTO_INCREMENT的初始值聲明為4294967295，sql如下：
CREATE TABLE `t` (
然后我們友好的向表中插一條數(shù)據(jù)：
INSERT INTO t VALUES (NULL);
你們猜一猜會發(fā)生什么复亏，能插入成功嗎趾娃？成功后表里長什么樣子缭嫡？先看一下執(zhí)行結(jié)果：
咦缔御，成功了，什么情況妇蛀，我們打開表看看：
這是什么鬼耕突？我不是插入的null嗎？
先解釋第一個疑問评架，廢話眷茁，當(dāng)然能插進(jìn)去了，因?yàn)锳UTO_INCREMENT=4294967295是從這個開始纵诞，當(dāng)然還能插進(jìn)去上祈，為什么插入null變成了4294967295，因?yàn)橹挥幸粋€字段浙芙，插入的null又不是id的值登刺，id是自增主鍵啊，現(xiàn)在明白了吧嗡呼，有可能有人覺得我這兩個問題好無聊纸俭，但是我真的見過有不少人不知道，不信你以后面試時可以隨機(jī)問問南窗。
高潮來了揍很，我們這時候再插入一條數(shù)據(jù)呢郎楼，還能插進(jìn)去嗎？走起：
INSERT INTO t VALUES (NULL);
看結(jié)果窒悔，oh no呜袁，報(bào)主鍵沖突了：
這是為什么呢？上面我故意只截圖了一半蛉迹，我們看看第一次插入null成功傅寡，我們打開表后看看表的DDL語句：
可以看到，當(dāng)再次插入時北救，使用的自增ID還是 4294967295荐操，這時候就會報(bào)主鍵沖突的錯誤。其實(shí)4294967295這個數(shù)字已經(jīng)可以滿足大部分的應(yīng)用場景了珍策，如果你的服務(wù)會經(jīng)常性的插入和刪除數(shù)據(jù)的話托启，還是存在用完的風(fēng)險(xiǎn)，建議采用bigint unsigned攘宙，這個數(shù)字就大了屯耸。
姿勢二：replace insert
如果表的自增id用完時，我們使用replace into繼續(xù)插數(shù)據(jù)時蹭劈，會發(fā)生什么呢疗绣？我們先直接執(zhí)行：
REPLACE INTO t VALUES (NULL);
可以發(fā)現(xiàn)和之前報(bào)一樣的錯，都是主鍵沖突铺韧。
那么多矮，是不是insert into和replace into這兩條sql在自增id用完這種情況下，繼續(xù)插入數(shù)據(jù)時發(fā)生了一樣的遭遇呢哈打？
我們下來看一下自增id未滿的情況下塔逃，這兩條sql是怎樣執(zhí)行的。
1料仗、新建一張表湾盗，自增id就從1開始(默認(rèn)也是從1)
DROP TABLE IF EXISTS t1;
2、先使用insert into插入一條數(shù)據(jù)：
INSERT INTO t1 VALUES (1,1);
查看結(jié)果：
看一下表結(jié)果：
3立轧、再使用replace into：
REPLACE INTO t1 VALUES (1,1);
查看結(jié)果：
看一下表結(jié)果：
4格粪、再使用replace into：
REPLACE INTO t1 VALUES (1,2);
查看執(zhí)行結(jié)果：
再看一下表結(jié)果：
看到這兒你有發(fā)現(xiàn)什么了嗎？第一次insert into和replace into執(zhí)行后受影響的行都是1行氛改，第二次執(zhí)行replace into后受影響的行變成了2行帐萎，為什么呢？這就是replace into和insert into的不同之處了平窘。
劃重點(diǎn)來了：
replace into 跟 insert 功能類似吓肋，不同點(diǎn)在于：replace into 首先嘗試插入數(shù)據(jù)到表中，這時候：
如果發(fā)現(xiàn)表中已經(jīng)有此行數(shù)據(jù)（根據(jù)主鍵或者唯一索引判斷）則先刪除此行數(shù)據(jù)瑰艘，然后插入新的數(shù)據(jù)是鬼。
否則肤舞，直接插入新數(shù)據(jù)。
所以均蜜，最后一次執(zhí)行replace into時受影響的行變成了2行李剖。知道了replace into的執(zhí)行原理后，我們回到自增id最大時囤耳，replace into報(bào)錯篙顺，發(fā)生了什么呢？如果發(fā)現(xiàn)表中已經(jīng)有了最大的id充择，會先刪除這條數(shù)據(jù)德玫，然后重新插入，但是此時雖然刪除了此條數(shù)據(jù)椎麦，自增id依然是最大值宰僧，為什么呢？這就是自增主鍵沒有持久化的bug观挎。究其原因琴儿，在于自增主鍵的分配，是由InnoDB數(shù)據(jù)字典內(nèi)部一個計(jì)數(shù)器來決定的嘁捷，而該計(jì)數(shù)器只在內(nèi)存中維護(hù)惩坑，并不會持久化到磁盤中斥铺，所以還會報(bào)主鍵沖突的錯誤喷兼。
姿勢三：insert ignore into
insert into ignore就比較簡單了棵介，我們直接運(yùn)行sql看結(jié)果：
insert ignore into t VALUES (NULL);
結(jié)果顯示：
可以看到受影響的行為0，這就是insert ignore 的作用：
如果發(fā)現(xiàn)表中已經(jīng)有此行數(shù)據(jù)（根據(jù)主鍵或者唯一索引判斷）則跳過此查詢现诀，不對數(shù)據(jù)庫作任何操作;
否則沒有此行數(shù)據(jù)的話夷磕，直接插入新數(shù)據(jù)履肃。
姿勢四：insert ...on duplicate
老樣子仔沿，我們先通過sql實(shí)例看看自增id最大時，使用insert... on duplicate key會發(fā)生什么尺棋，sql運(yùn)行起來：
INSERT INTO t VALUES (NULL) ON DUPLICATE KEY UPDATE id =id+1;
查看運(yùn)行結(jié)果：
依舊是主鍵沖突錯誤封锉。
下面我們來簡單分析一下INSERT ON DUPLICATE KEY UPDATE這條sql做了什么事。我們在日常業(yè)務(wù)開發(fā)中經(jīng)常有這樣一個場景膘螟，首先創(chuàng)建一條記錄成福，然后插入到數(shù)據(jù)庫；如果數(shù)據(jù)庫已經(jīng)存在同一主鍵的記錄荆残，則執(zhí)行update操作奴艾，如果不存在，則執(zhí)行insert操作内斯，這個時候INSERT ON DUPLICATE KEY UPDATE就派上用場了蕴潦。在MySQL數(shù)據(jù)庫中像啼，如果在insert語句后面帶上ON DUPLICATE KEY UPDATE 子句，而要插入的行與表中現(xiàn)有記錄的惟一索引或主鍵中產(chǎn)生重復(fù)值潭苞，那么就會發(fā)生舊行的更新忽冻；如果插入的行數(shù)據(jù)與現(xiàn)有表中記錄的唯一索引或者主鍵不重復(fù)，則執(zhí)行新紀(jì)錄插入操作此疹。另外僧诚，ON DUPLICATE KEY UPDATE不能寫where條件。
以上就是當(dāng)Mysql的一張表自增id用完了蝗碎，此時再以各種姿勢向表里插入數(shù)據(jù)會發(fā)生什么的一些實(shí)踐探討湖笨，同時也加了其他一些知識點(diǎn)的講解，希望能給看到的小伙伴有所幫助和啟發(fā)蹦骑。
知識拓展
如果在創(chuàng)建表時沒有顯示聲明主鍵赶么，會怎么辦呢？如果是這種情況脊串，InnoDB會自動幫你創(chuàng)建一個不可見的辫呻、長度為6字節(jié)的row_id，而且InnoDB 維護(hù)了一個全局的 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ì)就會存在一個問題：如果全局row_id一直漲匾寝，一直漲，直到2的48冪次-1時荷腊，這個時候再+1艳悔，row_id的低48位都為0，結(jié)果在插入新一行數(shù)據(jù)時女仰，拿到的row_id就為0猜年，存在主鍵沖突的可能性。所以疾忍，為了避免這種隱患乔外，每個表都需要定一個主鍵。
最后一罩，留下一個問題杨幼，大家知道int(0)和int(10)有什么區(qū)別嗎？
