| 标题 | mysql 原生语句中save 的写法汇总 |
| 范文 | 背景 在平常的开发中，经常碰到这种更新数据的场景：先判断某一数据在库表中是否存在，存在则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语句。 |
| 随便看 |
|
在线学习网范文大全提供好词好句、学习总结、工作总结、演讲稿等写作素材及范文模板，是学习及工作的有利工具。