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

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

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

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

• 表必須具有主鍵或唯一索引；
• 插入的數(shù)據(jù)必須包含主鍵或唯一索引列；
• 主鍵或唯一索引列的值不能為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語句，如果數(shù)據(jù)庫中已存在具有相同唯一索引或主鍵的記錄，則更新該記錄。

其底層原理和執(zhí)行流程如下：

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

相似SQL

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

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

淺談主鍵跳躍

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

這是因?yàn)镸ySQL在嘗試插入新記錄時(shí)，會(huì)先分配一個(gè)新的自增主鍵值，無論后續(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í)行以下語句：

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)被用過了，雖然沒插入成功，但是新的記錄就直接用3了。

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論