文档章节

MySQL当批量插入遇上唯一索引

雍雍_yoyo
 雍雍_yoyo
发布于 2013/12/26 10:29
字数 1574
阅读 119
收藏 14
一、 背景

以前使用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的错误,导致整个过程会回滚,没有达到导入差异数据的目的。

-- 导入1 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与Figure10UserId为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可以通过以上三种不同的方式和你的业务逻辑进行处理。


本文转载自:http://www.cnblogs.com/gaizai/archive/2013/01/04/2843909.html

共有 人打赏支持
雍雍_yoyo
粉丝 11
博文 139
码字总数 52609
作品 0
大连
私信 提问
MySQL insert or update SQL 示例

建表 创建一个合适的表,表中必须有唯一索引,因为更新的时候就依据唯一索引更新的 编写sql测试 结合两个sql可以得出结论:ON DUPLICATE KEY UPDATE之后的字段少了UNIQUE INDEX中涉及的两个字...

Listen_ing
2016/05/25
59
0
MySQL的InsertOrUpdate语法

MySQL的插入语法提供了类似insertOrUpdate的语法,这种方式大部分存储系统都有类似的机制比如在Solr或者ElasticSearch中,如果主键一样的就更新,不一样就添加,只不过在数据库里可以是主键单...

九劫散仙
2016/08/04
112
0
mysql大量数据插入探讨(量变引起质变)

分类:见Visio图 关于大量数据导入是应注意以下几点: 分批导入,导入一批后最后提交(commit),可以使用jdbc的(executeBatch)批量处理但是注意它的最大上限,否则只会执行一部分sql语句,超...

soul_mate
2014/04/27
0
0
程序员基础 MySQL数据库开发必备常识

MySQL可以说是程序员应用最多的数据库,下面笔者为大家分享MySQL数据库开发当中的一些常识,存储引擎的选择,索引的设计及使用和大批量插入时SQL语句的优化。希望能对大家有帮助。 存储引擎的...

五味格子
2011/05/11
0
0
MySql5.6性能优化学习笔记

目标  了解什么是优化  掌握优化查询的方法  掌握优化数据库结构的方法  掌握优化MySQL服务器的方法 什么是优化?  合理安排资源、调整系统参数使MySQL运行更快、更节省资源。  ...

知止内明
06/28
0
0

没有更多内容

加载失败,请刷新页面

加载更多

springboot中filter的用法

一、在spring的应用中我们存在两种过滤的用法,一种是拦截器、另外一种当然是过滤器。我们这里介绍过滤器在springboot的用法,在springmvc中的用法基本上一样,只是配置上面有点区别。 二、f...

xiaomin0322
11分钟前
3
0
java项目修改了更换了jdk版本报错进行修改

java项目原来用的是1.8版本的,改成1.7版本后,项目会报错,要进行的修改是 然后是clean一下项目,然后是选中项目的buildpath,然后是configurebuildpath,然后是看jdk是否进行修改...

myAll_myAll
23分钟前
3
0
Gartner 2018 数据库系列报告发布 巨杉数据库连续两年入选

近期,Gartner陆续发布了2018年的数据库系列报告,包括《数据库魔力象限》《数据库核心能力》以及《数据库推荐报告》。其中,SequoiaDB巨杉数据库作为业界领先的金融级分布式交易型数据库产品...

巨杉数据库
25分钟前
0
0
Navicat闲置一段时间卡死问题的解决

先关闭连接,再右键点击所需要设置的链接,进入编辑连接,进入高级项,勾选保持连续间隔(秒):时间设置短一些,比如30秒,完成!!

joyStalker
26分钟前
0
0
理解Java中的弱引用(Weak Reference)

1. What——什么是弱引用? Java中的弱引用具体指的是java.lang.ref.WeakReference<T>类,我们首先来看一下官方文档对它做的说明: 弱引用对象的存在不会阻止它所指向的对象变被垃圾回收器回...

绝地逢生
26分钟前
0
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部