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”关键字更合适。

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

(0)

相关推荐

  • Window10下安装 mysql5.7图文教程(解压版)

    1. 解压mysql-5.7.11-winx64.zip 到某文件夹, 如C:\DevelopCommon\mysql-5.7.11-winx64. 2. 配置环境变量 变量名 : MYSQL_HOME 变量值 : C:\DevelopCommon\mysql-5.7.11-winx64 变量名 : Path 变量值 : %MYSQL_HOME%\bin 2. 以管理员身份进入 C:\DevelopCommon\mysql-5.7.11-winx64\bin目录: (在window菜单下搜索命令

  • mysql5.x升级到mysql5.7后导入之前数据库date出错的快速解决方法

    mysql5.x升级至mysql5.7后导入之前数据库date出错的解决方法如下所示: 修改mysql5.7的配置文件即可解决,方法如下: linux版:找到mysql的安装路径进入默认的为/usr/share/mysql/中,进行对my-default.cnf编辑 利用查找功能"/"找到"sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES" 将其删除或者是注释即可. windows版:32位找到mysql安装路径

  • MySQL中ADDDATE()函数的使用教程

    复制代码 代码如下: ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days) 当被调用的第二个参数的区间形式,ADDDATE()是DATE_ADD()的同义词.相关功能SUBDATE()是DATE_SUB()的代名词.对于区间上的单元参数的详细信息,请参阅DATE_ADD()的说明. mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY); +--------------------------

  • php+MySQL判断update语句是否执行成功的方法

    update语句是PHP+MySQL中常用的操作,判断update语句是否执行成功是其中非常重要的一个环节.本文就以实例展示了php+MySQL判断update语句是否执行成功的方法.分享给大家供大家参考之用.具体方法如下: 代码一: $rs=MySQL_query($sql); if(mysql_affected_rows()) echo "sql执行成功"; else echo "sql执行失败"; 代码二: <?php /* 连接数据库 */ mysql

  • Mysql 数据库死锁过程分析(select for update)

    近期有一个业务需求,多台机器需要同时从Mysql一个表里查询数据并做后续业务逻辑,为了防止多台机器同时拿到一样的数据,每台机器需要在获取时锁住获取数据的数据段,保证多台机器不拿到相同的数据. 我们Mysql的存储引擎是innodb,支持行锁.解决同时拿数据的方法有很多,为了更加简单,不增加其他表和服务的情况下,我们考虑采用select... for update的方式,这样X锁锁住查询的数据段,表里其他数据没有锁,其他业务逻辑还是可以操作. 这样一台服务器比如select .. for upda

  • 安装Mysql5.7.10 winx64出现的几个问题汇总

    电脑是64位的安装不了Windows (x86, 32-bit),Mysql installer MSI ,然后下载了Windows (x86, 32-bit), ZIP Archive 这种是免安装直接解压使用的,因为最新版本的原因也有一些问题. (1) 首先解压后有一个默认的配置文件my-default.ini配置文件,直接替换掉换成以下的配置文件(注意安装路径的更改). [mysql] # 设置mysql客户端默认字符集 default-character-set=utf8 [mysqld

  • 64位Win10系统安装Mysql5.7.11的方法(案例详解)

    最近在装了64位Win10系统的mac book笔记本上用mysql-installer-community-5.7.11.0安装Mysql5.7.11,在配置mysql server时老是卡住,报错.(在别的PC相同windows系统,自动安装没问题),决定手动安装,依然问题多多,最后的成功安装案例如下: 一.准备安装软件 1.mysql.com下载mysql-5.7.11-win32.zip 2.mysql-workbench-community-6.3.6-win32.msi 3.vcre

  • MySQL 从一张表update字段到另外一张表中

    先来几个简单的示例 Solution 1:  1列 update student s, city c set s.city_name = c.name where s.city_code = c.code; Solution 2:  多个列 update a, b set a.title=b.title, a.name=b.name where a.id=b.id Solution 3: 子查询 update student s set city_name = (select name from

  • Mysql5.7.11在windows10上的安装与配置(解压版)

    第一步 my-default.ini 添加配置: #绑定IPv4和3306端 bind-address = 127.0.0.1 port = 3306 # 设置mysql的安装目 basedir= E:\mysql # 设置mysql数据库的数据的存放目 datadir=E:\mysql\data # 允许最大连接数 max_connections=200 #设置默认字符集为utf8 default-character-set=utf8 第二步 右击我的电脑–>属性–>高级–>环境变量,

  • mysql中You can’t specify target table for update in FROM clause错误解决方法

    mysql中You can't specify target table for update in FROM clause错误的意思是说,不能先select出同一表中的某些值,再update这个表(在同一语句中). 例如下面这个sql: 复制代码 代码如下: delete from tbl where id in (         select max(id) from tbl a where EXISTS         (             select 1 from tbl b

随机推荐