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数据库 MYSQL数据库(Figure1:Duplicate entry '1' for key 'PRIMARY') MYSQL数据库但是在实际的生产环境中,需求往往是需要在UserId键值中设置唯一索引,今天我就以这个作为示例,进行唯一索引的测试: -- 创建测试表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数据库 MYSQL数据库(Figure2:testtable1记录) MYSQL数据库 MYSQL数据库(Figure3:testtable2记录) MYSQL数据库通过执行上面的SQL脚本,我们在testtable1和testtable2都创建了唯一索引:UNIQUE KEY `IX_UserId` (`UserId`),这就说明UserId在testtable1和testtable2表中都是唯一的,如果把testtable2的数据批量导入到testtable1,如果执行下面【导入1】的SQL,就会出现1062的错误,导致整个过程会回滚,没有达到导入差异数据的目的. INSERT INTO testtable1(UserId,UserType) SELECT UserId,UserType FROM testtable2; MYSQL数据库 MYSQL数据库(Figure4:Duplicate entry '101' for key 'IX_UserId') MYSQL数据库MySQL提供一个关键字:IGNORE,这个关键字判断每条记录是否存在,是否违反饿了表中的唯一索引,如果存在就不插入,而不存在的记录就会插入. -- 导入2 INSERT IGNORE INTO testtable1(UserId,UserType FROM testtable2; 所以执行完【导入2】,就会产生Figure5的结果,这已经达到了我们的目的了,但是你有没发现自增的ID值跳过了一些值,这是因为我们之前执行【导入1】失败造成的,虽然我们的事务回滚了,但是自增ID会出现断层.在SQL Server中也会有这样的问题.扩展阅读:简单实用SQL脚本Part:查找SQL Server 自增ID值不连续记录 MYSQL数据库 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; MYSQL数据库(Figure6:REPLACE效果) MYSQL数据库从上图Figure6中,我们可以看到:UserId为101的记录发生了改变,不单UserName修改了,而且UserType也变为NULL了. MYSQL数据库所以,如果导入中发现了重复的,先删除再插入,如果记录有多个字段,在插入的时候如果有的字段没有赋值,那么新插入的记录这些字段为空(新插入记录的UserType都为NULL). MYSQL数据库需要注意的是,当你replace的时候,如果被插入的表如果没有指定列,会用NULL表示,而不是这个表原来的内容.如果插入的内容列和被插入的表列一样,则不会出现NULL. MYSQL数据库2. 如果我们表结构UserType字段不允许为空,而且没有默认值的情况,执行【导入3】会发生什么事情呢? MYSQL数据库 MYSQL数据库(Figure7:返回警告信息) MYSQL数据库 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,UserType FROM testtable2; MYSQL数据库 MYSQL数据库(Figure9:一起更新UserType) MYSQL数据库(三) 导入保留重复数据未指定字段,INSERT INTO ON DUPLICATE KEY UPDATE 的使用 MYSQL数据库把testtable1和testtable2分别回滚到Figure2和Figure3的状态(使用TRUNCATE TABLE命名再执行Insert语句),看有什么效果: -- 导入5 INSERT INTO testtable1(UserId,UserName FROM testtable2 ON DUPLICATE KEY UPDATE testtable1.UserName = testtable2.UserName; MYSQL数据库 MYSQL数据库(Figure10:保留UserType值) MYSQL数据库对比Figure2、Figure3与Figure10,UserId为101的记录:更新了UserName的值,保留了UserType的值;但是由于【导入5】中没有指定UserType,所以新插入记录的UserType是为NULL的. -- 导入6 INSERT INTO testtable1(UserId,UserType FROM testtable2 ON DUPLICATE KEY UPDATE testtable1.UserName = testtable2.UserName; MYSQL数据库 MYSQL数据库(Figure11:保留UserType值) MYSQL数据库对比Figure2、Figure3与Figure11,只插入testtable2表的UserId,UserName字段,但是保留testtable1表的UserType字段.如果发现有重复的记录,做更新操作;在原有记录基础上,更新指定字段内容,其它字段内容保留. MYSQL数据库(四) 总结 MYSQL数据库当在一个UNIQUE键上插入包含重复值的记录时,默认的insert会报1062错误,MYSQL可以通过以上三种不同的方式和你的业务逻辑进行处理. MYSQL数据库三、参考文献 MYSQL数据库MYSQL插入处理重复键值的几种方法 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |