MySQL批量插入遇上唯一索引避免方法
一、背景
以前使用SQL Server進行表分區(qū)的時候就碰到很多關(guān)于唯一索引的問題：Step8：SQL Server 當表分區(qū)遇上唯一約束，沒想到在MySQL的分區(qū)中一樣會遇到這樣的問題：MySQL表分區(qū)實戰(zhàn)。
今天我們來了解MySQL唯一索引的一些知識：包括如何創(chuàng)建，如何批量插入，還有一些技巧上SQL；
這些問題的根源在什么地方？有什么共同點？MySQL中也有分區(qū)對齊的概念？唯一索引是在很多系統(tǒng)中都會出現(xiàn)的要求，有什么辦法可以避免？它對性能的影響有多大？
二、過程
(一) 導(dǎo)入差異數(shù)據(jù)，忽略重復(fù)數(shù)據(jù)，IGNORE INTO的使用
在MySQL創(chuàng)建表的時候，我們通常創(chuàng)建一個表的時候是以一個自增ID值作為主鍵，那么MySQL就會以PRIMARY KEY作為聚集索引鍵和主鍵，既然是主鍵，那當然是唯一的了，所以重復(fù)執(zhí)行下面的插入語句會報1062錯誤：如Figure1所示；
-- 創(chuàng)建測試表
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;
-- 插入測試數(shù)據(jù)
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'）
但是在實際的生產(chǎn)環(huán)境中，需求往往是需要在UserId鍵值中設(shè)置唯一索引，今天我就以這個作為示例，進行唯一索引的測試：
-- 創(chuàng)建測試表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;
-- 創(chuàng)建測試表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;
-- 插入測試數(shù)據(jù)1
INSERT INTO testtable1(Id,UserId,UserName,UserType)
VALUES(1,101,'aa',1),(2,102,'bbb',2),(3,103,'ccc',3);
-- 插入測試數(shù)據(jù)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記錄）
通過執(zhí)行上面的SQL腳本，我們在testtable1和testtable2都創(chuàng)建了唯一索引：UNIQUE KEY `IX_UserId` (`UserId`)，這就說明UserId在testtable1和testtable2表中都是唯一的，如果把testtable2的數(shù)據(jù)批量導(dǎo)入到testtable1，如果執(zhí)行下面【導(dǎo)入1】的SQL，就會出現(xiàn)1062的錯誤，導(dǎo)致整個過程會回滾，沒有達到導(dǎo)入差異數(shù)據(jù)的目的。
INSERT INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2;
（Figure4：Duplicate entry '101' for key 'IX_UserId'）
MySQL提供一個關(guān)鍵字：IGNORE，這個關(guān)鍵字判斷每條記錄是否存在，是否違反餓了表中的唯一索引，如果存在就不插入，而不存在的記錄就會插入。
-- 導(dǎo)入2
INSERT IGNORE INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2;
所以執(zhí)行完【導(dǎo)入2】，就會產(chǎn)生Figure5的結(jié)果，這已經(jīng)達到了我們的目的了，但是你有沒發(fā)現(xiàn)自增的ID值跳過了一些值，這是因為我們之前執(zhí)行【導(dǎo)入1】失敗造成的，雖然我們的事務(wù)回滾了，但是自增ID會出現(xiàn)斷層。在SQL Server中也會有這樣的問題。擴展閱讀：簡單實用SQL腳本Part：查找SQL Server 自增ID值不連續(xù)記錄
（Figure5：IGNORE效果）
(二) 導(dǎo)入并覆蓋重復(fù)數(shù)據(jù)，REPLACE INTO 的使用
1. 把testtable1和testtable2分別回滾到Figure2和Figure3的狀態(tài)（使用TRUNCATE TABLE命名再執(zhí)行Insert語句），這個時候再執(zhí)行下面的SQL，看有什么效果：
-- 導(dǎo)入3
REPLACE INTO testtable1(UserId,UserName)
SELECT UserId,UserName FROM testtable2;
（Figure6：REPLACE效果）
從上圖Figure6中，我們可以看到：UserId為101的記錄發(fā)生了改變，不單UserName修改了，而且UserType也變?yōu)镹ULL了。
所以，如果導(dǎo)入中發(fā)現(xiàn)了重復(fù)的，先刪除再插入，如果記錄有多個字段，在插入的時候如果有的字段沒有賦值，那么新插入的記錄這些字段為空（新插入記錄的UserType都為NULL）。
需要注意的是，當你replace的時候，如果被插入的表如果沒有指定列，會用NULL表示，而不是這個表原來的內(nèi)容。如果插入的內(nèi)容列和被插入的表列一樣，則不會出現(xiàn)NULL。
2. 如果我們表結(jié)構(gòu)UserType字段不允許為空，而且沒有默認值的情況，執(zhí)行【導(dǎo)入3】會發(fā)生什么事情呢？
（Figure7：返回警告信息）
（Figure8：UserType被設(shè)置為0）
通過Figure7和Figure8，我們知道數(shù)據(jù)記錄還是插入了，只是返回Field 'UserType' doesn't have a default value的警告，插入記錄的UserType字段都被設(shè)置為0（'UserType' 為int數(shù)據(jù)類型）。
3. 如果我們希望導(dǎo)入的時候一起更新UserType字段的值，這自然很簡單了，使用下面的SQL腳本就可以解決：
-- 導(dǎo)入4
REPLACE INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2;
（Figure9：一起更新UserType）
(三) 導(dǎo)入保留重復(fù)數(shù)據(jù)未指定字段，INSERT INTO ON DUPLICATE KEY UPDATE 的使用
把testtable1和testtable2分別回滾到Figure2和Figure3的狀態(tài)（使用TRUNCATE TABLE命名再執(zhí)行Insert語句），這個時候再執(zhí)行下面的SQL，看有什么效果：
-- 導(dǎo)入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的值；但是由于【導(dǎo)入5】中沒有指定UserType，所以新插入記錄的UserType是為NULL的。
-- 導(dǎo)入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字段。如果發(fā)現(xiàn)有重復(fù)的記錄，做更新操作；在原有記錄基礎(chǔ)上，更新指定字段內(nèi)容，其它字段內(nèi)容保留。
(四) 總結(jié)
當在一個UNIQUE鍵上插入包含重復(fù)值的記錄時，默認的insert會報1062錯誤，MYSQL可以通過以上三種不同的方式和你的業(yè)務(wù)邏輯進行處理。
三、參考文獻
相關(guān)文章
MySQL5.6升級5.7時出現(xiàn)主從延遲問題排查過程
這篇文章主要介紹了MySQL5.6升級5.7時出現(xiàn)主從延遲問題排查過程，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-09-09
mysql 5.7.17 安裝配置方法圖文教程（windows10）
這篇文章主要為大家分享了mysql 5.7.17 安裝配置方法圖文教程，具有一定的參考價值，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-01-01
這篇文章主要介紹了MySQL中數(shù)據(jù)類型的驗證 的相關(guān)資料,需要的朋友可以參考下2016-04-04
MySQL數(shù)據(jù)庫10秒內(nèi)插入百萬條數(shù)據(jù)的實現(xiàn)
假設(shè)現(xiàn)在我們要向mysql插入500萬條數(shù)據(jù),如何實現(xiàn)高效快速的插入進去？本文就詳細的介紹一下，感興趣的可以了解一下2021-10-10
MySQL是目前來說最好的 RDBMS應(yīng)用軟件之一，這篇文章主要給大家介紹了關(guān)于MySql基本操作的相關(guān)資料，非常適合mysql新手入門，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-05-05
這篇文章主要介紹了mysql中的limit和offset用法詳解,limit一般被用來排序,offset一般和limit組合使用,本文對這兩個函數(shù)進行詳細介紹,需要的朋友可以參考下2023-10-10
下面小編就為大家?guī)硪黄猰ysql 列轉(zhuǎn)行的技巧(分享)。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-03-03
這篇文章主要介紹MySQL的索引及優(yōu)化，索引是幫助MySQL進行高效查詢的一種數(shù)據(jù)結(jié)構(gòu)。好比一本書的目錄，能加快查詢的速度，想進一步了解的小伙伴可以詳細閱讀本文2023-03-03
MySQL 百萬級分頁優(yōu)化(Mysql千萬級快速分頁)
MySql 性能到底能有多高？用了php半年多，真正如此深入的去思考這個問題還是從前天開始。有過痛苦有過絕望，到現(xiàn)在充滿信心2012-11-11
mysql數(shù)據(jù)庫刪除重復(fù)數(shù)據(jù)只保留一條方法實例
這篇文章主要給大家介紹了關(guān)于mysql數(shù)據(jù)庫刪除重復(fù)數(shù)據(jù)，只保留一條的相關(guān)資料，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-03-03