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

一、背景

此前使用SQL Server举办表分区的时候就境遇比超多有关独一索引的标题:Step8:SQL Server 当表分区遇上独一限定,没悟出在MySQL的分区中相像会遇上这样的主题材料:MySQL表分区实战。

前些天大家来打听MySQL独一索引的片段知识:包含什么创建,怎样批量布署,还会有一部分技巧上SQL;

这一个题目标来源在怎么地点?有哪些协同点?MySQL中也许有分区对齐的概念?独一索引是在好些个系统中都会并发的须要,有怎么着格局能够制止?它对品质的影响有多大?

二、过程

(一卡塔尔国 导入差别数据,忽视重复数据,IGNORE INTO的运用

在MySQL创造表的时候,大家日常成立叁个表的时候是以多少个自增ID值作为主键,那么MySQL就能够以P君越IMAOdysseyY 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 CHAENCORESET=utf8; -- 插入测验数据 INSERT INTO testtable(Id,UserId,UserName,UserType卡塔尔VALUES(1,101,'aa',1卡塔尔(قطر‎,(2,102,'bbb',2State of Qatar,(3,103,'ccc',3卡塔尔(قطر‎;

不过在其实的生育碰到中,必要往往是急需在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 CHAGL450SET=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 CHALacrosseSET=utf8; -- 插入测量检验数据1 INSERT INTO testtable1(Id,UserId,UserName,UserTypeState of QatarVALUES(1,101,'aa',1卡塔尔国,(2,102,'bbb',2卡塔尔国,(3,103,'ccc',3卡塔尔(قطر‎; -- 插入测量试验数据2 INSERT INTO testtable2(Id,UserId,UserName,UserType卡塔尔国VALUES(1,201,'aaa',1State of Qatar,(2,202,'bbb',2卡塔尔国,(3,203,'ccc',3卡塔尔,(4,101,'xxxx',5State of Qatar;

由此实践上面包车型大巴SQL脚本,大家在testtable1和testtable2都创立了独一索引:UNIQUE KEY `IX_UserId` (`UserId`卡塔尔(قطر‎,那就印证UserId在testtable1和testtable2表中都以并世无双的,若是把testtable2的多少批量导入到testtable1,假使实践上边包车型客车SQL,就能够合世1062的荒诞,招致整个经过会回滚,未有直达导入差别数据的目标。复制代码 代码如下:INSERT INTO testtable1(UserId,UserName,UserTypeState of Qatar SELECT UserId,UserName,UserType FROM testtable2;

MySQL提供二个重点字:IGNORE,那些首要字剖断每条记下是不是留存,是或不是违背饿了表中的唯一索引,假如存在就不插入,而不设有的笔录就能够插入。复制代码 代码如下:-- 导入2 INSERT IGNORE INTO testtable1(UserId,UserName,UserTypeState of Qatar SELECT UserId,UserName,UserType FROM testtable2;所以举办完,就能爆发Figure5的结果,这曾经完结了我们的目标了,但是你有没发掘自增的ID值跳过了一部分值,那是因为我们前边施行破产变成的,纵然我们的业务回滚了,不过自增ID会并发断层。在SQL Server中也许有这么的难点。扩充阅读:轻便实用SQL脚本Part:查找SQL Server 自增ID值不总是记录

(二卡塔尔 导入并覆盖再次数据,REPLACE INTO 的利用

1. 把testtable1和testtable2分别回滚到Figure2和Figure3的情况,此时再施行上面包车型大巴SQL,看有何功用:复制代码 代码如下:-- 导入3 REPLACE INTO testtable1(UserId,UserNameState of Qatar SELECT UserId,UserName FROM testtable2;

从上海教室Figure6中,大家得以看出:UserId为101的笔录产生了改换,不单UserName更正了,並且UserType也变为NULL了。

之所以,如果导入中发掘了再一次的,先删除再插入,如若记录有七个字段,在插入的时候要是有个别字段未有赋值,那么新插入的记录那个字段为空。

内需专一的是,当您replace的时候,假使被插入的表若无内定列,会用NULL表示,并不是以此表原来的源委。若是插入的内容列和被插入的表列同样,则不晤面世NULL。

2. 假使我们表布局UserType字段差异意为空,何况还未有私下认可值的情事,实践会时有产生怎么样工作吗?

通过Figure7和Figure8,大家精通多少记录照旧插入了,只是重临Field'UserType' doesn't have a default value的告诫,插入记录的UserType字段都被安装为0。

3. 只要大家目的在于导入的时候一同更新UserType字段的值,那当然相当轻便了,使用上边包车型客车SQL脚本就足以消除:复制代码 代码如下:-- 导入4 REPLACE INTO testtable1(UserId,UserName,UserType卡塔尔(قطر‎ SELECT UserId,UserName,UserType FROM testtable2;

(三卡塔尔 导入保留重复数据未钦赐字段,INSERT INTO ON DUPLICATE KEY UPDATE 的接纳

把testtable1和testtable2分别回滚到Figure2和Figure3的情形,这时候再进行上面包车型地铁SQL,看有啥效率:复制代码 代码如下:-- 导入5 INSERT INTO testtable1(UserId,UserName卡塔尔 SELECT UserId,UserName FROM testtable2 ON DUPLICATE KEY UPDATE testtable1.UserName = testtable2.UserName;

相比较Figure2、Figure3与Figure10,UserId为101的笔录:更新了UserName的值,保留了UserType的值;可是由于中并未有内定UserType,所以新插入记录的UserType是为NULL的。复制代码 代码如下:-- 导入6 INSERT INTO testtable1(UserId,UserName,UserType卡塔尔(قطر‎ SELECT UserId,UserName,UserType FROM testtable2 ON DUPLICATE KEY UPDATE testtable1.UserName = testtable2.UserName;

相比较Figure2、Figure3与Figure11,只插入testtable2表的UserId,UserName字段,不过保留testtable1表的UserType字段。借使发掘成重复的笔录,做创新操作;在原始记录底蕴上,更新钦点字段内容,别的字段内容保留。

(四) 总结

当在三个UNIQUE键上插入富含重复值的笔录时,私下认可的insert会报1062荒谬,MYSQL能够通过上述两种不一样的办法和您的业务逻辑进行拍卖。

三、参谋文献

MYSQL插入管理重复键值的三种格局

本文由新浦京娱乐站_新浦京娱乐场官网_新浦京娱乐发布于技术创新,转载请注明出处:MySQL批量插入遇上唯一索引避免方法

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。