百度123123网址大全,无忧网站优化,网页制作与网站建设自考,邯郸市瑞荣网络科技有限公司一、背景以前使用SQL Server进行表分区的时候就碰到很多关于唯一索引的问题#xff1a;Step8#xff1a;SQL Server 当表分区遇上唯一约束#xff0c;没想到在MySQL的分区中一样会遇到这样的问题#xff1a;MySQL表分区实战。今天我们来了解MySQL唯一索引的一些知识#x…一、背景以前使用SQL Server进行表分区的时候就碰到很多关于唯一索引的问题Step8SQL Server 当表分区遇上唯一约束没想到在MySQL的分区中一样会遇到这样的问题MySQL表分区实战。今天我们来了解MySQL唯一索引的一些知识包括如何创建如何批量插入还有一些技巧上SQL这些问题的根源在什么地方有什么共同点MySQL中也有分区对齐的概念唯一索引是在很多系统中都会出现的要求有什么办法可以避免它对性能的影响有多大二、过程(一) 导入差异数据忽略重复数据IGNORE INTO的使用在MySQL创建表的时候我们通常创建一个表的时候是以一个自增ID值作为主键那么MySQL就会以PRIMARY KEY作为聚集索引键和主键既然是主键那当然是唯一的了所以重复执行下面的插入语句会报1062错误如Figure1所示--创建测试表CREATE TABLEtesttable (IdINT(11) UNSIGNED NOT NULLAUTO_INCREMENT,UserIdINT(11) DEFAULT NULL,UserNameVARCHAR(10) DEFAULT NULL,UserTypeINT(11) DEFAULT NULL,PRIMARY KEY(Id)) ENGINEINNODB DEFAULT CHARSETutf8;--插入测试数据INSERT INTOtesttable(Id,UserId,UserName,UserType)VALUES(1,101,aa,1),(2,102,bbb,2),(3,103,ccc,3);(Figure1Duplicate entry 1 for key PRIMARY)但是在实际的生产环境中需求往往是需要在UserId键值中设置唯一索引今天我就以这个作为示例进行唯一索引的测试--创建测试表1CREATE TABLEtesttable1 (IdINT(11) UNSIGNED NOT NULLAUTO_INCREMENT,UserIdINT(11) DEFAULT NULL,UserNameVARCHAR(10) DEFAULT NULL,UserTypeINT(11) DEFAULT NULL,PRIMARY KEY(Id),UNIQUE KEYIX_UserId (UserId)) ENGINEINNODB DEFAULT CHARSETutf8;--创建测试表2CREATE TABLEtesttable2 (IdINT(11) UNSIGNED NOT NULLAUTO_INCREMENT,UserIdINT(11) DEFAULT NULL,UserNameVARCHAR(10) DEFAULT NULL,UserTypeINT(11) DEFAULT NULL,PRIMARY KEY(Id),UNIQUE KEYIX_UserId (UserId)) ENGINEINNODB DEFAULT CHARSETutf8;--插入测试数据1INSERT INTOtesttable1(Id,UserId,UserName,UserType)VALUES(1,101,aa,1),(2,102,bbb,2),(3,103,ccc,3);--插入测试数据2INSERT INTOtesttable2(Id,UserId,UserName,UserType)VALUES(1,201,aaa,1),(2,202,bbb,2),(3,203,ccc,3),(4,101,xxxx,5);(Figure2testtable1记录)(Figure3testtable2记录)通过执行上面的SQL脚本我们在testtable1和testtable2都创建了唯一索引UNIQUE KEY IX_UserId (UserId)这就说明UserId在testtable1和testtable2表中都是唯一的如果把testtable2的数据批量导入到testtable1如果执行下面【导入1】的SQL就会出现1062的错误导致整个过程会回滚没有达到导入差异数据的目的。--导入1INSERT INTOtesttable1(UserId,UserName,UserType)SELECT UserId,UserName,UserType FROM testtable2;(Figure4Duplicate entry 101 for key IX_UserId)MySQL提供一个关键字IGNORE这个关键字判断每条记录是否存在是否违反饿了表中的唯一索引如果存在就不插入而不存在的记录就会插入。--导入2INSERT IGNORE INTOtesttable1(UserId,UserName,UserType)SELECT UserId,UserName,UserType FROM testtable2;所以执行完【导入2】就会产生Figure5的结果这已经达到了我们的目的了但是你有没发现自增的ID值跳过了一些值这是因为我们之前执行【导入1】失败造成的虽然我们的事务回滚了但是自增ID会出现断层。在SQL Server中也会有这样的问题。扩展阅读简单实用SQL脚本Part查找SQL Server 自增ID值不连续记录(Figure5IGNORE效果)(二) 导入并覆盖重复数据REPLACE INTO 的使用1. 把testtable1和testtable2分别回滚到Figure2和Figure3的状态(使用TRUNCATE TABLE命名再执行Insert语句)这个时候再执行下面的SQL看有什么效果--导入3REPLACE INTOtesttable1(UserId,UserName)SELECT UserId,UserName FROM testtable2;(Figure6REPLACE效果)从上图Figure6中我们可以看到UserId为101的记录发生了改变不单UserName修改了而且UserType也变为NULL了。所以如果导入中发现了重复的先删除再插入如果记录有多个字段在插入的时候如果有的字段没有赋值那么新插入的记录这些字段为空(新插入记录的UserType都为NULL)。需要注意的是当你replace的时候如果被插入的表如果没有指定列会用NULL表示而不是这个表原来的内容。如果插入的内容列和被插入的表列一样则不会出现NULL。2. 如果我们表结构UserType字段不允许为空而且没有默认值的情况执行【导入3】会发生什么事情呢(Figure7返回警告信息)(Figure8UserType被设置为0)通过Figure7和Figure8我们知道数据记录还是插入了只是返回Field UserType doesnt have a default value的警告插入记录的UserType字段都被设置为0(UserType 为int数据类型)。3. 如果我们希望导入的时候一起更新UserType字段的值这自然很简单了使用下面的SQL脚本就可以解决--导入4REPLACE INTOtesttable1(UserId,UserName,UserType)SELECT UserId,UserName,UserType FROM testtable2;(Figure9一起更新UserType)(三) 导入保留重复数据未指定字段INSERT INTO ON DUPLICATE KEY UPDATE的使用把testtable1和testtable2分别回滚到Figure2和Figure3的状态(使用TRUNCATE TABLE命名再执行Insert语句)这个时候再执行下面的SQL看有什么效果--导入5INSERT INTOtesttable1(UserId,UserName)SELECT UserId,UserName FROMtesttable2ON DUPLICATE KEY UPDATEtesttable1.UserName testtable2.UserName;(Figure10保留UserType值)对比Figure2、Figure3与Figure10UserId为101的记录更新了UserName的值保留了UserType的值但是由于【导入5】中没有指定UserType所以新插入记录的UserType是为NULL的。--导入6INSERT INTOtesttable1(UserId,UserName,UserType)SELECT UserId,UserName,UserType FROMtesttable2ON DUPLICATE KEY UPDATEtesttable1.UserName testtable2.UserName;(Figure11保留UserType值)对比Figure2、Figure3与Figure11只插入testtable2表的UserId,UserName字段但是保留testtable1表的UserType字段。如果发现有重复的记录做更新操作在原有记录基础上更新指定字段内容其它字段内容保留。(四) 总结当在一个UNIQUE键上插入包含重复值的记录时默认的insert会报1062错误MYSQL可以通过以上三种不同的方式和你的业务逻辑进行处理。三、参考文献
