文档章节

mysql忽略主键冲突、避免重复插入的几种方式

时光流转
 时光流转
发布于 2017/08/31 15:28
字数 1179
阅读 8
收藏 0

本文转载自:http://www.2cto.com/database/201308/234179.html

 

方案一:使用 ignore 关键字

方案二:使用 replace into

方案三:ON DUPLICATE KEY UPDATE

 

方案一:使用 ignore 关键字

如果是用主键primary或者唯一索引unique区分了记录的唯一性,避免重复插入记录可以使用:

insert ignore into table_name(email,phone,user_id) values('test9@163.com','99999','9999')

,这样当有重复记录就会忽略,执行后返回数字0,还有个应用就是复制表,避免重复记录:

insert ignore into table(name) select  name from table2

 

方案二:使用 replace into

replace的语法格式为:

1. replace into table_name(col_name, ...) values(...)

2. replace into table_name(col_name, ...) select ...

3. replace into table_name set col_name=value, ...

算法说明:

REPLACE的运行与INSERT很相像,但是如果旧记录与新记录有相同的值,则在新记录被插入之前,旧记录被删除,即:

1.    尝试把新行插入到表中

2.    当因为对于主键或唯一关键字出现重复关键字错误而造成插入失败时:

 从表中删除含有重复关键字值的冲突行

再次尝试把新行插入到表中

旧记录与新记录有相同的值的判断标准就是:表有一个PRIMARY KEY或UNIQUE索引,否则,使用一个REPLACE语句没有意义

。该语句会与INSERT相同,因为没有索引被用于确定是否新行复制了其它的行。

返回值:

REPLACE语句会返回一个数,来指示受影响的行的数目。该数是被删除和被插入的行数的和。

受影响的行数可以容易地确定是否REPLACE只添加了一行,或者是否REPLACE也替换了其它行:检查该数是否为1(添加)或更大(替换)。

示例:

eg:(phone字段为唯一索引)

replace  into table_name(email,phone,user_id) values('test569','99999','123')

另外:在 SQL Server 中可以这样处理:

if not exists (select phone from t where phone= '1')

 insert into t(phone, update_time) values('1', getdate())

else

 update t set update_time = getdate() where phone= '1'

 

方案三:ON DUPLICATE KEY UPDATE

如‍上所写,你也可以在INSERT INTO.....后面加上 ON DUPLICATE KEY UPDATE方法来实现。

如果您指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引

或PRIMARY KEY中出现重复值,

则执行旧行UPDATE。例如,如果列a被定义为UNIQUE,并且包含值1,则以下两个语句具有相同的效果:

mysql>INSERT INTO table (a,b,c) VALUES (1,2,3)

  ->ON DUPLICATE KEY UPDATE c=c+1;

mysql>UPDATE table SET c=c+1 WHERE a=1;

如果行作为新记录被插入,则受影响行的值为1;如果原有的记录被更新,则受影响行的值为2。

注释:如果列b也是唯一列,则INSERT与此UPDATE语句相当:

mysql> UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

如果a=1 OR b=2与多个行向匹配,则只有一个行被更新。通常,您应该尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY子句。

您可以在UPDATE子句中使用VALUES(col_name)函数从INSERT...UPDATE语句的INSERT部分引用列值。

换句话说,如果没有发生重复关键字冲突,则UPDATE子句中的VALUES(col_name)可以引用被插入的

col_name的值。本函数特别适用于多行插入。VALUES()函数只在INSERT...UPDATE语句中有意义,其它时候

会返回NULL。

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)

-> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

本语句与以下两个语句作用相同:

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)

-> ON DUPLICATE KEY UPDATE c=3;

mysql> INSERT INTO table (a,b,c) VALUES (4,5,6)

-> ON DUPLICATE KEY UPDATE c=9;

当您使用ON DUPLICATE KEY UPDATE时,DELAYED选项被忽略。

示例: 这个例子是我在实际项目中用到的:是将一个表的数据导入到另外一个表中,数据的重复性就得考虑(如下)。

唯一索引为:email

INSERT INTO table_name1(title,first_name,last_name,email,phone,user_id

,role_id,status,campaign_id)

SELECT '','','',table_name2.email,table_name2.phone,NULL,NULL,'pending',29

