SQL如何优雅地结合insert和update
博客专区 > Hosee 的博客 > 博客详情
SQL如何优雅地结合insert和update
Hosee 发表于3个月前
SQL如何优雅地结合insert和update
  • 发表于 3个月前
  • 阅读 86
  • 收藏 0
  • 点赞 0
  • 评论 0

腾讯云 新注册用户 域名抢购1元起>>>   

背景

构造1条记录,插入到数据库;当数据库已经存在同一主键的数据,则执行update操作,如果不存在,则执行insert操作。

用update、和insert语句可以满足要求,但是需要先查询数据库中有没有该记录,根据查询结果再判断是执行update还是insert。对于单条语句来说很适合,但是对于多条批量大数据操作时比较浪费性能。

解决方法

第1种解决方式:replace into

replace into 首先尝试插入数据到表中,

1. 如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,然后插入新的数据。

2. 否则,直接插入新数据。

REPLACE INTO `table_name`(`col_name`, ...) VALUES (...);
REPLACE INTO `table_name` (`col_name`, ...) SELECT ...;
REPLACE INTO `table_name` SET `col_name`='value';

第2种解决方式:INSERT ... ON DUPLICATE KEY UPDATE

首先,此语法的前提是表中一定要有个唯一的索引或者主键
具体使用如下:

单条操作

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

这里假设a是主键,当数据库中存在a=1的记录时,将c的值设置为c+1

多条操作

用VALUES关键字

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE b=VALUES(b);

这里假设a是主键,当数据库中存在a=1的记录时,将b的值设置为2,否则插入新数据a=1,b=2,c=3; 当数据库中存在a=4的记录时,将b的值设置为5,否则插入新数据a=4,b=5,c=6

性能对比

在数据库数据量很少的时候,这两种方式都很快,无论是直接的插入还是有冲突时的更新,都不错,但在数据库表的内容数量比较大(如百万级)的时候,两种方式就不太一样了,

首先是直接的插入操作,两种的插入效率都略低,比如直接向表里插入1000条数据(百万级的表(innodb引擎)),二者都差不多需要5,6甚至十几秒。究其原因,我的主机性能是一方面,但在向大数据表批量插入数据的时候,每次的插入都要维护索引的,索引固然可以提高查询的效率,但在更新表尤其是大表的时候,索引就成了一个不得不考虑的问题了。

其次是更新表,这里的更新的时候是带主键值的(因为我是从另一个表获取数据再插入,要求主键不能变)同样直接更新1000条数据,replace的操作要比insert on duplicate的操作低太多太多,当insert瞬间完成(感觉)的时候,replace要7,8s, replace慢的原因我是知道的,在更新数据的时候,要先删除旧的,然后插入新的,在这个过程中,还要重新维护索引,insert on duplicate 的更新操作虽然也会更新数据,但其对主键的索引却不会有改变,也就是说,insert on duplicate 更新对主键索引没有影响.因此对索引的维护成本就低了一些(如果更新的字段不包括主键,那就要另说了)。

Reference:

1. http://blog.csdn.net/jbboy/article/details/46828917

2. https://segmentfault.com/a/1190000002527333

共有 人打赏支持
粉丝 395
博文 126
码字总数 202800
×
Hosee
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: