MySQL中insertOrUpdate的功能如何實現的

作者：派大星 2024-06-07 11:48:32

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

insertOrUpdate 在我們日常使用中比較常見，那么它是如何實現的呢，不知道大家有沒有考慮過呢？

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

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

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

舉個例子：

設想有一張 student 表，包括 id、name 和 age 三列，其中 id 是主鍵。現在要插入一條數據，若該數據的主鍵已存在，則更新該數據的姓名和年齡，否則插入該數據。

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

底層實現

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

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

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

在 MySQL 中使用 INSERT ON DUPLICATE KEY UPDATE 語句時，如果插入操作失敗（因為主鍵或唯一鍵沖突），而執行了更新操作，確實會導致自增主鍵計數器增加，即使沒有實際插入新記錄。

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

例如，假設有一個表 test 定義如下：

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

然后執行以下語句：

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

圖片

再執行一次：

圖片

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

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

圖片

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

責任編輯：武曉燕來源：碼上遇見你