背景

在平常的開發(fā)中，經(jīng)常碰到這種更新數(shù)據(jù)的場(chǎng)景：先判斷某一數(shù)據(jù)在庫(kù)表中是否存在，存在則update，不存在則insert。

如果使用hibernate，它自帶saverorupdate方法，用起來很方便，但如使用原生sql語(yǔ)句呢？

新手最常見的寫法是，先通過select語(yǔ)句查詢記錄是否存在，存在則使用update語(yǔ)句更新，不存在則使用insert語(yǔ)句插入。

但是這樣做明顯不夠優(yōu)雅，存在幾個(gè)問題：

•為了執(zhí)行一次更新操作，卻在程序中使用了兩次sql查詢語(yǔ)句，在系統(tǒng)負(fù)載比較大的情況下，性能還是會(huì)有影響的。

•代碼中存在if else語(yǔ)句，明明干了一件事，代碼卻很長(zhǎng)。碼農(nóng)都是懶人，能把事情簡(jiǎn)單做的為啥要復(fù)雜做呢：）。

那么問題來了，如何優(yōu)雅的用sql語(yǔ)句實(shí)現(xiàn)saverorupdate？

最近工作上也碰到類似更新數(shù)據(jù)的問題，寫多了也開始覺得煩。記得oracle下有merge的寫法，就google一下mysql的類似實(shí)現(xiàn)，整理如下：

數(shù)據(jù)不存在則插入，存在則無操作

在insert語(yǔ)句中使用ignore關(guān)鍵字實(shí)現(xiàn)數(shù)據(jù)不存在則插入，存在則無操作。它的實(shí)現(xiàn)邏輯是，當(dāng)插入語(yǔ)句出現(xiàn)主鍵沖突，或者唯一鍵沖突時(shí)，不拋出錯(cuò)誤，直接忽略這條插入語(yǔ)句。官網(wǎng)上的相關(guān)介紹如下：

“

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官方文檔中提供標(biāo)準(zhǔn)的語(yǔ)法：

代碼如下:

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 ...

可見除了多了個(gè)ignore關(guān)鍵字以外，跟一般insert語(yǔ)句并無區(qū)別。

舉個(gè)栗子：

1.建一張測(cè)試用的表

代碼如下:

create table `test_tab` (

`name` varchar(64) not null,

`age` int(11) not null,

primary key (`name`)

) engine=innodb default charset=utf8;

2.插入一條數(shù)據(jù)

代碼如下:

insert into `test_tab` (`name`,`age`) values ('zhangsan',24)

當(dāng)前test_tab表的數(shù)據(jù)為：

代碼如下:

name|age

:—-|:—

zhangsan|24

3.再執(zhí)行一次步驟2的插入語(yǔ)句，則會(huì)報(bào)異常：

代碼如下:

[err] 1062 - duplicate entry 'zhangsan' for key 'primary'

4.對(duì)步驟2的insert語(yǔ)句增加ignore關(guān)鍵字，則不會(huì)報(bào)異常，已存在的數(shù)據(jù)也不會(huì)被更新。

代碼如下:

insert ignore into `test_tab` (`name`,`age`) values ('zhangsan',24) ;

------

語(yǔ)句執(zhí)行情況：

受影響的行: 0

時(shí)間: 0.000s

當(dāng)前test_tab表的數(shù)據(jù)為：

代碼如下:

name|age

:—-|:—

zhangsan|24

不存在則插入，存在則更新，其一（使用duplicate key update關(guān)鍵字）

在insert語(yǔ)句中使用on duplicate key update關(guān)鍵字實(shí)現(xiàn)數(shù)據(jù)不存在則插入，存在則更新的操作。判斷數(shù)據(jù)重復(fù)的邏輯依然是主鍵沖突或者唯一鍵沖突。

官網(wǎng)上的相關(guān)介紹如下：

“

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官方文檔中提供標(biāo)準(zhǔn)的語(yǔ)法：

代碼如下:

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語(yǔ)句的寫法。

舉個(gè)栗子：

1.使用剛才新建的test_tab表，此時(shí)表中的數(shù)據(jù)如下：

代碼如下:

name|age

:—-|:—

zhangsan|24

2.使用主鍵相同的insert語(yǔ)句，仍然會(huì)duplicate key錯(cuò)誤

代碼如下:

insert into `test_tab` (`name`,`age`) values ('zhangsan',50) ;

------------

[err] 1062 - duplicate entry 'zhangsan' for key 'primary'

3.對(duì)剛才的insert語(yǔ)句添加 on duplicate key update … 關(guān)鍵字：

代碼如下:

insert into `test_tab` (`name`,`age`) values ('zhangsan',50)

on duplicate key update `age`=50 ;

------------

受影響的行: 2

時(shí)間: 0.025s

4.此時(shí)主鍵為'zhangsan'的數(shù)據(jù)，age字段已被更新：

代碼如下:

name|age

:—-|:—

zhangsan|50

5.當(dāng)然，如果主鍵不沖突，效果跟一般插入語(yǔ)句是一樣的：

代碼如下:

insert into `test_tab` (`name`,`age`) values ('lisi',30)

on duplicate key update `age`=30 ;

------------

受影響的行: 1

時(shí)間: 0.009s

代碼如下:

name|age

:—-|:—

zhangsan|50

lisi|30

不存在則插入，存在則更新，其二（使用replace語(yǔ)句實(shí)現(xiàn)）

save or update 在mysql中還有另一種實(shí)現(xiàn)，即replace into語(yǔ)句，它用起來有點(diǎn)像oracle的merge。判斷數(shù)據(jù)重復(fù)的邏輯依然是主鍵或者唯一鍵沖突。mysql官方文檔中提供標(biāo)準(zhǔn)的語(yǔ)法：

代碼如下:

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 ...

舉個(gè)栗子：

1.仍然使用上面的test_tab表的數(shù)據(jù)，此時(shí)數(shù)據(jù)如下

代碼如下:

name|age

:—-|:—

zhangsan|50

lisi|30

2.使用一般的insert語(yǔ)句插入name=zhangsan的數(shù)據(jù)，報(bào)主鍵沖突。但是換成replace into…語(yǔ)句則沒問題：

代碼如下:

replace into `test_tab` (`name`,`age`) values ('zhangsan',30) ;

------------

受影響的行: 2

時(shí)間: 0.009s

3.結(jié)果如下：

代碼如下:

name|age

:—-|:—

zhangsan|30

lisi|30

  對(duì)于操作結(jié)果來說，很像是save or update，但是實(shí)現(xiàn)方式與insert的“duplicate key update”關(guān)鍵字不同。當(dāng)使用replace into語(yǔ)句時(shí)，對(duì)于重復(fù)的數(shù)據(jù)，是直接刪除，然后再插入新數(shù)據(jù)的。所以它的更新其實(shí)不是update，而是delete->insert。大多數(shù)情況下，使用replace into完成更新操作并無問題，但是有一種場(chǎng)景必須特別注意：

•當(dāng)被更新的表，存在insert，update，和delete觸發(fā)器時(shí)，使用replace語(yǔ)句必須特別小心。因?yàn)榘凑諛I(yè)務(wù)邏輯，更新完數(shù)據(jù)后，應(yīng)該觸發(fā)update觸發(fā)器，但是使用replace語(yǔ)句的話，會(huì)觸發(fā)delete和insert觸發(fā)器，如果update觸發(fā)器有一些特殊操作（比如記錄操作日志）的話，使用replace會(huì)導(dǎo)致業(yè)務(wù)邏輯混亂。

所以當(dāng)被更新表存在觸發(fā)器的場(chǎng)景時(shí)，使用insert的“duplicate key update”關(guān)鍵字更合適。

以上就是本文所述的全部?jī)?nèi)容了，希望能讓大家更好的理解mysql中的save和update語(yǔ)句。