Mysql 原生語(yǔ)句中save or update 的寫(xiě)法匯總
背景
在平常的開(kāi)發(fā)中，經(jīng)常碰到這種更新數(shù)據(jù)的場(chǎng)景：先判斷某一數(shù)據(jù)在庫(kù)表中是否存在，存在則update，不存在則insert。
如果使用Hibernate，它自帶saverOrUpdate方法，用起來(lái)很方便，但如使用原生sql語(yǔ)句呢？
新手最常見(jiàn)的寫(xiě)法是，先通過(guò)select語(yǔ)句查詢記錄是否存在，存在則使用update語(yǔ)句更新，不存在則使用insert語(yǔ)句插入。
但是這樣做明顯不夠優(yōu)雅，存在幾個(gè)問(wèn)題：
•為了執(zhí)行一次更新操作，卻在程序中使用了兩次sql查詢語(yǔ)句，在系統(tǒng)負(fù)載比較大的情況下，性能還是會(huì)有影響的。
•代碼中存在if else語(yǔ)句，明明干了一件事，代碼卻很長(zhǎng)。碼農(nóng)都是懶人，能把事情簡(jiǎn)單做的為啥要復(fù)雜做呢：）。
那么問(wèn)題來(lái)了，如何優(yōu)雅的用sql語(yǔ)句實(shí)現(xiàn)saverOrUpdate？
最近工作上也碰到類似更新數(shù)據(jù)的問(wèn)題，寫(xiě)多了也開(kāi)始覺(jué)得煩。記得Oracle下有Merge的寫(xiě)法，就google一下mysql的類似實(shí)現(xiàn)，整理如下：
數(shù)據(jù)不存在則插入，存在則無(wú)操作
在insert語(yǔ)句中使用ignore關(guān)鍵字實(shí)現(xiàn)數(shù)據(jù)不存在則插入，存在則無(wú)操作。它的實(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 ...
可見(jiàn)除了多了個(gè)IGNORE關(guān)鍵字以外，跟一般INSERT語(yǔ)句并無(wú)區(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] ... ]
可見(jiàn)，還是原來(lái)insert語(yǔ)句的寫(xiě)法。
舉個(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ǔ)句，它用起來(lái)有點(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ǔ)句則沒(méi)問(wèn)題：
replace into `test_tab` (`name`,`age`) values ('zhangsan',30) ;
------------
受影響的行: 2
時(shí)間: 0.009s
3.結(jié)果如下：
name|age
:—-|:—
zhangsan|30
lisi|30
對(duì)于操作結(jié)果來(lái)說(shuō)，很像是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完成更新操作并無(wú)問(wèn)題，但是有一種場(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ǔ)句。
相關(guān)文章
關(guān)于InnoDB索引的底層實(shí)現(xiàn)和實(shí)際效果
這篇文章主要介紹了關(guān)于InnoDB索引的底層實(shí)現(xiàn)和實(shí)際效果，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-12-12
windows下修改Mysql5.7.11初始密碼的圖文教程
這篇文章主要介紹了windows下修改Mysql5.7.11初始密碼的圖文教程,本文給大家介紹的非常詳細(xì)，具有參考借鑒價(jià)值，需要的朋友可以參考下2016-11-11
MySQL學(xué)習(xí)第三天 Windows 64位操作系統(tǒng)下驗(yàn)證MySQL
MySQL學(xué)習(xí)第三天教大家如何在Windows 64位操作系統(tǒng)下驗(yàn)證MySQL，感興趣的小伙伴們可以參考一下2016-05-05
MySQL 8中新增的這三大索引 隱藏、降序、函數(shù)
這篇文章主要介紹了MySQL 8.x版本中新增的三大索引 隱藏索引、降索引序、函數(shù)索引，如果文章對(duì)你有點(diǎn)幫助，小伙伴們點(diǎn)贊、收藏、評(píng)論、分享走起呀2021-09-09
SQL?日期處理視圖創(chuàng)建(常見(jiàn)數(shù)據(jù)類型查詢防范?SQL注入)
這篇文章主要為大家介紹了SQL日期處理和視圖創(chuàng)建：常見(jiàn)數(shù)據(jù)類型、示例查詢和防范?SQL?注入方法示例,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪2023-12-12
Linux系統(tǒng)MySQL8.0.19快速安裝配置教程圖解
這篇文章主要介紹了Linux系統(tǒng)MySQL8.0.19快速安裝配置教程，本文通過(guò)圖文并茂的形式給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2020-02-02
MySQL死鎖問(wèn)題分析及解決方法實(shí)例詳解
這篇文章主要介紹了MySQL死鎖問(wèn)題分析及解決方法,需要的朋友可以參考下2014-06-06
MySql獲取某個(gè)字段存在于哪個(gè)表的sql語(yǔ)句
本文為大家詳細(xì)介紹下通過(guò)MySql查詢某個(gè)字段所在表是哪一個(gè)，具體的sql語(yǔ)句如下，感興趣的朋友可以參考下，希望對(duì)大家有所幫助2013-07-07
這篇文章主要介紹了一文詳解MySQL主從同步原理，MySQL主從同步是基于Bin?Log實(shí)現(xiàn)的，而B(niǎo)in?Log記錄的是原始SQL語(yǔ)句，更多相關(guān)內(nèi)容介紹感興趣的小伙伴可以參考一下2022-08-08
MySQL是一個(gè)開(kāi)放源碼的小型關(guān)聯(lián)式數(shù)據(jù)庫(kù)管理系統(tǒng)，被廣泛地應(yīng)用在Internet上的中小型網(wǎng)站中。接下來(lái)通過(guò)本篇文章給大家分享安裝MySQL常見(jiàn)的三種方式，需要的朋友可以參考下2015-09-09