SQL如何优雅地结合insert和update

最新推荐文章于 2022-07-08 13:38:16 发布

weixin_34101784

最新推荐文章于 2022-07-08 13:38:16 发布

阅读量1.3k

点赞数 1

原文链接：https://my.oschina.net/hosee/blog/1535782

版权

为什么80%的码农都做不了架构师？>>>

背景

构造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引擎))，二者都差不多需要５，６甚至十几秒。究其原因，我的主机性能是一方面，但在向大数据表批量插入数据的时候，每次的插入都要维护索引的，索引固然可以提高查询的效率，但在更新表尤其是大表的时候，索引就成了一个不得不考虑的问题了。

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