文档章节

Mysql 原生语句中save or update 的各种写法

Tek_Eternal
 Tek_Eternal
发布于 2015/02/09 15:23
字数 1645
阅读 1872
收藏 13

Mysql 原生语句中save or update 的各种写法

##背景   在平常的开发中,经常碰到这种更新数据的场景:先判断某一数据在库表中是否存在,存在则update,不存在则insert。 如果使用Hibernate,它自带saverOrUpdate方法,用起来很方便,但如使用原生sql语句呢?   新手最常见的写法是,先通过select语句查询记录是否存在,存在则使用update语句更新,不存在则使用insert语句插入。 但是这样做明显不够优雅,存在几个问题:

  • 为了执行一次更新操作,却在程序中使用了两次sql查询语句,在系统负载比较大的情况下,性能还是会有影响的。
  • 代码中存在if else语句,明明干了一件事,代码却很长。码农都是懒人,能把事情简单做的为啥要复杂做呢:)。

##那么问题来了,如何优雅的用sql语句实现saverOrUpdate?   最近工作上也碰到类似更新数据的问题,写多了也开始觉得烦。记得Oracle下有Merge的写法,就google一下mysql的类似实现,整理如下:

###数据不存在则插入,存在则无操作    在insert语句中使用ignore关键字实现数据不存在则插入,存在则无操作。它的实现逻辑是,当插入语句出现主键冲突,或者唯一键冲突时,不抛出错误,直接忽略这条插入语句。官网上的相关介绍如下:

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs. Ignored errors may generate warnings instead, although duplicate-key errors do not.

Mysql官方文档中提供标准的语法:

INSERT  IGNORE
    INTO tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
或者  
INSERT IGNORE
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    SELECT ...

可见除了多了个IGNORE关键字以外,跟一般INSERT语句并无区别。

####举个栗子: 1.建一张测试用的表

CREATE TABLE `test_tab` (
  `name` varchar(64) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.插入一条数据

insert into `test_tab` (`name`,`age`) values ('zhangsan',24) 

当前test_tab表的数据为: name|age :----|:--- zhangsan|24 3.再执行一次步骤2的插入语句,则会报异常:

[Err] 1062 - Duplicate entry 'zhangsan' for key 'PRIMARY'

4.对步骤2的insert语句增加ignore关键字,则不会报异常,已存在的数据也不会被更新。

insert IGNORE into `test_tab` (`name`,`age`) values ('zhangsan',24) ;
------
语句执行情况:
受影响的行: 0
时间: 0.000s

当前test_tab表的数据为: name|age :----|:--- zhangsan|24

###不存在则插入,存在则更新,其一(使用DUPLICATE KEY UPDATE关键字)    在insert语句中使用ON DUPLICATE KEY UPDATE关键字实现数据不存在则插入,存在则更新的操作。判断数据重复的逻辑依然是主键冲突或者唯一键冲突。 官网上的相关介绍如下:

if you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. The affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values.

Mysql官方文档中提供标准的语法:

INSERT 
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

或者:

INSERT 
    [INTO] tbl_name
    [PARTITION (partition_name,...)]
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

或者:
INSERT 
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

可见,还是原来insert语句的写法。

####举个栗子: 1.使用刚才新建的test_tab表,此时表中的数据如下: name|age :----|:--- zhangsan|24

2.使用主键相同的insert语句,仍然会duplicate key错误

insert into `test_tab` (`name`,`age`) values ('zhangsan',50) ;
------------
[Err] 1062 - Duplicate entry 'zhangsan' for key 'PRIMARY'

3.对刚才的insert语句添加 on duplicate key update ... 关键字:

insert into `test_tab` (`name`,`age`) values ('zhangsan',50)  
ON DUPLICATE KEY UPDATE `age`=50 ;
------------
受影响的行: 2
时间: 0.025s

4.此时主键为'zhangsan'的数据,age字段已被更新: name|age :----|:--- zhangsan|50

5.当然,如果主键不冲突,效果跟一般插入语句是一样的:

insert into `test_tab` (`name`,`age`) values ('lisi',30)  
ON DUPLICATE KEY UPDATE `age`=30 ;
------------
受影响的行: 1
时间: 0.009s
name age
zhangsan 50
lisi 30

###不存在则插入,存在则更新,其二(使用replace语句实现)    save or update 在mysql中还有另一种实现,即replace into语句,它用起来有点像Oracle的Merge。判断数据重复的逻辑依然是主键或者唯一键冲突。Mysql官方文档中提供标准的语法:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
或:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    SET col_name={expr | DEFAULT}, ...
或:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name,...)]  
    [(col_name,...)]
    SELECT ...

####举个栗子: 1.仍然使用上面的test_tab表的数据,此时数据如下 name|age :----|:--- zhangsan|50 lisi|30

2.使用一般的insert语句插入name=zhangsan的数据,报主键冲突。但是换成replace into...语句则没问题:

replace into `test_tab` (`name`,`age`) values ('zhangsan',30) ;
------------
受影响的行: 2
时间: 0.009s

3.结果如下: name|age :----|:--- zhangsan|30 lisi|30

  对于操作结果来说,很像是save or update,但是实现方式与INSERT的“DUPLICATE KEY UPDATE”关键字不同。当使用replace into语句时,对于重复的数据,是直接删除,然后再插入新数据的。所以它的更新其实不是update,而是delete->insert。大多数情况下,使用replace into完成更新操作并无问题,但是有一种场景必须特别注意:

  • 当被更新的表,存在insert,update,和delete触发器时,使用replace语句必须特别小心。因为按照业务逻辑,更新完数据后,应该触发update触发器,但是使用replace语句的话,会触发delete和insert触发器,如果update触发器有一些特殊操作(比如记录操作日志)的话,使用replace会导致业务逻辑混乱。

所以当被更新表存在触发器的场景时,使用INSERT的“DUPLICATE KEY UPDATE”关键字更合适。

###参考资料

  1. Mysql官网Insert语法介绍: http://dev.mysql.com/doc/refman/5.6/en/insert.html
  2. Mysql官网Replace语法介绍: http://dev.mysql.com/doc/refman/5.6/en/replace.html

© 著作权归作者所有

Tek_Eternal
粉丝 11
博文 24
码字总数 16362
作品 0
福州
程序员
私信 提问
MySQL via EF6 的试用报告

1、如何通过 EF6 来连接 MySQL? 2、如何通过 EF6 来实现 CRUD? 2.1、Create 添加 2.2、Retrieve 查询 2.3、Update 修改 2.4、Delete 删除 3、如何更好的运用 EF6 来完成工作? 3.1、传说中...

韩宗泽
04/22
0
0
LeetCode:Delete Duplicate Emails - 删除重复的邮箱数据

1、题目名称 Delete Duplicate Emails(删除重复的邮箱数据) 2、题目地址 https://leetcode.com/problems/delete-duplicate-emails/ 3、题目内容 写一个SQL删除表Person中所有的重复数据,对...

北风其凉
2016/03/22
363
0
mysql如何临时禁用触发器

mysql如何临时禁用触发器 起因   Mysql的触发器,在触发控制上,只能按照对数据的操作方式(Insert,Update,Delete)以及操作前后(before,after)进行触发控制。但是如果碰到以下需求又...

Tek_Eternal
2015/02/20
2.7K
1
Mysql与sql server的区别

由于工作的原因:上家公司的数据库全采用MySql,所以不得不用它。因此也学到了MySql的一些知识,但考虑到今后可能没机会使用了,所以想趁 现在离职在家休息,打算把这些东西整理一下,也为了...

未来十年
2012/01/12
0
0
Android数据库相关整理

今天对Android中数据库相关的操作和代码做了一个整理,便于自己之后的查阅。主要内容有: 1.原生数据库写法 2.终端进sqlite的操作 3.第三方库 4.事务处理 5.权限和路径 一、原生数据库写法 ...

孟飞阳
2016/07/25
50
0

没有更多内容

加载失败,请刷新页面

加载更多

最简单的获取相机拍照的图片

  import android.content.Intent;import android.graphics.Bitmap;import android.os.Bundle;import android.os.Environment;import android.provider.MediaStore;import andr......

MrLins
今天
6
0
说好不哭!数据可视化深度干货,前端开发下一个涨薪点在这里~

随着互联网在各行各业的影响不断深入,数据规模越来越大,各企业也越来越重视数据的价值。作为一家专业的数据智能公司,个推从消息推送服务起家,经过多年的持续耕耘,积累沉淀了海量数据,在...

个推
今天
9
0
第三方支付-返回与回调注意事项

不管是支付宝,微信,还是其它第三方支付,第四方支付,支付机构服务商只要涉及到钱的交易都要进行如下校验,全部成功了才视为成功订单 1.http请求是否成功 2.校验商户号 3.校验订单号及状态...

Shingfi
今天
5
0
简述Java内存分配和回收策略以及Minor GC 和 Major GC(Full GC)

内存分配: 1. 栈区:栈可分为Java虚拟机和本地方法栈 2. 堆区:堆被所有线程共享,在虚拟机启动时创建,是唯一的目的是存放对象实例,是gc的主要区域。通常可分为两个区块年轻代和年老代。更...

DustinChan
今天
7
0
Excel插入批注:可在批注插入文字、形状、图片

1.批注一直显示:审阅选项卡-------->勾选显示批注选项: 2.插入批注快捷键:Shift+F2 组合键 3.在批注中插入图片:鼠标右键点击批注框的小圆点【重点不可以在批注文本框内点击】----->调出批...

东方墨天
今天
7
1

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部