您的位置：首页 > 数据库 > mysql  >  Mysql 原生语句中save or update 的写法汇总

Mysql 原生语句中save or update 的写法汇总

 更新时间：2015年3月15日 21:24  点击：2805

背景

  在平常的开发中，经常碰到这种更新数据的场景：先判断某一数据在库表中是否存在，存在则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”关键字更合适。

以上就是本文所述的全部内容了，希望能让大家更好的理解mysql中的save和update语句。

[!--infotagslink--]

相关文章

  • mysql中获取一天、一周、一月时间数据的各种sql语句写法

    创建表：复制代码 代码如下:create table if not exists t( id int, addTime datetime default '0000-00-00 00:00:00′)添加两条初始数据：insert t values(1, '2012-07-12 21:00:00′);insert t values(2, '2012-07...2014-05-31

  • MySQL中SELECT+UPDATE处理并发更新问题解决方案分享

    问题背景： 假设MySQL数据库有一张会员表vip_member（InnoDB表），结构如下： 当一个会员想续买会员（只能续买1个月、3个月或6个月）时，必须满足以下业务要求： •如果end_at早于当前时间，则设置start_at为当前时间，end_at为当前时...2014-05-31

  • PHP实现今天是星期几的几种写法

    复制代码 代码如下: // 第一种写法 $da = date("w"); if( $da == "1" ){ echo "今天是星期一"; }else if( $da == "2" ){ echo "今天是星期二"; }else if( $da == "3" ){ echo "今天是星期三"; }else if( $da == "4"...2013-10-04

  • C#条件语句、循环语句(if、while)

    这篇文章主要介绍了C#条件语句、循环语句(if、while)的用法，学习c#的朋友可以参考下...2020-06-25

  • Mybatis执行update失败的解决

    这篇文章主要介绍了Mybatis执行update失败的解决方案，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教...2021-09-01

  • 解决Mybatis中mapper.xml文件update,delete及insert返回值问题

    这篇文章主要介绍了解决Mybatis中mapper.xml文件update,delete及insert返回值问题，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...2020-11-23

  • c# 循环语句的使用方法

    这篇文章主要介绍了c# 循环语句的使用方法，文中代码非常详细，供大家参考和学习，感兴趣的朋友可以了解下...2020-06-25

  • 详解git submodule update获取不到最新提交的代码

    这篇文章主要介绍了详解git submodule update获取不到最新提交的代码，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧...2020-08-21

  • Mysql 原生语句中save or update 的写法汇总

    背景  在平常的开发中，经常碰到这种更新数据的场景：先判断某一数据在库表中是否存在，存在则update，不存在则insert。 如果使用Hibernate，它自带saverOrUpdate方法，用起来很方便，但如使用原生sql语句呢？  ...2015-03-15

  • jquery if条件语句的写法

    下面小编就为大家带来一篇jquery if条件语句的写法。小编觉得挺不错的，现在分享给大家，也给大家做个参考。一起跟随小编过来看看吧...2016-05-20

  • C#实现String字符串转化为SQL语句中的In后接的参数详解

    在本篇文章中小编给大家分享的是一篇关于C#实现String字符串转化为SQL语句中的In后接的实例内容和代码，需要的朋友们参考下。...2020-06-25

  • Python基础之循环语句相关知识总结

    今天给大家带来的是关于Python基础的相关知识,文章围绕着Python循环语句展开,文中有非常详细的介绍及代码示例,需要的朋友可以参考下...2021-06-15

  • 如何利用JavaScript编写更好的条件语句详解

    这篇文章主要给大家介绍了关于如何利用JavaScript编写更好的条件语句的相关资料，文中通过示例代码介绍的非常详细，对大家学习或者使用JavaScript具有一定的参考学习价值，需要的朋友们下面来一起学习学习吧...2020-08-10

  • Nginx中if语句的判断条件与多条件判断详解

    这篇文章主要介绍了关于Nginx中if语句的判断条件与多条件判断的相关资料，文中给出了详细的示例代码，对大家具有一定的参考价值，需要的朋友们下面来一起看看吧。...2017-07-06

  • SQL语句中的一些参数如何用变量来代替?

    可以采用exec方法 declare @tempStr varchar(350) select @tempStr='Update weekcount set [' convert(varchar,@week) ']=[' convert(varchar,@week) '] 1 where us...2016-11-25

  • 一篇文章带你了解JavaScript-语句

    这篇文章主要介绍了JavaScript的基本语句,包括注释和书写格式等基本知识,需要的朋友可以参考下，希望本篇文章能给您带来帮助...2021-08-15

  • mysql update case 更新字段值不固定的操作

    这篇文章主要介绍了mysql update case 更新字段值不固定的操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-04

  • C++函数中return语句的使用方法

    C++中的return语句是函数中一个重要的语句，return语句用于结束当前正在执行的函数，并将控制权返回给调用此函数的函数，需要的朋友可以了解下...2020-04-25

  • 如何写软文？软文写法技巧

    我们很多人都感觉软文的力量强大，如春风化雨，润物细无声!等你发现这是一篇软文的时候，已经完了，因为你至少脑海中有这种印象了!对于软文来说，这是一种成功! 很多人写软...2016-10-10

  • go语言中if语句用法实例

    这篇文章主要介绍了go语言中if语句用法,以实例形式分析了if语句的定义及使用技巧,非常具有实用价值,需要的朋友可以参考下...2020-05-03