深度剖析onduplicate

在MySQL中，onduplicate被廣泛使用來處理對於重複的主鍵或唯一索引值。具體來說，它通過一條INSERT語句，當有重複的主鍵或唯一的索引出現時，會執行下一步操作。常見的有覆蓋（insert into… onduplicate key do nothing）,更新（insert into… onduplicate key update ）等操作

一、onduplicate key do not

當主鍵或唯一索引重複時,INSERT語句將不會進行任何操作，並忽略這一行的插入

insert into table_name (primary_key, column1, column2, ...) values (value1, value2, value3, ...) 
onduplicate key do nothing

實現上，ONDUP_HANDLERR_FUNC 類型的函數do_nothing_handler()以及do_nothing()函數會被調用, 在do_nothing_handler()函數中什麼都沒有做，因此會直接跳出函數。

二、onduplicate key update where

當主鍵或唯一索引重複時，MySQL實現了相關的Updating機制。以onduplicate key update where這條語句為例, 當插入的主鍵重複時，會根據where分句指定的條件來更新。舉個例子，假設存在主鍵為id的一條記錄，需要更新column1的值。可以用以下代碼完成：

insert into table_name (primary_key, column1, column2, ...) 
values (value1, value2, value3, ...) 
onduplicate key update set column1=new_value where id=primary_key_value

在執行這個語句的時候，MySQL會先檢查表中是否已經有了值為primary_key_value的行。如果有的話，MySQL會更新對應行的column1的值為new_value。如果沒有，MySQL會插入一行：

insert into table_name (primary_key, column1, column2, ...)
values (value1, value2, value3, ...)

這個特性可以非常方便地插入帶有唯一性約束的數據，並防止出現衝突。

三、onduplicate key

在執行 INSERT INTO … ON DUPLICATE KEY UPDATE 時，MySQL會在插入過程中根據PRIMARY KEY 或 UNIQUE KEY 約束，檢查新插入的行是否與表中已有的行有重複之處，如果有，則執行UPDATE操作。如果沒有，則執行INSERT操作。

insert into table_name (primary_key, column1, column2, ...) 
values (value1, value2, value3, ...) 
onduplicate key 
column1 = case when values(column1) is null then column1 else values(column1) end, 
column2 = case when values(column2) is null then column2 else values(column2) end

當主鍵或唯一索引重複時，UPDATE語句的作用是將主鍵ID為X的行更新為值為Y的行, 如果不存在這樣的行, INSERT語句插入一條新記錄。

四、onduplicate key update性能

在執行INSERT INTO … ON DUPLICATE KEY UPDATE 的時候，不論數據是插入還是更新，MySQL都會把行鎖定在內存中，直到整個命令完成後釋放。如果並發插入過多會增加鎖超時的風險。因此，在實際需求中需要進行測試。

五、onduplicate key update鎖表

在MySQL 8.0中，當「INSERT ON DUPLICATE KEY UPDATE」查詢在表的行存在重複關鍵字時進行插入和更新並發訪問時存在潛在的鎖競爭，因為此時MySQL會有行級鎖以及語句級鎖的問題。建議在高並發情況下做好數據優化、分片、讀寫分離等處理。

六、onduplicatekeyupdate批量更新

在有些情況下，我們可能會遇到這樣的問題，需要INSERT一組數據，但如果在數據庫中已經存在了相同主鍵的記錄，則需要對這些記錄進行更新操作。此時，比較高效的方法是使用INSERT INTO … ON DUPLICATE KEY UPDATE語句配合VALUES子句，來實現批量更新。具體來說：

INSERT INTO table_name (primary_key, col1, col2, ...)
VALUES
    (1, 'foo', 'bar'),
    (2, 'baz', 'qux')
ON DUPLICATE KEY UPDATE
    col1=VALUES(col1),
    col2=VALUES(col2);

在這個例子中，如果（1，『foo’,’bar’）這個主鍵已經在表中存在，則執行UPDATE操作來更新對應的值。同理，如果表中存在主鍵為2的記錄，則把（2，「baz」、「qux」）這條記錄更新到表中。如果表中原來沒有這兩個主鍵的記錄，則直接執行INSERT操作。

總結

對於大多數數據庫管理系統而言，onduplicate是一項非常重要的性能特性。在MySQL中，這個語法早已經成為了開發者們必須要熟練掌握的知識之一。在實際開發過程中，我們可以根據具體情況來選擇合適的onduplicate處理方式，並且需要注意性能優化、鎖表等問題，以避免在高並發情況下造成數據不一致等問題。