Mysql插入重复行数据处理方法

51 阅读 0 评论 34 点赞

我是靠谱客的博主微笑乌冬面，最近开发中收集的这篇文章主要介绍Mysql插入重复行数据处理方法，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

1、需求

在mysql更新数据时，我们经常希望如果数据库中存在该条记录，就只对其更新相关字段属性（如时间）或者不做处理，如果不存在记录就直接插入。
2、常规思维

通过查询是否包含该条记录，存在则更新否则就插入
该方法需要每条查询判断然后再更新或插入执行sql性能很低，容易造成阻塞。
3、mysql处理方法

以下方法同时也支持批量处理：
1）对于主键和唯一索引，可以用IGNORE关键字，遇到重复记录会直接忽略插入记录，返回0。
CREATE TABLE test (
id int NOT NULL,
name VARCHAR(50) NOT NULL,
age int DEFAULT NULL,
PRIMARY KEY (id,name)
);
insert into test(id,name,age) values (1,'nancy',29);
如:
insert ignore into test(id,name,age) values (1,'nancy',29);
2）replace关键字：REPLACE的运行与INSERT很相像,但是如果旧记录与新记录有相同的值，则在新记录被插入之前，旧记录被删除。REPLACE返回受影响的行数。
replace into test(id,name,age) values (1,'nancy',29)

3)ON DUPLICATE KEY UPDATE：遇到重复的记录则更新指定的字段。如果行作为新记录被插入，则受影响行的值为1；如果表中原有的记录被更新，则受影响行的值为2。
insert into test(id,name,age) values (1,'nancy',29) ON DUPLICATE KEY UPDATE id=100,age=33;

在编码中一般使用方式如下：
INSERT INTO mytable(id,pid,ele,anim)
VALUES (?,?,?,?),(?,?,?,?),(?,?,?,?)
ON DUPLICATE KEY UPDATE pid=VALUES(pid),ele=VALUES(ele)
//pid=VALUES(pid),ele=VALUES(ele) 表示出现在values中某列的id字段值与表中已有id字段值重复时，会更新对应记录的这两个字段

//还可以指定其它值或进行运算：pid=pid+1，ele=ele-1

它不但对唯一主键有效，对复合主键同样有效，复合主键设置：

ALTER TABLE mytable ADD(CONSTRAINT PRIMARY KEY(id,pid));
不过ON DUPLICATE KEY UPDATE只是MySQL的特有语法，并不是SQL标准语法，不要乱用。

4、参考资料

https://my.oschina.net/codespring/blog/411889

https://my.oschina.net/glenxu/blog/1976114