以前使用SQL Server進(jìn)行表分區(qū)的時(shí)候就碰到很多關(guān)于唯一索引的問(wèn)題,今天我們來(lái)了解MySQL唯一索引的一些知識(shí)：包括如何創(chuàng)建，如何批量插入，還有一些技巧上SQL,感興趣的朋友可以了解下

一、背景

以前使用SQL Server進(jìn)行表分區(qū)的時(shí)候就碰到很多關(guān)于唯一索引的問(wèn)題：Step8：SQL Server 當(dāng)表分區(qū)遇上唯一約束，沒(méi)想到在MySQL的分區(qū)中一樣會(huì)遇到這樣的問(wèn)題：MySQL表分區(qū)實(shí)戰(zhàn)。

今天我們來(lái)了解MySQL唯一索引的一些知識(shí)：包括如何創(chuàng)建，如何批量插入，還有一些技巧上SQL；

這些問(wèn)題的根源在什么地方？有什么共同點(diǎn)？MySQL中也有分區(qū)對(duì)齊的概念？唯一索引是在很多系統(tǒng)中都會(huì)出現(xiàn)的要求，有什么辦法可以避免？它對(duì)性能的影響有多大？

二、過(guò)程

(一) 導(dǎo)入差異數(shù)據(jù)，忽略重復(fù)數(shù)據(jù)，IGNORE INTO的使用

在MySQL創(chuàng)建表的時(shí)候，我們通常創(chuàng)建一個(gè)表的時(shí)候是以一個(gè)自增ID值作為主鍵，那么MySQL就會(huì)以PRIMARY KEY作為聚集索引鍵和主鍵，既然是主鍵，那當(dāng)然是唯一的了，所以重復(fù)執(zhí)行下面的插入語(yǔ)句會(huì)報(bào)1062錯(cuò)誤：如Figure1所示；
代碼如下:
-- 創(chuàng)建測(cè)試表
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;

-- 插入測(cè)試數(shù)據(jù)
INSERT INTO testtable(Id,UserId,UserName,UserType)
VALUES(1,101,'aa',1),(2,102,'bbb',2),(3,103,'ccc',3);

但是在實(shí)際的生產(chǎn)環(huán)境中，需求往往是需要在UserId鍵值中設(shè)置唯一索引，今天我就以這個(gè)作為示例，進(jìn)行唯一索引的測(cè)試：
代碼如下:
-- 創(chuàng)建測(cè)試表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)建測(cè)試表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;

-- 插入測(cè)試數(shù)據(jù)1
INSERT INTO testtable1(Id,UserId,UserName,UserType)
VALUES(1,101,'aa',1),(2,102,'bbb',2),(3,103,'ccc',3);

-- 插入測(cè)試數(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：記錄）

（Figure3：testtable2記錄）

通過(guò)執(zhí)行上面的SQL腳本，我們?cè)趖esttable1和testtable2都創(chuàng)建了唯一索引：UNIQUE KEY `IX_UserId` (`UserId`)，這就說(shuō)明UserId在testtable1和testtable2表中都是唯一的，如果把testtable2的數(shù)據(jù)批量導(dǎo)入到testtable1，如果執(zhí)行下面【導(dǎo)入1】的SQL，就會(huì)出現(xiàn)1062的錯(cuò)誤，導(dǎo)致整個(gè)過(guò)程會(huì)回滾，沒(méi)有達(dá)到導(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提供一個(gè)關(guān)鍵字：IGNORE，這個(gè)關(guān)鍵字判斷每條記錄是否存在，是否違反餓了表中的唯一索引，如果存在就不插入，而不存在的記錄就會(huì)插入。
代碼如下:
-- 導(dǎo)入2
INSERT IGNORE INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2;

所以執(zhí)行完【導(dǎo)入2】，就會(huì)產(chǎn)生Figure5的結(jié)果，這已經(jīng)達(dá)到了我們的目的了，但是你有沒(méi)發(fā)現(xiàn)自增的ID值跳過(guò)了一些值，這是因?yàn)槲覀冎皥?zhí)行【導(dǎo)入1】失敗造成的，雖然我們的事務(wù)回滾了，但是自增ID會(huì)出現(xiàn)斷層。在SQL Server中也會(huì)有這樣的問(wèn)題。擴(kuò)展閱讀：簡(jiǎn)單實(shí)用SQL腳本Part：查找SQL Server 自增ID值不連續(xù)記錄

（5：IGNORE效果）

1. 把testtable1和testtable2分別回滾到Figure2和Figure3的狀態(tài)（使用TRUNCATE TABLE命名再執(zhí)行Insert語(yǔ)句），這個(gè)時(shí)候再執(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ù)的，先刪除再插入，如果記錄有多個(gè)字段，在插入的時(shí)候如果有的字段沒(méi)有賦值，那么新插入的記錄這些字段為空（新插入記錄的UserType都為NULL）。

需要注意的是，當(dāng)你replace的時(shí)候，如果被插入的表如果沒(méi)有指定列，會(huì)用NULL表示，而不是這個(gè)表原來(lái)的內(nèi)容。如果插入的內(nèi)容列和被插入的表列一樣，則不會(huì)出現(xiàn)NULL。

2. 如果我們表結(jié)構(gòu)UserType字段不允許為空，而且沒(méi)有默認(rèn)值的情況，執(zhí)行【導(dǎo)入3】會(huì)發(fā)生什么事情呢？

（Figure7：返回警告信息）

（Figure8：UserType被設(shè)置為0）

通過(guò)Figure7和Figure8，我們知道數(shù)據(jù)記錄還是插入了，只是返回Field 'UserType' doesn't have a default value的警告，插入記錄的UserType字段都被設(shè)置為0（'UserType' 為int數(shù)據(jù)類型）。

3. 如果我們希望導(dǎo)入的時(shí)候一起更新UserType字段的值，這自然很簡(jiǎn)單了，使用下面的SQL腳本就可以解決：
代碼如下:
-- 導(dǎo)入4
REPLACE INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2;

把testtable1和testtable2分別回滾到Figure2和Figure3的狀態(tài)（使用TRUNCATE TABLE命名再執(zhí)行Insert語(yǔ)句），這個(gè)時(shí)候再執(zhí)行下面的SQL，看有什么效果：
代碼如下:
-- 導(dǎo)入5
INSERT INTO testtable1(UserId,UserName)
SELECT UserId,UserName FROM testtable2
ON DUPLICATE KEY UPDATE
testtable1.UserName = testtable2.UserName;

UserId為101的記錄：更新了UserName的值，保留了UserType的值；但是由于【導(dǎo)入5】中沒(méi)有指定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值）

對(duì)比Figure2、Figure3與Figure11，只插入testtable2表的UserId,UserName字段，但是保留testtable1表的UserType字段。如果發(fā)現(xiàn)有重復(fù)的記錄，做更新操作；在原有記錄基礎(chǔ)上，更新指定字段內(nèi)容，其它字段內(nèi)容保留。

(四) 總結(jié)

當(dāng)在一個(gè)UNIQUE鍵上插入包含重復(fù)值的記錄時(shí)，默認(rèn)的insert會(huì)報(bào)1062錯(cuò)誤，MYSQL可以通過(guò)以上三種不同的方式和你的業(yè)務(wù)邏輯進(jìn)行處理。

三、參考文獻(xiàn)