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

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

值得留意的是，在出現(xiàn)重復鍵時，會在先前索引值和當前值之間添加臨時鍵鎖，這可能導致死鎖。

若要使用 INSERT INTO ... ON DUPLICATE KEY UPDATE 語句，需滿足以下條件：

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

舉個例子：

設(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;

底層實現(xiàn)

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

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

相似 SQL

除了 INSERT INTO ... ON DUPLICATE KEY UPDATE 之外，還有一些類似的 SQL 語句，比如：

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

淺談主鍵跳躍

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

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

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

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

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

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

圖片

再執(zhí)行一次：

圖片

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

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

圖片

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