MySQL批量插入遇上唯一索引避免方法

一、背景

以前使用SQL Server進行表分區的時候就碰到很多關於唯一索引的問題：Step8：SQL Server 當表分區遇上唯一約束，沒想到在MySQL的分區中一樣會遇到這樣的問題：MySQL表分區實戰。

今天我們來瞭解MySQL唯一索引的一些知識：包括如何建立，如何批量插入，還有一些技巧上SQL；

這些問題的根源在什麼地方？有什麼共同點？MySQL中也有分區對齊的概念？唯一索引是在很多系統中都會出現的要求，有什麼辦法可以避免？它對效能的影響有多大？

二、過程

(一) 匯入差異資料，忽略重複資料，IGNORE INTO的使用

在MySQL建立表的時候，我們通常建立一個表的時候是以一個自增ID值作為主鍵，那麼MySQL就會以PRIMARY KEY作為叢集索引鍵和主鍵，既然是主鍵，那當然是唯一的了，所以重複執行下面的插入語句會報1062錯誤：如Figure1所示；

複製代碼 代碼如下:-- 建立測試表
CREATE TABLE `testtable` (
`Id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`UserId` INT(11) DEFAULT NULL,
`UserName` VARCHAR(10) DEFAULT NULL,
`UserType` INT(11) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 插入測試資料
INSERT INTO testtable(Id,UserId,UserName,UserType)
VALUES(1,101,'aa',1),(2,102,'bbb',2),(3,103,'ccc',3);

（Figure1：Duplicate entry '1' for key 'PRIMARY'）

但是在實際的生產環境中，需求往往是需要在UserId索引值中設定唯一索引，今天我就以這個作為樣本，進行唯一索引的測試：

複製代碼 代碼如下:-- 建立測試表1
CREATE TABLE `testtable1` (
`Id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`UserId` INT(11) DEFAULT NULL,
`UserName` VARCHAR(10) DEFAULT NULL,
`UserType` INT(11) DEFAULT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `IX_UserId` (`UserId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 建立測試表2
CREATE TABLE `testtable2` (
`Id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`UserId` INT(11) DEFAULT NULL,
`UserName` VARCHAR(10) DEFAULT NULL,
`UserType` INT(11) DEFAULT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `IX_UserId` (`UserId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 插入測試資料1
INSERT INTO testtable1(Id,UserId,UserName,UserType)
VALUES(1,101,'aa',1),(2,102,'bbb',2),(3,103,'ccc',3);

-- 插入測試資料2
INSERT INTO testtable2(Id,UserId,UserName,UserType)
VALUES(1,201,'aaa',1),(2,202,'bbb',2),(3,203,'ccc',3),(4,101,'xxxx',5);

（Figure2：testtable1記錄）

（Figure3：testtable2記錄）

通過執行上面的SQL指令碼，我們在testtable1和testtable2都建立了唯一索引：UNIQUE KEY `IX_UserId` (`UserId`)，這就說明UserId在testtable1和testtable2表中都是唯一的，如果把testtable2的資料大量匯入到testtable1，如果執行下面【匯入1】的SQL，就會出現1062的錯誤，導致整個過程會復原，沒有達到匯入差異資料的目的。

複製代碼 代碼如下:INSERT INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2;

（Figure4：Duplicate entry '101' for key 'IX_UserId'）

MySQL提供一個關鍵字：IGNORE，這個關鍵字判斷每條記錄是否存在，是否違反餓了表中的唯一索引，如果存在就不插入，而不存在的記錄就會插入。

複製代碼 代碼如下:-- 匯入2
INSERT IGNORE INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2;

所以執行完【匯入2】，就會產生Figure5的結果，這已經達到了我們的目的了，但是你有沒發現自增的ID值跳過了一些值，這是因為我們之前執行【匯入1】失敗造成的，雖然我們的交易回復了，但是自增ID會出現斷層。在SQL Server中也會有這樣的問題。擴充閱讀：簡單實用SQL指令碼Part：尋找SQL Server 自增ID值不連續記錄

（Figure5：IGNORE效果）

(二) 匯入並覆蓋重複資料，REPLACE INTO 的使用

1. 把testtable1和testtable2分別復原到Figure2和Figure3的狀態（使用TRUNCATE TABLE命名再執行Insert語句），這個時候再執行下面的SQL，看有什麼效果：

複製代碼 代碼如下:-- 匯入3
REPLACE INTO testtable1(UserId,UserName)
SELECT UserId,UserName FROM testtable2;

（Figure6：REPLACE效果）

從Figure6中，我們可以看到：UserId為101的記錄發生了改變，不單UserName修改了，而且UserType也變為NULL了。

所以，如果匯入中發現了重複的，先刪除再插入，如果記錄有多個欄位，在插入的時候如果有的欄位沒有賦值，那麼新插入的記錄這些欄位為空白（新插入記錄的UserType都為NULL）。

需要注意的是，當你replace的時候，如果被插入的表如果沒有指定列，會用NULL表示，而不是這個表原來的內容。如果插入的內容列和被插入的表列一樣，則不會出現NULL。

2. 如果我們表結構UserType欄位不允許為空白，而且沒有預設值的情況，執行【匯入3】會發生什麼事情呢？

（Figure7：返回警告資訊）

（Figure8：UserType被設定為0）

通過Figure7和Figure8，我們知道資料記錄還是插入了，只是返回Field 'UserType' doesn't have a default value的警告，插入記錄的UserType欄位都被設定為0（'UserType' 為int資料類型）。

3. 如果我們希望匯入的時候一起更新UserType欄位的值，這自然很簡單了，使用下面的SQL指令碼就可以解決：

複製代碼 代碼如下:-- 匯入4
REPLACE INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2;

（Figure9：一起更新UserType）

(三) 匯入保留重複資料未指定欄位，INSERT INTO ON DUPLICATE KEY UPDATE 的使用

把testtable1和testtable2分別復原到Figure2和Figure3的狀態（使用TRUNCATE TABLE命名再執行Insert語句），這個時候再執行下面的SQL，看有什麼效果：

複製代碼 代碼如下:-- 匯入5
INSERT INTO testtable1(UserId,UserName)
SELECT UserId,UserName FROM testtable2
ON DUPLICATE KEY UPDATE
testtable1.UserName = testtable2.UserName;

（Figure10：保留UserType值）

對比Figure2、Figure3與Figure10，UserId為101的記錄：更新了UserName的值，保留了UserType的值；但是由於【匯入5】中沒有指定UserType，所以新插入記錄的UserType是為NULL的。

複製代碼 代碼如下:-- 匯入6
INSERT INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2
ON DUPLICATE KEY UPDATE
testtable1.UserName = testtable2.UserName;

（Figure11：保留UserType值）

對比Figure2、Figure3與Figure11，只插入testtable2表的UserId,UserName欄位，但是保留testtable1表的UserType欄位。如果發現有重複的記錄，做更新操作；在原有記錄基礎上，更新指定欄位內容，其它欄位內容保留。

(四) 總結

當在一個UNIQUE鍵上插入包含重複值的記錄時，預設的insert會報1062錯誤，MYSQL可以通過以上三種不同的方式和你的商務邏輯進行處理。

三、參考文獻

MYSQL插入處理重複索引值的幾種方法