当前位置： 移动技术网 > IT编程>数据库>Mysql > MySQL批量插入遇上唯一索引避免方法

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

2017年12月12日  | 移动技术网IT编程  | 我要评论

一、背景

以前使用sql server进行表分区的时候就碰到很多关于唯一索引的问题：step8：sql server 当表分区遇上唯一约束，没想到在mysql的分区中一样会遇到这样的问题：mysql表分区实战。

今天我们来了解mysql唯一索引的一些知识：包括如何创建，如何批量插入，还有一些技巧上sql；

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

二、过程

(一) 导入差异数据，忽略重复数据，ignore into的使用

在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);

u1_1062

（figure1：duplicate entry '1' for key 'primary'）

但是在实际的生产环境中，需求往往是需要在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);

u2_table1

（figure2：testtable1记录）

u3_table2

（figure3：testtable2记录）

通过执行上面的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;

u4_unique

（figure4：duplicate entry '101' for key 'ix_userid'）

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值不连续记录

u5_效果

（figure5：ignore效果）

(二) 导入并覆盖重复数据，replace into 的使用

1. 把testtable1和testtable2分别回滚到figure2和figure3的状态（使用truncate table命名再执行insert语句），这个时候再执行下面的sql，看有什么效果：

复制代码 代码如下:

-- 导入3
replace into testtable1(userid,username)
select userid,username from testtable2;

u6_rep

（figure6：replace效果）

从上图figure6中，我们可以看到：userid为101的记录发生了改变，不单username修改了，而且usertype也变为null了。

所以，如果导入中发现了重复的，先删除再插入，如果记录有多个字段，在插入的时候如果有的字段没有赋值，那么新插入的记录这些字段为空（新插入记录的usertype都为null）。

需要注意的是，当你replace的时候，如果被插入的表如果没有指定列，会用null表示，而不是这个表原来的内容。如果插入的内容列和被插入的表列一样，则不会出现null。

2. 如果我们表结构usertype字段不允许为空，而且没有默认值的情况，执行【导入3】会发生什么事情呢？

u7_not null

（figure7：返回警告信息）

u8_0

（figure8：usertype被设置为0）

通过figure7和figure8，我们知道数据记录还是插入了，只是返回field 'usertype' doesn't have a default value的警告，插入记录的usertype字段都被设置为0（'usertype' 为int数据类型）。

3. 如果我们希望导入的时候一起更新usertype字段的值，这自然很简单了，使用下面的sql脚本就可以解决：

复制代码 代码如下:

-- 导入4
replace into testtable1(userid,username,usertype)
select userid,username,usertype from testtable2;

u9_rep

（figure9：一起更新usertype）

(三) 导入保留重复数据未指定字段，insert into on duplicate key update 的使用

把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;

u10_update

（figure10：保留usertype值）

对比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;

u11_update

（figure11：保留usertype值）

对比figure2、figure3与figure11，只插入testtable2表的userid,username字段，但是保留testtable1表的usertype字段。如果发现有重复的记录，做更新操作；在原有记录基础上，更新指定字段内容，其它字段内容保留。

(四) 总结

当在一个unique键上插入包含重复值的记录时，默认的insert会报1062错误，mysql可以通过以上三种不同的方式和你的业务逻辑进行处理。

三、参考文献

mysql插入处理重复键值的几种方法

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网