點擊上方"碼之初"關注，···選擇"設為星標"

與精品技術文章不期而遇

在之前有篇文章中，和大家探討了在MySOL數據庫中，一個表的自增id用完，再插入數據有什么問題？評論處 @背井?公眾號的大佬建議我另開一篇再說一下表的自增id在用完的情況下，用replace into、insert ignore以及insert... on duplicate會發(fā)生什么，當時不假思索就回復了安排，結果這一拖就快一個月了。

海岳尚可傾，吐諾終不移。今天想到這件事，就立馬先把標題寫出來了，這樣就能督促自己把這篇文章完成，你以為我是不想失信于尤慕大佬嗎？是的，我不想！但同時，我更不想失信于自己！言歸正傳，開始我們今天的主題，當Mysql的一張表自增id用完了，此時再以各種姿勢向表里插入數據會發(fā)生些什么呢？上一篇文章覺得說的不太好，有一些我想介紹的知識點沒有講到，所以通過這篇跟大家一起通過實踐詳細的探討一番。

先創(chuàng)建一個簡單的表，只包含一個自增字段id，像這樣，勾選上自動遞增和無符號

此時我們可以看到右邊的建表語句，注意一點，類型int(10)后面多了一個unsigned關鍵字，不知道你們是否都知道unsigned是什么意思，又是怎么出來的，如果不知道，就要好好看下去了，這是一個很重要的知識點。

unsigned

首先，勾選上左側的無符號才會出現unsigned類型。那unsigned是什么意思呢？整型的每一種都有無符號(unsigned)和有符號(signed)兩種類型，在默認情況下聲明的整型變量都是有符號的類型(char有點特別)。由于在計算機中,整數是以補碼形式存放的。根據最高位的不同，如果是1,有符號數的話就是負數；如果是無符號數,則都解釋為正數。同時在相同位數的情況下,所能表達的整數范圍變大。另外，unsigned若省略后一個關鍵字，大多數編譯器都會認為是unsigned int。有沒有不知所云或者不明覺厲，簡單點解釋就是，在mysql中創(chuàng)建表時，如果整型使用了unsigned類型，那么該數據項就永遠是正整數，每種數值類型的名稱和取值范圍如下表所示：

下面通過sql實例演示一下，我們先去掉勾選無符號，建表語句變成

這時候已經沒有unsigned了，我們向表里插入兩條數據，一條正數一條負數：

INSERT INTO t VALUES (1);INSERT INTO t VALUES (-1);

結果顯示插入成功

表里也有數據：

接下來我們修改表結構，勾選上無符號使用unsigned類型，像最初那樣

這時候我們再向表里插入兩條數據，一條正數一條負數：

INSERT INTO t VALUES (1);INSERT INTO t VALUES (-1);

看看執(zhí)行結果：

發(fā)現-1不能插入表中，報錯超出了范圍值，因為使用了unsigned之后，int類型的值范圍變成了0到4294967295(0 到232?- 1)4個字節(jié)，參考上面的表格：

介紹了unsigned無符號類型之后，那大家應該都知道了使用了int?unsigned類型字段數據理論上最大為4294967295，那么接下來我們就挑戰(zhàn)極限，看看當一張表自增id用完了，此時再以各種姿勢向表里插入數據會發(fā)生些什么？

姿勢一：insert into

我們先直接暴力輸出，在創(chuàng)建表的時候，直接將AUTO_INCREMENT的初始值聲明為4294967295，sql如下：

CREATE TABLE `t` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

然后我們友好的向表中插一條數據：

INSERT?INTO?t?VALUES?(NULL);你們猜一猜會發(fā)生什么，能插入成功嗎？成功后表里長什么樣子？先看一下執(zhí)行結果：

咦，成功了，什么情況，我們打開表看看：

這是什么鬼？我不是插入的null嗎？

先解釋第一個疑問，廢話，當然能插進去了，因為AUTO_INCREMENT=4294967295是從這個開始，當然還能插進去，為什么插入null變成了4294967295，因為只有一個字段，插入的null又不是id的值，id是自增主鍵啊，現在明白了吧，有可能有人覺得我這兩個問題好無聊，但是我真的見過有不少人不知道，不信你以后面試時可以隨機問問。

高潮來了，我們這時候再插入一條數據呢，還能插進去嗎？走起：

INSERT?INTO?t?VALUES?(NULL);

看結果，oh no，報主鍵沖突了：

這是為什么呢？上面我故意只截圖了一半，我們看看第一次插入null成功，我們打開表后看看表的DDL語句：

可以看到，當再次插入時，使用的自增ID還是 4294967295，這時候就會報主鍵沖突的錯誤。其實4294967295這個數字已經可以滿足大部分的應用場景了，如果你的服務會經常性的插入和刪除數據的話，還是存在用完的風險，建議采用bigint unsigned，這個數字就大了。姿勢二：replace insert如果表的自增id用完時，我們使用replace into繼續(xù)插數據時，會發(fā)生什么呢？我們先直接執(zhí)行：REPLACE?INTO?t?VALUES?(NULL);

可以發(fā)現和之前報一樣的錯，都是主鍵沖突。

那么，是不是insert into和replace into這兩條sql在自增id用完這種情況下，繼續(xù)插入數據時發(fā)生了一樣的遭遇呢？

我們下來看一下自增id未滿的情況下，這兩條sql是怎樣執(zhí)行的。

1、新建一張表，自增id就從1開始(默認也是從1)

DROP TABLE IF EXISTS t1;CREATE TABLE `t1` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`num` int(10) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

2、先使用insert into插入一條數據：

INSERT?INTO?t1?VALUES?(1,1);

查看結果：

看一下表結果：

3、再使用replace into：

REPLACE?INTO?t1?VALUES?(1,1);

查看結果：

看一下表結果：

4、再使用replace into：

REPLACE?INTO?t1?VALUES?(1,2);

查看執(zhí)行結果：

再看一下表結果：

看到這兒你有發(fā)現什么了嗎？第一次insert into和replace into執(zhí)行后受影響的行都是1行，第二次執(zhí)行replace into后受影響的行變成了2行，為什么呢？這就是replace into和insert into的不同之處了。

劃重點來了：

replace into 跟 insert 功能類似，不同點在于：replace into 首先嘗試插入數據到表中，這時候：

如果發(fā)現表中已經有此行數據(根據主鍵或者唯一索引判斷)則先刪除此行數據，然后插入新的數據。

否則，直接插入新數據。

所以，最后一次執(zhí)行replace into時受影響的行變成了2行。知道了replace into的執(zhí)行原理后，我們回到自增id最大時，replace into報錯，發(fā)生了什么呢？如果發(fā)現表中已經有了最大的id，會先刪除這條數據，然后重新插入，但是此時雖然刪除了此條數據，自增id依然是最大值，為什么呢？這就是自增主鍵沒有持久化的bug。究其原因，在于自增主鍵的分配，是由InnoDB數據字典內部一個計數器來決定的，而該計數器只在內存中維護，并不會持久化到磁盤中，所以還會報主鍵沖突的錯誤。姿勢三：insert ignore into

insert into ignore就比較簡單了，我們直接運行sql看結果：

insert?ignore?into?t?VALUES?(NULL);

結果顯示：

可以看到受影響的行為0，這就是insert ignore 的作用：

如果發(fā)現表中已經有此行數據(根據主鍵或者唯一索引判斷)則跳過此查詢，不對數據庫作任何操作;

否則沒有此行數據的話，直接插入新數據。

姿勢四：insert ...on duplicate

老樣子，我們先通過sql實例看看自增id最大時，使用insert... on duplicate key會發(fā)生什么，sql運行起來：

INSERT?INTO?t?VALUES?(NULL)?ON?DUPLICATE?KEY?UPDATE?id?=id+1;

查看運行結果：

依舊是主鍵沖突錯誤。

下面我們來簡單分析一下INSERT ON DUPLICATE KEY UPDATE這條sql做了什么事。我們在日常業(yè)務開發(fā)中經常有這樣一個場景，首先創(chuàng)建一條記錄，然后插入到數據庫；如果數據庫已經存在同一主鍵的記錄，則執(zhí)行update操作，如果不存在，則執(zhí)行insert操作，這個時候INSERT ON DUPLICATE KEY UPDATE就派上用場了。在MySQL數據庫中，如果在insert語句后面帶上ON DUPLICATE KEY UPDATE 子句，而要插入的行與表中現有記錄的惟一索引或主鍵中產生重復值，那么就會發(fā)生舊行的更新；如果插入的行數據與現有表中記錄的唯一索引或者主鍵不重復，則執(zhí)行新紀錄插入操作。另外，ON DUPLICATE KEY UPDATE不能寫where條件。以上就是當Mysql的一張表自增id用完了，此時再以各種姿勢向表里插入數據會發(fā)生什么的一些實踐探討，同時也加了其他一些知識點的講解，感謝尤慕大佬的建議，讓我在漫漫路上多了一些求索的動力。

知識拓展

如果在創(chuàng)建表時沒有顯示聲明主鍵，會怎么辦呢？如果是這種情況，InnoDB會自動幫你創(chuàng)建一個不可見的、長度為6字節(jié)的row_id，而且InnoDB 維護了一個全局的 dictsys.row_id，所以未定義主鍵的表都共享該row_id，每次插入一條數據，都把全局row_id當成主鍵id，然后全局row_id加1，該全局row_id在代碼實現上使用的是bigint unsigned類型，但實際上只給row_id留了6字節(jié)，這種設計就會存在一個問題：如果全局row_id一直漲，一直漲，直到2的48冪次-1時，這個時候再+1，row_id的低48位都為0，結果在插入新一行數據時，拿到的row_id就為0，存在主鍵沖突的可能性。所以，為了避免這種隱患，每個表都需要定一個主鍵。

最后，留下一個問題，大家知道int(0)和int(10)有什么區(qū)別嗎？

好文，點個在看吧

總結

以上是生活随笔為你收集整理的android怎样判断插入数据是否成功_MySQL一个表的自增id用完了，背井大佬让我用这些姿势再往里插数据...的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。