FROM table_name2

WHERE table_name2.status = 1

ON DUPLICATE KEY UPDATE table_name1.status = 'pending'

语句的关键地方,都已高亮出来~

再贴一个例子:

insert into class select * from class1

ON DUPLICATE KEY UPDATE class.course = class1.course

其它关键:DELAYED  做为快速插入,并不是很关心失效性,提高插入性能。

IGNORE  只关注主键对应记录是不存在,无则添加,有则忽略。

特别说明:在MYSQL中UNIQUE 索引将会对null字段失效,也就是说(a字段上建立唯一索引):

insert into test(a) values(null)

insert into test(a) values(null)

是可以重复插入的(联合唯一索引也一样)。

本文转载自:http://www.2cto.com/database/201308/234179.html

时光流转
粉丝 10
博文 77
码字总数 15293
作品 0
私信 提问
MASQL 忽略主键冲突、避免重复插入的几种方式

mysql忽略主键冲突、避免重复插入的几种方式 方案一:使用 ignore 关键字 方案二:使用 replace into 方案三:ON DUPLICATE KEY UPDATE 方案一:使用 ignore 关键字 如果是用主键primary或者...

不停息的脚步
2015/08/12
145
0
MySql避免重复插入记录的几种方法

本文章来给大家提供三种在mysql中避免重复插入记录方法,主要是讲到了ignore,Replace,ON DUPLICATE KEY UPDATE三种方法,有需要的朋友可以参考一下 方案一:使用ignore关键字 如果是用主键p...

凯文加内特
2016/01/09
222
0
MySql避免重复插入记录的几种方法

本文章来给大家提供三种在mysql中避免重复插入记录方法,主要是讲到了ignore,Replace,ON DUPLICATE KEY UPDATE三种方法,有需要的朋友可以参考一下 方案一:使用ignore关键字 如果是用主键p...

messi_10
2016/01/11
75
0
mysql存在则更新,不存在则插入

INSERT INTO ON DUPLICATE KEY UPDATE 与 REPLACE INTO,两个命令可以处理重复键值问题,在实际上它之间有什么区别呢? 前提条件是这个表必须有一个唯一索引或主键。 unique 1、REPLACE发现重...

LCZ777
2015/09/22
5.6K
0
180710-MySql插入唯一键冲突的三种可选方式

MySql插入时唯一键冲突的几种处理方式 MySql插入一条记录,结果提示主键冲突,怎么办? 批量插入数据时,发现插入的这批数据中,有某些记录存在唯一键冲突,一个一个跳出来就比较麻烦了,有什...

小灰灰Blog
2018/07/10
18
0

没有更多内容

加载失败,请刷新页面

加载更多

好程序员Java教程分享Zookeeper基本原理与运用场景

好程序员Java教程分享Zookeeper基本原理与运用场景一、什么是Zookeeper? zookeeper是一个分布式的一致性协调服务。 换句话说,也可以把zookeeper看成一个小型的分布式文件系统。但是和FastD...

好程序员官网
13分钟前
3
0
mysql表情符

1 修改表字段为utf8md4 ALTER table property_info MODIFY `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL 2 MySQL数据库服务器配置文件mysqld.cn......

干死it
32分钟前
2
0
正则表达式的基本语法

本文摘自LTP.NET知识库。 正则表达式的形式一般如下: /love/ 其中位于“/”定界符之间的部分就是将要在目标对象中进行匹配的模式。 用户只要把希望查找匹配对象的模式内容放入“/”定界符之...

木庄
34分钟前
3
0
java 框架有哪些?

十大常用框架: 一、SpringMVC 二、Spring 三、Mybatis 四、Dubbo 五、Maven 六、RabbitMQ 七、Log4j 八、Ehcache 九、Redis 十、Shiro 延展阅读: 一、SpringMVC Spring Web MVC是一种基于J...

java框架开发者
35分钟前
6
0
细谈Mysql事务

文章原创于公众号:程序猿周先森。本平台不定时更新,喜欢我的文章,欢迎关注我的微信公众号。 上一篇着重谈到了MySQL锁的概念,里面谈到了事务的概念,其实大部分开发者对于事务肯定不陌生,...

程序猿周先森
43分钟前
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部