MySQL中ON DUPLICATE KEY UPDATE優(yōu)雅解決存在更新/不存在插入難題
前言
在日常的數(shù)據(jù)庫操作中，我們經(jīng)常會(huì)遇到這樣的場景：“如果數(shù)據(jù)存在，就更新它；如果不存在，就插入一條新的”。這種模式通常被稱為 “Upsert”（Update + Insert）。在 MySQL 中，實(shí)現(xiàn) Upsert 最優(yōu)雅、最高效的方式之一就是使用 ON DUPLICATE KEY UPDATE 語法。
一、基本概念
1、什么是 ON DUPLICATE KEY UPDATE
ON DUPLICATE KEY UPDATE是 MySQL 特有的一種 INSERT 語句擴(kuò)展，當(dāng)執(zhí)行 INSERT 操作時(shí)，如果插入的數(shù)據(jù)與表中已有數(shù)據(jù)的主鍵(PRIMARY KEY)?或唯一索引(UNIQUE INDEX)?發(fā)生沖突（即要插入的值與已有記錄的主鍵或唯一索引值相同），則不執(zhí)行插入操作，而是轉(zhuǎn)而執(zhí)行 UPDATE 操作，更新已存在的記錄。
2、工作原理
嘗試插入?：MySQL 首先嘗試按照正常的 INSERT 語句插入新記錄
檢查沖突?：在插入前，MySQL 會(huì)檢查是否存在與待插入數(shù)據(jù)主鍵或唯一索引沖突的記錄
沖突處理?：
- 如果沒有沖突：正常插入新記錄
- 如果有沖突：不插入新記錄，而是根據(jù) ON DUPLICATE KEY UPDATE子句更新已存在的記錄
3、基本語法
基本語法格式如下：
INSERT INTO table_name (column1, column2, ..., columnN)
VALUES (value1, value2, ..., valueN)
ON DUPLICATE KEY UPDATE
column1 = value1,
column2 = value2,
...;
更常用的寫法是使用 VALUES() 函數(shù)來引用原本打算插入的值：
INSERT INTO table_name (column1, column2, ..., columnN)
VALUES (value1, value2, ..., valueN)
ON DUPLICATE KEY UPDATE
column1 = VALUES(column1),
column2 = VALUES(column2),
...;
觸發(fā)條件：只有當(dāng)插入操作違反了 主鍵（PRIMARY KEY） 或 唯一索引（UNIQUE INDEX） 約束時(shí)，UPDATE 部分才會(huì)被執(zhí)行
二、使用場景
1、計(jì)數(shù)器更新
最常見的應(yīng)用場景是實(shí)現(xiàn)計(jì)數(shù)器功能，如文章瀏覽量、點(diǎn)贊數(shù)等
INSERT INTO article_views (article_id, view_count) VALUES (123, 1) ON DUPLICATE KEY UPDATE view_count = view_count + 1;
2、配置項(xiàng)更新
當(dāng)需要更新或插入配置項(xiàng)時(shí)
INSERT INTO system_config (config_key, config_value, last_updated)
VALUES ('site_title', 'My Website', NOW())
ON DUPLICATE KEY UPDATE
config_value = VALUES(config_value), last_updated = NOW();
3、購物車商品更新
添加商品到購物車，已存在則更新數(shù)量
INSERT INTO shopping_cart (user_id, product_id, quantity)
VALUES (123, 456, 2)
ON DUPLICATE KEY UPDATE
quantity = quantity + VALUES(quantity),
added_at = CURRENT_TIMESTAMP;
必須添加主鍵或唯一索引，否則ON DUPLICATE KEY UPDATE將不會(huì)觸發(fā)，語句會(huì)正常執(zhí)行插入操作（如果無其他錯(cuò)誤）
三、高級(jí)用法
1、條件更新
在 ON DUPLICATE KEY UPDATE子句中使用條件表達(dá)式
-- 這個(gè)例子只在新的價(jià)格更低時(shí)才更新價(jià)格
INSERT INTO products (product_id, price, last_updated)
VALUES (101, 99.99, NOW())
ON DUPLICATE KEY UPDATE
price = IF(VALUES(price) < price, VALUES(price), price),
last_updated = NOW();
2、多表關(guān)聯(lián)
雖然不能直接在 ON DUPLICATE KEY UPDATE中使用多表，但可以結(jié)合子查詢
INSERT INTO user_stats (user_id, login_count) SELECT 123, 1 FROM dual WHERE NOT EXISTS (SELECT 1 FROM users WHERE id = 123) ON DUPLICATE KEY UPDATE login_count = login_count + 1;
3、批量操作優(yōu)化
對(duì)于大量數(shù)據(jù)的批量插入/更新，考慮以下優(yōu)化
INSERT INTO log_entries (user_id, action, timestamp)
VALUES
(1, 'login', NOW()),
(2, 'view', NOW()),
(3, 'purchase', NOW())
ON DUPLICATE KEY UPDATE
action = VALUES(action),
timestamp = VALUES(timestamp);
當(dāng)表有多個(gè)唯一約束時(shí)，任何唯一鍵沖突都會(huì)觸發(fā)UPDATE
四、其他處理沖突的方案
1、REPLACE INTO
實(shí)際上是先DELETE再INSERT，主鍵會(huì)有變化
REPLACE INTO users (email, name, login_count)
VALUES ('test@example.com', 'Test User', 1);
2、INSERT IGNORE
沖突時(shí)直接忽略，不更新
INSERT IGNORE INTO users (email, name)
VALUES ('test@example.com', 'Test User');
到此這篇關(guān)于MySQL中ON DUPLICATE KEY UPDATE優(yōu)雅解決存在更新/不存在插入難題的文章就介紹到這了,更多相關(guān)MySQL ON DUPLICATE KEY UPDATE內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
使用MySQL生成最近24小時(shí)整點(diǎn)時(shí)間臨時(shí)表
MySQL臨時(shí)表是一種只存在于當(dāng)前數(shù)據(jù)庫連接或會(huì)話期間的表,它們可以被用來存儲(chǔ)臨時(shí)數(shù)據(jù),這些數(shù)據(jù)可以在查詢中被使用,但是它們不會(huì)在數(shù)據(jù)庫中永久存儲(chǔ),這篇文章主要給大家介紹了關(guān)于如何使用MySQL生成最近24小時(shí)整點(diǎn)時(shí)間臨時(shí)表的相關(guān)資料,需要的朋友可以參考下2024-01-01
這牌你文章主要給大家分享的是MySQL中使用正則表達(dá)式詳情，MySQL中支持正則表達(dá)式匹配，在復(fù)雜的過濾條件中，可以考慮使用正則表達(dá)式。使用正則表達(dá)式需要掌握一些正則表達(dá)式的語法和指令，下面來看看文章的詳細(xì)內(nèi)容介紹吧，希望對(duì)你有所幫助2021-11-11
這篇文章主要介紹了Mysql嵌套子查詢,子查詢就是指的在一個(gè)完整的查詢語句之中，嵌套若干個(gè)不同功能的小查詢，從而一起完成復(fù)雜查詢的一種編寫形式，為了讓讀者更加清楚子查詢的概念，感興趣的朋友跟隨小編一起看看吧2022-01-01
這篇文章主要介紹了MySQL整型數(shù)據(jù)溢出的解決方法,本文出現(xiàn)整型溢出的mysql版本是5.1,5.1下整型溢出不會(huì)報(bào)錯(cuò),而會(huì)變成負(fù)數(shù),需要的朋友可以參考下2014-07-07
一文總結(jié)MySQL中數(shù)學(xué)函數(shù)有哪些
MySQL函數(shù)包括數(shù)學(xué)函數(shù)、字符串函數(shù)、日期和時(shí)間函數(shù)、條件判斷函數(shù)、系統(tǒng)信息函數(shù)、加密函數(shù)等,下面這篇文章主要給大家介紹了關(guān)于MySQL中數(shù)學(xué)函數(shù)有哪些的相關(guān)資料,需要的朋友可以參考下2023-02-02
MySQL ClickHouse常用表引擎超詳細(xì)講解
這篇文章主要介紹了MySQL ClickHouse常用表引擎，ClickHouse表引擎中，CollapsingMergeTree和VersionedCollapsingMergeTree都能通過標(biāo)記位按規(guī)則折疊數(shù)據(jù)，從而達(dá)到更新和刪除的效果2022-11-11
IDEA無法連接mysql數(shù)據(jù)庫的6種解決方法大全
這篇文章主要介紹了IDEA無法連接mysql數(shù)據(jù)庫的6種解決方法大全，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-11-11
這篇文章介紹了MySQL使用索引優(yōu)化性能的方法，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2022-04-04
win10下安裝兩個(gè)MySQL5.6.35數(shù)據(jù)庫
這篇文章主要為大家詳細(xì)介紹了win10下兩個(gè)MySQL5.6.35數(shù)據(jù)庫安裝教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-05-05
MySQL使用MRG_MyISAM(MERGE)實(shí)現(xiàn)分表后查詢的示例
這篇文章主要介紹了MySQL使用MRG_MyISAM(MERGE)實(shí)現(xiàn)分表后查詢的示例，幫助大家更好的理解和使用MySQL數(shù)據(jù)庫，感興趣的朋友可以了解下2020-12-12