MySql插入唯一鍵衝突的三種可選方式

一灰灰發表於2018-07-10
MySql

logo

MySql插入時唯一鍵衝突的幾種處理方式

MySql插入一條記錄，結果提示主鍵衝突，怎麼辦？

批量插入資料時，發現插入的這批資料中，有某些記錄存在唯一鍵衝突，一個一個跳出來就比較麻煩了，有什麼好的辦法直接忽略掉衝突的記錄麼？

下面簡單記錄三種處理方式

I. 插入時唯一鍵衝突問題

1. Ignore關鍵詞

某些場景下，我們需要批量插入的資料，某些已經在DB中了，因此我希望在出現衝突時，直接跳過，把能插入的都插入就好，這種情況下，使用ignore關鍵詞就比較合適了

一個實際的case如下

insert ignore into table (xxx, xxx) values (xxx,xxx), (xxx, xxx);
複製程式碼

執行截圖如下, 注意下面紅框中的內容，表示忽略了兩條，執行插入成功一條

image.png

2. Replace Into方式

如果在批量插入中，存在衝突時，我希望用我的新資料替換舊的資料，這個時候就可以使用replace into了

常用姿勢如下

replace into `user` (`id`, `name`, `create_at`, `update_at`) 
values
	(1, 'test', '2018-07-10 18:54:00', '2018-07-10 19:54:52'),
	(2, 'test2', '2018-07-10 18:54:00', '2018-07-10 19:54:52'),
	(3, 'test3', '2018-07-10 18:54:00', '2018-07-10 19:54:52');
複製程式碼

執行截圖如下，注意紅框中，當某條記錄衝突之後並修改，則影響行數為2, 其實際過程是

  • 刪除衝突資料
  • 插入新的資料

image.png

3. ON DUPLICATE KEY UPDATE

在出現衝突時，希望更新某些資料，這個時候就可以在insert語句的最後加上on duplicate key update了

例項如下

insert into `user` (`id`, `name`, `create_at`, `update_at`) values (1, 'test0', '2018-07-10 18:54:00', '2018-07-10 18:54:52') ON DUPLICATE KEY UPDATE `update_at`='2018-07-10 19:58:05';
複製程式碼

執行截圖如下，這個是在原記錄的基礎上執行更新指定的value, 比如上面的插入中，當衝突時，我們只更新update_at欄位，而name的test0沒有更新

image.png

II. 其他

1. 一灰灰Blog： https://liuyueyi.github.io/hexblog

一灰灰的個人部落格，記錄所有學習和工作中的博文，歡迎大家前去逛逛

2. 宣告

盡信書則不如，已上內容，純屬一家之言，因個人能力有限，難免有疏漏和錯誤之處，如發現bug或者有更好的建議，歡迎批評指正，不吝感激

3. 掃描關注

QrCode

相關文章