Mysql 原生語句中save or update 的寫法匯總

 　　背景

　　  在平常的開發中，經常碰到這種更新數據的場景：先判斷某一數據在庫表中是否存在，存在則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語句時，對於重復