Mysql 原生语句中save or update 的写法汇总
背景
在平常的开发中，经常碰到这种更新数据的场景：先判断某一数据在库表中是否存在，存在则update，不存在则insert。
如果使用Hibernate，它自带saverOrUpdate方法，用起来很方便，但如使用原生sql语句呢？
新手最常见的写法是，先通过select语句查询记录是否存在，存在则使用update语句更新，不存在则使用insert语句插入。
但是这样做明显不够优雅，存在几个问题：
•为了执行一次更新操作，却在程序中使用了两次sql查询语句，在系统负载比较大的情况下，性能还是会有影响的。
•代码中存在ifelse语句，明明干了一件事，代码却很长。码农都是懒人，能把事情简单做的为啥要复杂做呢：）。
那么问题来了，如何优雅的用sql语句实现saverOrUpdate？
最近工作上也碰到类似更新数据的问题，写多了也开始觉得烦。记得Oracle下有Merge的写法，就google一下mysql的类似实现，整理如下：
数据不存在则插入，存在则无操作
在insert语句中使用ignore关键字实现数据不存在则插入，存在则无操作。它的实现逻辑是，当插入语句出现主键冲突，或者唯一键冲突时，不抛出错误，直接忽略这条插入语句。官网上的相关介绍如下：
“
IfyouusetheIGNOREkeyword,errorsthatoccurwhileexecutingtheINSERTstatementareignored.Forexample,withoutIGNORE,arowthatduplicatesanexistingUNIQUEindexorPRIMARYKEYvalueinthetablecausesaduplicate-keyerrorandthestatementisaborted.WithIGNORE,therowisdiscardedandnoerroroccurs.Ignorederrorsmaygeneratewarningsinstead,althoughduplicate-keyerrorsdonot.
”
Mysql官方文档中提供标准的语法：
INSERT IGNORE
INTOtbl_name
[PARTITION(partition_name,...)]
[(col_name,...)]
{VALUES|VALUE}({expr|DEFAULT},...),(...),...
或者
INSERTIGNORE [INTO]tbl_name [PARTITION(partition_name,...)] [(col_name,...)] SELECT...
可见除了多了个IGNORE关键字以外，跟一般INSERT语句并无区别。
举个栗子：
1.建一张测试用的表
CREATETABLE`test_tab`( `name`varchar(64)NOTNULL, `age`int(11)NOTNULL, PRIMARYKEY(`name`) )ENGINE=InnoDBDEFAULTCHARSET=utf8;
2.插入一条数据
insertinto`test_tab`(`name`,`age`)values('zhangsan',24)
当前test_tab表的数据为：
name|age :—-|:— zhangsan|24
3.再执行一次步骤2的插入语句，则会报异常：
[Err]1062-Duplicateentry'zhangsan'forkey'PRIMARY'
4.对步骤2的insert语句增加ignore关键字，则不会报异常，已存在的数据也不会被更新。
insertIGNOREinto`test_tab`(`name`,`age`)values('zhangsan',24);
------
语句执行情况：
受影响的行:0
时间:0.000s
当前test_tab表的数据为：
name|age :—-|:— zhangsan|24
不存在则插入，存在则更新，其一（使用DUPLICATEKEYUPDATE关键字）
在insert语句中使用ONDUPLICATEKEYUPDATE关键字实现数据不存在则插入，存在则更新的操作。判断数据重复的逻辑依然是主键冲突或者唯一键冲突。
官网上的相关介绍如下：
“
ifyouspecifyONDUPLICATEKEYUPDATE,andarowisinsertedthatwouldcauseaduplicatevalueinaUNIQUEindexorPRIMARYKEY,anUPDATEoftheoldrowisperformed.Theaffected-rowsvalueperrowis1iftherowisinsertedasanewrow,2ifanexistingrowisupdated,and0ifanexistingrowissettoitscurrentvalues.
”
Mysql官方文档中提供标准的语法：
INSERT
[INTO]tbl_name
[PARTITION(partition_name,...)]
[(col_name,...)]
{VALUES|VALUE}({expr|DEFAULT},...),(...),...
[ONDUPLICATEKEYUPDATE
col_name=expr
[,col_name=expr]...]
或者：
INSERT
[INTO]tbl_name
[PARTITION(partition_name,...)]
SETcol_name={expr|DEFAULT},...
[ONDUPLICATEKEYUPDATE
col_name=expr
[,col_name=expr]...]
或者：
INSERT
[INTO]tbl_name
[PARTITION(partition_name,...)]
[(col_name,...)]
SELECT...
[ONDUPLICATEKEYUPDATE
col_name=expr
[,col_name=expr]...]
可见，还是原来insert语句的写法。
举个栗子：
1.使用刚才新建的test_tab表，此时表中的数据如下：
name|age :—-|:— zhangsan|24
2.使用主键相同的insert语句，仍然会duplicatekey错误
insertinto`test_tab`(`name`,`age`)values('zhangsan',50);
------------
[Err]1062-Duplicateentry'zhangsan'forkey'PRIMARY'
3.对刚才的insert语句添加onduplicatekeyupdate…关键字：
insertinto`test_tab`(`name`,`age`)values('zhangsan',50)
ONDUPLICATEKEYUPDATE`age`=50;
------------
受影响的行:2
时间:0.025s
4.此时主键为'zhangsan'的数据，age字段已被更新：
name|age :—-|:— zhangsan|50
5.当然，如果主键不冲突，效果跟一般插入语句是一样的：
insertinto`test_tab`(`name`,`age`)values('lisi',30)
ONDUPLICATEKEYUPDATE`age`=30;
------------
受影响的行:1
时间:0.009s
name|age :—-|:— zhangsan|50 lisi|30
不存在则插入，存在则更新，其二（使用replace语句实现）
saveorupdate在mysql中还有另一种实现，即replaceinto语句，它用起来有点像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,...)]
SETcol_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的数据，报主键冲突。但是换成replaceinto…语句则没问题：
replaceinto`test_tab`(`name`,`age`)values('zhangsan',30);
------------
受影响的行:2
时间:0.009s
3.结果如下：
name|age :—-|:— zhangsan|30 lisi|30
对于操作结果来说，很像是saveorupdate，但是实现方式与INSERT的“DUPLICATEKEYUPDATE”关键字不同。当使用replaceinto语句时，对于重复的数据，是直接删除，然后再插入新数据的。所以它的更新其实不是update，而是delete->insert。大多数情况下，使用replaceinto完成更新操作并无问题，但是有一种场景必须特别注意：
•当被更新的表，存在insert，update，和delete触发器时，使用replace语句必须特别小心。因为按照业务逻辑，更新完数据后，应该触发update触发器，但是使用replace语句的话，会触发delete和insert触发器，如果update触发器有一些特殊操作（比如记录操作日志）的话，使用replace会导致业务逻辑混乱。
所以当被更新表存在触发器的场景时，使用INSERT的“DUPLICATEKEYUPDATE”关键字更合适。
以上就是本文所述的全部内容了，希望能让大家更好的理解mysql中的save和update语句。