SQL語句中的ON DUPLICATE KEY UPDATE使用

Posted on by

一：主鍵索引，唯一索引和普通索引的關系

主鍵索引

主鍵索引是唯一索引的特殊類型。 
數據庫表通常有一列或列組合，其值用來唯一標識表中的每一行。該列稱為表的主鍵。 
在數據庫關系圖中為表定義一個主鍵將自動創建主鍵索引，主鍵索引是唯一索引的特殊類型。主鍵索引要求主鍵中的每個值是唯一的。當在查詢中使用主鍵索引時，它還允許快速訪問數據。主鍵索引不能為空。每個表隻能有一個主鍵

唯一索引：

不允許兩行具有相同的索引值。但可以都為NULL，筆者親試。 
如果現有數據中存在重復的鍵值，則數據庫不允許將新創建的唯一索引與表一起保存。當新數據將使表中的鍵值重復時，數據庫也拒絕接受此數據。每個表可以有多個唯一索引

普通索引：

一般的索引結構，可以在條件刪選時加快查詢效率，索引字段的值可以重復，可以為空值

二：ON DUPLICATE KEY UPDATE使用測試(MYSQL下的Innodb引擎)

上面介紹瞭索引的知識，是為瞭介紹這個ON DUPLICATE KEY UPDATE功能做鋪墊。

1：ON DUPLICATE KEY UPDATE功能介紹：

有時候由於業務需求，可能需要先去根據某一字段值查詢數據庫中是否有記錄，有則更新，沒有則插入。你可能是下面這樣寫的

if not exists (select node_name from node_status where node_name = target_name)
      insert into node_status(node_name,ip,...) values('target_name','ip',...)
else
      update node_status set ip = 'ip',site = 'site',... where node_name = target_name

這樣寫在大多數情況下可以滿足我們的需求，但是會有兩個問題。

①性能帶來開銷，尤其是系統比較大的時候。

②在高並發的情況下會出現錯誤，可能需要利用事務保證安全。

有沒有一種優雅的寫法來實現有則更新，沒有則插入的寫法呢？ON DUPLICATE KEY UPDATE提供瞭這樣的一個方式。

2：ON DUPLICATE KEY UPDATE測試樣例+總結：

首先我們瞭解下這個簡單的表結構id(主鍵)、code、name。

看下表中現有的數據：

執行以下實驗進行分析：

實驗一：含有ON DUPLICATE KEY UPDATE的INSERT語句中包含主鍵：

①插入更新都失敗，原因是因為把主鍵id改成瞭已經存在的id

 

②執行更新操作。這裡的數據還是四條。不過第四條的id由75變化為85

③執行更新操作。數據總量是四條

④insert語句中未包含主鍵，執行插入操作。數據量變為5條

實驗二：含有ON DUPLICATE KEY UPDATE的INSERT語句中包含唯一索引：

表結構中增加code的唯一索引，表中現有的數據：

①插入更新都失敗，原因是因為把code改成瞭已經存在的code值

②執行更新操作。這裡的數據總量為5條。不過第五條的code由1000變化為1200

 

③執行更新操作。數據總量五條，沒有變化

④insert語句中未包含唯一索引，執行插入操作。數據量變為6條

總結：

1：ON DUPLICATE KEY UPDATE需要有在INSERT語句中有存在主鍵或者唯一索引的列，並且對應的數據已經在表中才會執行更新操作。而且如果要更新的字段是主鍵或者唯一索引，不能和表中已有的數據重復，否則插入更新都失敗。

2：不管是更新還是增加語句都不允許將主鍵或者唯一索引的對應字段的數據變成表中已經存在的數據。

最後感謝博主文章：MySQL：插入更新語句ON DUPLICATE KEY UPDATE

到此這篇關於SQL語句中的ON DUPLICATE KEY UPDATE使用的文章就介紹到這瞭,更多相關SQL ON DUPLICATE KEY UPDATE內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：