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

背景 在平常的开发中，经常碰到这种更新数据的场景：先判断某一数据在库表中是否存在，存在则update，不存在则insert。 如果使用Hibernate，它自带saverOrUpdate方法，用起来很方便，但如使用原生sql语句呢? 新手最常见的写法是，先通过select语句查询记录是

 　　背景

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