文档章节

SQL如何优雅地结合insert和update

Hosee
 Hosee
发布于 2017/09/12 12:12
字数 855
阅读 160
收藏 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

© 著作权归作者所有

共有 人打赏支持
Hosee
粉丝 532
博文 132
码字总数 207228
作品 0
杭州
程序员
springboot(六):如何优雅的使用mybatis

这两天启动了一个新项目因为项目组成员一直都使用的是mybatis,虽然个人比较喜欢jpa这种极简的模式,但是为了项目保持统一性技术选型还是定了 mybatis。到网上找了一下关于spring boot和myb...

纯洁的微笑
2016/11/07
5.7K
41
Mysql 原生语句中save or update 的各种写法

Mysql 原生语句中save or update 的各种写法 背景   在平常的开发中,经常碰到这种更新数据的场景:先判断某一数据在库表中是否存在,存在则update,不存在则insert。如果使用Hibernate,它...

Tek_Eternal
2015/02/09
0
0
执行一条sql语句update多条记录实现思路

执行一条sql语句update多条记录实现思路 如果你想更新多行数据,并且每行记录的各字段值都是各不一样,你会怎么办呢?本文以一个示例向大家讲解下如何实现如标题所示的情况,有此需求的朋友可...

bigTreee
2015/07/08
0
0
EasyDB 1.0.1正式版发布,MySQL操作类

EasyDB 1.0.1正式版发布.MySQL操作类 更新内容如下: 支持对insert/update/deldete/select的简化操作; 支持对复杂的insert/update/deldete/select SQL进行链式操作; 链式操作目前支持 fiel...

奋斗de熊猫
2016/01/14
2.9K
15
架构模式数据源模式之:表数据入口(Table Data Gateway)、行数据入口(Row Data Gateway)、活动记录(Active Record)

一:表数据入口(Table Data Gateway) 表数据入口提供了用于访问单个表或者视图(也包含了联表查询)的所有SQL,通常一个表一个类。其它代码通过它来实现对数据库的交互。基于这个特点,表数...

文艺小青年
2017/02/10
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

流量劫持是如何产生的?

流量劫持,这种古老的攻击沉寂了一段时间后,最近又开始闹的沸沸扬扬。众多知名品牌的路由器相继爆出存在安全漏洞,引来国内媒体纷纷报道。只要用户没改默认密码,打开一个网页甚至帖子,路由...

谢思华
10分钟前
0
0
Hadoop Client无法使用maven下载源码

最近在学习hadoop,使用maven的时候想看一下源码的注释,结果IDEA一直提示无法下载 搞得我一度以为maven坏掉了。 但是通过搜索,发现在maven仓库里确实没有源码.... 而2.8.1以及之前的版本是...

Iceberg_XTY
12分钟前
0
0
为什么程序员千万不要重写代码?

你所做的事情,也许暂时看不到成果,但不要灰心或焦虑,你不是没有成长,而是在扎根。 图片来自网络 0 前言 程序员都有一颗工程师的心,所以当他们到一片新的场地想做的第一件事就是,将旧的...

Java小铺
13分钟前
0
0
VUE集成AdminLte

1. 安装需要到插件 npm i admin-lte -Snpm i jquery -Snpm i axios -Snpm i vue-router -S 2. 配置webpack.config.js 2.1 module.exports.module.rules修改字体loader: {test: /\.(p......

Pasenger
52分钟前
0
0
Spring Aop原理之切点表达式解析

在前面的文章(Spring AOP切点表达式详解)中,我们总结了Spring Aop切点表达式的用法,而在上文(Spring Aop原理之Advisor过滤)中我们讲到,切点表达式的解析主要是在PatternParser.parse...

爱宝贝丶
今天
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部