栏目导航: 首页 > 数据库类 > MySQL数据库 > 内容

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

www.hx99.net    时间: 2015-04-06    阅读: 次     整理: 华西安全网

 在平常的开发中,经常碰到这种更新数据的场景:先判断某一数据在库表中是否存在,存在则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语句。

本文来源:华西安全网[http://www.hx99.net]
发表评论】【告诉QQ好友】【错误报告】【加入收藏】【关闭