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

概述介绍《MYSQL教程MySQL批量插入遇上唯一索引避免方法》开发教程，希望对您有用。

《MysqL教程MysqL批量插入遇上唯一索引避免方法》要点：
本文介绍了MysqL教程MysqL批量插入遇上唯一索引避免方法，希望对您有用。如果有疑问，可以联系我们。
@H_301_5@

MysqL数据库一、背景@H_301_5@

MysqL数据库以前使用sql Server进行表分区的时候就碰到很多关于唯一索引的问题：Step8：sql Server 当表分区遇上唯一约束,没想到在MysqL的分区中一样会遇到这样的问题：MysqL表分区实战.@H_301_5@

MysqL数据库今天我们来了解MysqL唯一索引的一些知识：包括如何创建,如何批量插入,还有一些技巧上sql；@H_301_5@

MysqL数据库这些问题的根源在什么地方?有什么共同点?MysqL中也有分区对齐的概念?唯一索引是在很多系统中都会出现的要求,有什么办法可以避免?它对性能的影响有多大?@H_301_5@

MysqL数据库二、过程@H_301_5@

MysqL数据库(一) 导入差异数据,忽略重复数据,IGnorE INTO的使用@H_301_5@

MysqL数据库在MysqL创建表的时候,我们通常创建一个表的时候是以一个自增ID值作为主键,那么MysqL就会以PRIMARY KEY作为聚集索引键和主键,既然是主键,那当然是唯一的了,所以重复执行下面的插入语句会报1062错误：如figure1所示；
@H_301_5@ 代码如下:
-- 创建测试表
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数据库

@H_301_5@@H_301_5@

MysqL数据库(figure1：Duplicate entry '1' for key 'PRIMARY')@H_301_5@

MysqL数据库但是在实际的生产环境中,需求往往是需要在UserID键值中设置唯一索引,今天我就以这个作为示例,进行唯一索引的测试：
@H_301_5@ 代码如下:
-- 创建测试表1
CREATE table `testtable1` (
`ID` INT(11) UNSIGNED NOT NulL auto_INCREMENT,
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,
UNIQUE KEY `IX_UserID` (`UserID`)
) ENGINE=INNODB DEFAulT CHARSET=utf8;

-- 插入测试数据1
INSERT INTO testtable1(ID,3);

-- 插入测试数据2
INSERT INTO testtable2(ID,201,'aaa',202,203,3),(4,'xxxx',5);

MysqL数据库

@H_301_5@@H_301_5@

MysqL数据库(figure2：testtable1记录)@H_301_5@

MysqL数据库

@H_301_5@@H_301_5@

MysqL数据库(figure3：testtable2记录)@H_301_5@

MysqL数据库通过执行上面的sql脚本,我们在testtable1和testtable2都创建了唯一索引：UNIQUE KEY `IX_UserID` (`UserID`),这就说明UserID在testtable1和testtable2表中都是唯一的,如果把testtable2的数据批量导入到testtable1,如果执行下面【导入1】的sql,就会出现1062的错误,导致整个过程会回滚,没有达到导入差异数据的目的.
@H_301_5@ 代码如下:
INSERT INTO testtable1(UserID,UserType)
SELECT UserID,UserType FROM testtable2;

MysqL数据库

@H_301_5@@H_301_5@

MysqL数据库(figure4：Duplicate entry '101' for key 'IX_UserID')@H_301_5@

MysqL数据库MysqL提供一个关键字：IGnorE,这个关键字判断每条记录是否存在,是否违反饿了表中的唯一索引,如果存在就不插入,而不存在的记录就会插入.
@H_301_5@ 代码如下:
-- 导入2
INSERT IGnorE INTO testtable1(UserID,UserType FROM testtable2;

所以执行完【导入2】,就会产生figure5的结果,这已经达到了我们的目的了,但是你有没发现自增的ID值跳过了一些值,这是因为我们之前执行【导入1】失败造成的,虽然我们的事务回滚了,但是自增ID会出现断层.在sql Server中也会有这样的问题.扩展阅读：简单实用sql脚本Part：查找sql Server 自增ID值不连续记录

MysqL数据库

@H_301_5@@H_301_5@

MysqL数据库(figure5：IGnorE效果)@H_301_5@

MysqL数据库(二) 导入并覆盖重复数据,REPLACE INTO 的使用@H_301_5@

MysqL数据库1. 把testtable1和testtable2分别回滚到figure2和figure3的状态(使用TruncATE table命名再执行Insert语句),这个时候再执行下面的sql,看有什么效果：
@H_301_5@ 代码如下:
-- 导入3
REPLACE INTO testtable1(UserID,Username)
SELECT UserID,Username FROM testtable2;

@H_301_5@

MysqL数据库(figure6：REPLACE效果)@H_301_5@

MysqL数据库从上图figure6中,我们可以看到：UserID为101的记录发生了改变,不单Username修改了,而且UserType也变为NulL了.@H_301_5@

MysqL数据库所以,如果导入中发现了重复的,先删除再插入,如果记录有多个字段,在插入的时候如果有的字段没有赋值,那么新插入的记录这些字段为空(新插入记录的UserType都为NulL).@H_301_5@

MysqL数据库需要注意的是,当你replace的时候,如果被插入的表如果没有指定列,会用NulL表示,而不是这个表原来的内容.如果插入的内容列和被插入的表列一样,则不会出现NulL.@H_301_5@

MysqL数据库2. 如果我们表结构UserType字段不允许为空,而且没有默认值的情况,执行【导入3】会发生什么事情呢?@H_301_5@

MysqL数据库

@H_301_5@@H_301_5@

MysqL数据库(figure7：返回警告信息)@H_301_5@

MysqL数据库

@H_301_5@@H_301_5@

MysqL数据库(figure8：UserType被设置为0)@H_301_5@

MysqL数据库通过figure7和figure8,我们知道数据记录还是插入了,只是返回FIEld 'UserType' doesn't have a default value的警告,插入记录的UserType字段都被设置为0('UserType' 为int数据类型).@H_301_5@

MysqL数据库3. 如果我们希望导入的时候一起更新UserType字段的值,这自然很简单了,使用下面的sql脚本就可以解决：
@H_301_5@ 代码如下:
-- 导入4
REPLACE INTO testtable1(UserID,UserType FROM testtable2;

MysqL数据库

@H_301_5@@H_301_5@

MysqL数据库(figure9：一起更新UserType)@H_301_5@

MysqL数据库(三) 导入保留重复数据未指定字段,INSERT INTO ON DUPliCATE KEY UPDATE 的使用@H_301_5@

MysqL数据库把testtable1和testtable2分别回滚到figure2和figure3的状态(使用TruncATE table命名再执行Insert语句),看有什么效果：
@H_301_5@ 代码如下:
-- 导入5
INSERT INTO testtable1(UserID,Username FROM testtable2
ON DUPliCATE KEY UPDATE
testtable1.Username = testtable2.Username;

MysqL数据库

@H_301_5@@H_301_5@

MysqL数据库(figure10：保留UserType值)@H_301_5@

MysqL数据库对比figure2、figure3与figure10,UserID为101的记录：更新了Username的值,保留了UserType的值；但是由于【导入5】中没有指定UserType,所以新插入记录的UserType是为NulL的.
@H_301_5@ 代码如下:
-- 导入6
INSERT INTO testtable1(UserID,UserType FROM testtable2
ON DUPliCATE KEY UPDATE
testtable1.Username = testtable2.Username;

MysqL数据库

@H_301_5@@H_301_5@

MysqL数据库(figure11：保留UserType值)@H_301_5@

MysqL数据库对比figure2、figure3与figure11,只插入testtable2表的UserID,Username字段,但是保留testtable1表的UserType字段.如果发现有重复的记录,做更新 *** 作；在原有记录基础上,更新指定字段内容,其它字段内容保留.@H_301_5@

MysqL数据库(四) 总结@H_301_5@

MysqL数据库当在一个UNIQUE键上插入包含重复值的记录时,默认的insert会报1062错误,MysqL可以通过以上三种不同的方式和你的业务逻辑进行处理.@H_301_5@

MysqL数据库三、参考文献@H_301_5@

MysqL数据库MysqL插入处理重复键值的几种方法@H_301_5@ 总结

以上是内存溢出为你收集整理的MYSQL教程MySQL批量插入遇上唯一索引避免方法全部内容，希望文章能够帮你解决MYSQL教程MySQL批量插入遇上唯一索引避免方法所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。