文档章节

SQL如何优雅地结合insert和update

Hosee
 Hosee
发布于 2017/09/12 12:12
字数 855
阅读 147
收藏 1
点赞 0
评论 0

背景

构造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
粉丝 510
博文 132
码字总数 207228
作品 0
杭州
程序员
Mysql 原生语句中save or update 的各种写法

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

Tek_Eternal ⋅ 2015/02/09 ⋅ 0

springboot(六):如何优雅的使用mybatis

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

纯洁的微笑 ⋅ 2016/11/07 ⋅ 41

优雅的拼装SQL

insert update selectByPk(hibernate传递参数方式)

jackzlz ⋅ 2015/08/05 ⋅ 0

执行一条sql语句update多条记录实现思路

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

bigTreee ⋅ 2015/07/08 ⋅ 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 ⋅ 15

PHP 数据库操作类--EasyDB

EasyDB是一个基于PDO的数据库操作类,它使用了PDO的精髓--预编译--的思想,从而在根源上杜绝SQL注入的危险。 现在网上的大多数据库封装类,甚至一些知名的框架都仅仅只是包装一下数据库的操作...

奋斗de熊猫 ⋅ 2015/06/29 ⋅ 2

架构模式数据源模式之:表数据入口(Table Data Gateway)、行数据入口(Row Data Gateway)、活动记录(Active Record)

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

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

MySQL Jdbc驱动的rewriteBatchedStatements参数使batch生效

MySQL Jdbc驱动在默认情况下会无视executeBatch()语句,把我们期望批量执行的一组sql语句拆散,一条一条地发给MySQL数据库,直接造成较低的性能。 只有把rewriteBatchedStatements参数置为t...

如风达 ⋅ 2016/11/08 ⋅ 0

Oracle之FORALL与BULK COLLECT简介

在上篇文章Oracle大数据的导入与更新方案中介绍了导入超大数据量的操作,而要批量更新超大数据量的操作时就需要用到本文所要介绍的FORALL与BULK COLLECT语句。 1 PL/SQL块的执行过程 当PL/SQ...

landy8530 ⋅ 03/17 ⋅ 0

(2)预期+思考【利用objective-c的runtime特性,结合FMDB实现轻量级的ORM】

版权声明:本文为博主原创文章,未经博主允许不得转载。 (本ORM的源码已经上传到github上 (https://github.com/helloclq/BCSqliteORMFMDB),大家可以下载测试,如发现什么问题或意见,欢迎大...

辉兔狼 ⋅ 2015/09/10 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

如何使用serverchan微信推送告警

之前实现推送告警信息到微信的方法有如下几种: 1、通过企业公众号实现----收费: 2、通过QQ邮箱,在微信平台上开启收到邮件进行提醒; 3、第三方告警平台API,一般也是收费的; 不过最近看文...

问题终结者 ⋅ 29分钟前 ⋅ 0

TCP的RPC

RPC就是远程方法调用(Remote Process Call ),包含了客户端和服务端,涉及了对象的序列化传输。 1.服务端启动,注册远程调用的类2.客户端发送请求信息包含类、方法、参数的一些信息、序列化传...

Cobbage ⋅ 50分钟前 ⋅ 0

IOS-UI UI初步代码布局添加事件

ISO开发界面,UI是必须学习的一部分,其实很早之前想学来了,一直没有沉下心来学习。看到IOS的代码风格和布局就别扭的不行,跟java代码和android布局比较显得不是那么方便,所以一直到现在。...

京一 ⋅ 今天 ⋅ 0

浅谈OpenDaylight的二次开发

OpenDaylight作为一款开源SDN网络控制器,依托于强大的社区支持以及功能特性,成为了目前主流的SDN网络控制器开发平台。在比较稳定的OpenDaylight Helium版本中,已经为开发者提供了大量的网...

wangxuwei ⋅ 今天 ⋅ 0

API 开发中可选择传递 token 接口遇到的一个坑

在做 API 开发时,不可避免会涉及到登录验证,我使用的是jwt-auth 在登录中会经常遇到一个token过期的问题,在config/jwt.php默认设置中,这个过期时间是一个小时,不过为了安全也可以设置更...

等月人 ⋅ 今天 ⋅ 0

Java NIO之文件处理

程序要操作本地操作系统的一个文件,可以分为以下三个部分: 对文件位置的操作 对文件的操作 对文件内容的操作 其中,对文件内容的操作在 Java NIO之Channel 中已经有了介绍,通过FileChann...

士别三日 ⋅ 今天 ⋅ 0

Maven的pom.xml配置文件详解

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.......

小海bug ⋅ 今天 ⋅ 0

解决httpclient超时设置不生效的问题

最近公司有项目需要通过http调用第三方服务,且第三方服务偶有超时,故需要设置一定的超时时间防止不响应的情况出现。 初始设置如下: [java] view plain copy //超时设置 RequestConfig re...

Mr_Tea伯奕 ⋅ 今天 ⋅ 0

过滤器Filter和拦截器HandlerInterceptor

过滤器 依赖于servlet容器。在实现上基于函数回调,可以对几乎所有请求进行过滤,但是缺点是一个过滤器实例只能在容器初始化时调用一次。使用过滤器的目的是用来做一些过滤操作,获取我们想要...

hutaishi ⋅ 今天 ⋅ 0

Redis入门详解(转)

Redis入门详解 Redis简介 Redis安装 Redis配置 Redis数据类型 Redis功能 持久化 主从复制 事务支持 发布订阅 管道 虚拟内存 Redis性能 Redis部署 Redis应用场景 Redis总结 Redis简介: Redi...

xiaoyaoyoufang ⋅ 今天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部