欧美97色伦欧美一区二区日韩,国产福利片在线观看,freexxx性欧美vide0高清,西西亚洲,日本欧美国产精品第一页久久,成人18免费软件

當前位置： 維易PHP培訓學院 > MYSQL教程 > 內容正文

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

作者：VEPHP   時間 2017-09-28

《MYSQL教程MySQL批量插入遇上唯一索引避免方法》要點：
本文介紹了MYSQL教程MySQL批量插入遇上唯一索引避免方法，希望對您有用。如果有疑問，可以聯系我們。

MYSQL數據庫一、背景

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

MYSQL數據庫今天我們來了解MySQL唯一索引的一些知識：包括如何創建,如何批量插入,還有一些技巧上SQL；

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

MYSQL數據庫二、過程

MYSQL數據庫(一) 導入差異數據,忽略重復數據,IGNORE INTO的使用

MYSQL數據庫在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);

MYSQL數據庫u1_1062

MYSQL數據庫(Figure1：Duplicate entry '1' for key 'PRIMARY')

MYSQL數據庫但是在實際的生產環境中,需求往往是需要在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);

MYSQL數據庫u2_table1

MYSQL數據庫(Figure2：testtable1記錄)

MYSQL數據庫u3_table2

MYSQL數據庫(Figure3：testtable2記錄)

MYSQL數據庫通過執行上面的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;

MYSQL數據庫u4_unique

MYSQL數據庫(Figure4：Duplicate entry '101' for key 'IX_UserId')

MYSQL數據庫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值不連續記錄

MYSQL數據庫u5_效果

MYSQL數據庫(Figure5：IGNORE效果)

MYSQL數據庫(二) 導入并覆蓋重復數據,REPLACE INTO 的使用

MYSQL數據庫1. 把testtable1和testtable2分別回滾到Figure2和Figure3的狀態(使用TRUNCATE TABLE命名再執行Insert語句),這個時候再執行下面的SQL,看有什么效果：

代碼如下:

-- 導入3
REPLACE INTO testtable1(UserId,UserName)
SELECT UserId,UserName FROM testtable2;
u6_rep

MYSQL數據庫(Figure6：REPLACE效果)

MYSQL數據庫從上圖Figure6中,我們可以看到：UserId為101的記錄發生了改變,不單UserName修改了,而且UserType也變為NULL了.

MYSQL數據庫所以,如果導入中發現了重復的,先刪除再插入,如果記錄有多個字段,在插入的時候如果有的字段沒有賦值,那么新插入的記錄這些字段為空(新插入記錄的UserType都為NULL).

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

MYSQL數據庫2. 如果我們表結構UserType字段不允許為空,而且沒有默認值的情況,執行【導入3】會發生什么事情呢?

MYSQL數據庫u7_not null

MYSQL數據庫(Figure7：返回警告信息)

MYSQL數據庫u8_0

MYSQL數據庫(Figure8：UserType被設置為0)

MYSQL數據庫通過Figure7和Figure8,我們知道數據記錄還是插入了,只是返回Field 'UserType' doesn't have a default value的警告,插入記錄的UserType字段都被設置為0('UserType' 為int數據類型).

MYSQL數據庫3. 如果我們希望導入的時候一起更新UserType字段的值,這自然很簡單了,使用下面的SQL腳本就可以解決：

代碼如下:

-- 導入4
REPLACE INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2;

MYSQL數據庫u9_rep

MYSQL數據庫(Figure9：一起更新UserType)

MYSQL數據庫(三) 導入保留重復數據未指定字段,INSERT INTO ON DUPLICATE KEY UPDATE 的使用

MYSQL數據庫把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;

MYSQL數據庫u10_update

MYSQL數據庫(Figure10：保留UserType值)

MYSQL數據庫對比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;

MYSQL數據庫u11_update

MYSQL數據庫(Figure11：保留UserType值)

MYSQL數據庫對比Figure2、Figure3與Figure11,只插入testtable2表的UserId,UserName字段,但是保留testtable1表的UserType字段.如果發現有重復的記錄,做更新操作；在原有記錄基礎上,更新指定字段內容,其它字段內容保留.

MYSQL數據庫(四) 總結

MYSQL數據庫當在一個UNIQUE鍵上插入包含重復值的記錄時,默認的insert會報1062錯誤,MYSQL可以通過以上三種不同的方式和你的業務邏輯進行處理.

MYSQL數據庫三、參考文獻

MYSQL數據庫MYSQL插入處理重復鍵值的幾種方法

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/5321.html


    標簽：