insertOrUpdate 在我們?nèi)粘Ｊ褂弥斜容^常見(jiàn)，那么它是如何實(shí)現(xiàn)的呢，不知道大家有沒(méi)有考慮過(guò)呢？

在 MySQL 中，可采用INSERT INTO ... ON DUPLICATE KEY UPDATE語(yǔ)句實(shí)現(xiàn) insertOrUpdate 功能。

值得留意的是，在出現(xiàn)重復(fù)鍵時(shí)，會(huì)在先前索引值和當(dāng)前值之間添加臨時(shí)鍵鎖，這可能導(dǎo)致死鎖。

若要使用 INSERT INTO ... ON DUPLICATE KEY UPDATE 語(yǔ)句，需滿(mǎn)足以下條件：

1. 表必須具有主鍵或唯一索引；
2. 插入的數(shù)據(jù)必須包含主鍵或唯一索引列；
3. 主鍵或唯一索引列的值不能為 NULL。

舉個(gè)例子：

設(shè)想有一張 student 表，包括 id、name 和 age 三列，其中 id 是主鍵?，F(xiàn)在要插入一條數(shù)據(jù)，若該數(shù)據(jù)的主鍵已存在，則更新該數(shù)據(jù)的姓名和年齡，否則插入該數(shù)據(jù)。

INSERT INTO student (id, name, age) VALUES (1, 'Paidaxing', 20)
ON DUPLICATE KEY UPDATE name='Paidaxing', age=18;

底層實(shí)現(xiàn)

使用INSERT INTO ... ON DUPLICATE KEY UPDATE語(yǔ)句，如果數(shù)據(jù)庫(kù)中已存在具有相同唯一索引或主鍵的記錄，則更新該記錄。其底層原理和執(zhí)行流程如下：

1. 檢查唯一索引或主鍵：執(zhí)行INSERT INTO ... ON DUPLICATE KEY UPDATE語(yǔ)句時(shí)，數(shù)據(jù)庫(kù)首先嘗試插入新行。在此過(guò)程中，數(shù)據(jù)庫(kù)會(huì)檢查表中是否存在與新插入行具有相同的唯一索引或主鍵的記錄。
2. 沖突處理：如果不存在沖突的唯一索引或主鍵，新行將被正常插入。如果存在沖突，即發(fā)現(xiàn)重復(fù)的唯一索引或主鍵值，數(shù)據(jù)庫(kù)將不會(huì)插入新行，而是轉(zhuǎn)而執(zhí)行更新操作。
3. 執(zhí)行更新：在檢測(cè)到唯一索引或主鍵的沖突后，數(shù)據(jù)庫(kù)將根據(jù)ON DUPLICATE KEY UPDATE后面指定的列和值來(lái)更新已存在的記錄。這里可以指定一個(gè)或多個(gè)列進(jìn)行更新，并且可以使用 VALUES 函數(shù)引用原本嘗試插入的值。

相似 SQL

除了 INSERT INTO ... ON DUPLICATE KEY UPDATE 之外，還有一些類(lèi)似的 SQL 語(yǔ)句，比如：

1. REPLACE INTO：如果存在唯一索引沖突，則先刪除舊記錄，再插入新記錄。
2. INSERT IGNORE INTO：如果唯一索引沖突，則忽略該條插入操作，不報(bào)錯(cuò)。

淺談主鍵跳躍

在 MySQL 中使用 INSERT ON DUPLICATE KEY UPDATE 語(yǔ)句時(shí)，如果插入操作失?。ㄒ?yàn)橹麈I或唯一鍵沖突），而執(zhí)行了更新操作，確實(shí)會(huì)導(dǎo)致自增主鍵計(jì)數(shù)器增加，即使沒(méi)有實(shí)際插入新記錄。

這是因?yàn)?MySQL 在嘗試插入新記錄時(shí)，會(huì)先分配一個(gè)新的自增主鍵值，無(wú)論后續(xù)是插入成功還是執(zhí)行更新操作，這個(gè)主鍵值都已經(jīng)被分配并且會(huì)增加。

例如，假設(shè)有一個(gè)表 test 定義如下：

CREATE TABLE test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    value VARCHAR(255),
    UNIQUE KEY unique_value (value)
);

然后執(zhí)行以下語(yǔ)句：

INSERT INTO test (value) VALUES ('a')
ON DUPLICATE KEY UPDATE value = 'a';

圖片

再執(zhí)行一次：

圖片

此時(shí)，由于 value 列存在唯一鍵約束，并且已經(jīng)存在一條記錄 value='a'，所以不會(huì)插入新記錄，而是會(huì)執(zhí)行更新操作。但即便如此，自增主鍵 id 的計(jì)數(shù)器依然會(huì)增加。

然后再插入一條新的記錄：

圖片

這意味著下一次插入新記錄時(shí)，自增主鍵的值會(huì)比之前增加，即 2 已經(jīng)被用過(guò)了，雖然沒(méi)插入成功，但是新的記錄就直接用 3 了。