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

作者:佚名     字体:[增加 减小]    类型:转载
这篇文章主要介绍了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] ... ]

或者:

复制代码 代码如下: