一：主鍵索引，唯一索引和普通索引的關(guān)系

主鍵索引

主鍵索引是唯一索引的特殊類型。 
數(shù)據(jù)庫表通常有一列或列組合，其值用來唯一標(biāo)識(shí)表中的每一行。該列稱為表的主鍵。 
在數(shù)據(jù)庫關(guān)系圖中為表定義一個(gè)主鍵將自動(dòng)創(chuàng)建主鍵索引，主鍵索引是唯一索引的特殊類型。主鍵索引要求主鍵中的每個(gè)值是唯一的。當(dāng)在查詢中使用主鍵索引時(shí)，它還允許快速訪問數(shù)據(jù)。主鍵索引不能為空。每個(gè)表只能有一個(gè)主鍵

唯一索引：

不允許兩行具有相同的索引值。但可以都為NULL，筆者親試。 
如果現(xiàn)有數(shù)據(jù)中存在重復(fù)的鍵值，則數(shù)據(jù)庫不允許將新創(chuàng)建的唯一索引與表一起保存。當(dāng)新數(shù)據(jù)將使表中的鍵值重復(fù)時(shí)，數(shù)據(jù)庫也拒絕接受此數(shù)據(jù)。每個(gè)表可以有多個(gè)唯一索引

普通索引：

一般的索引結(jié)構(gòu)，可以在條件刪選時(shí)加快查詢效率，索引字段的值可以重復(fù)，可以為空值

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

上面介紹了索引的知識(shí)，是為了介紹這個(gè)ON DUPLICATE KEY UPDATE功能做鋪墊。

1：ON DUPLICATE KEY UPDATE功能介紹：

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

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

這樣寫在大多數(shù)情況下可以滿足我們的需求，但是會(huì)有兩個(gè)問題。

①性能帶來開銷，尤其是系統(tǒng)比較大的時(shí)候。

②在高并發(fā)的情況下會(huì)出現(xiàn)錯(cuò)誤，可能需要利用事務(wù)保證安全。

有沒有一種優(yōu)雅的寫法來實(shí)現(xiàn)有則更新，沒有則插入的寫法呢？ON DUPLICATE KEY UPDATE提供了這樣的一個(gè)方式。

2：ON DUPLICATE KEY UPDATE測(cè)試樣例+總結(jié)：

首先我們了解下這個(gè)簡(jiǎn)單的表結(jié)構(gòu)id(主鍵)、code、name。

看下表中現(xiàn)有的數(shù)據(jù)：

執(zhí)行以下實(shí)驗(yàn)進(jìn)行分析：

實(shí)驗(yàn)一：含有ON DUPLICATE KEY UPDATE的INSERT語句中包含主鍵：

①插入更新都失敗，原因是因?yàn)榘阎麈Iid改成了已經(jīng)存在的id

②執(zhí)行更新操作。這里的數(shù)據(jù)還是四條。不過第四條的id由75變化為85

③執(zhí)行更新操作。數(shù)據(jù)總量是四條

④insert語句中未包含主鍵，執(zhí)行插入操作。數(shù)據(jù)量變?yōu)?條

實(shí)驗(yàn)二：含有ON DUPLICATE KEY UPDATE的INSERT語句中包含唯一索引：

表結(jié)構(gòu)中增加code的唯一索引，表中現(xiàn)有的數(shù)據(jù)：

①插入更新都失敗，原因是因?yàn)榘裞ode改成了已經(jīng)存在的code值

②執(zhí)行更新操作。這里的數(shù)據(jù)總量為5條。不過第五條的code由1000變化為1200

③執(zhí)行更新操作。數(shù)據(jù)總量五條，沒有變化

④insert語句中未包含唯一索引，執(zhí)行插入操作。數(shù)據(jù)量變?yōu)?條

總結(jié)：

1：ON DUPLICATE KEY UPDATE需要有在INSERT語句中有存在主鍵或者唯一索引的列，并且對(duì)應(yīng)的數(shù)據(jù)已經(jīng)在表中才會(huì)執(zhí)行更新操作。而且如果要更新的字段是主鍵或者唯一索引，不能和表中已有的數(shù)據(jù)重復(fù)，否則插入更新都失敗。

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

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

到此這篇關(guān)于SQL語句中的ON DUPLICATE KEY UPDATE使用的文章就介紹到這了,更多相關(guān)SQL ON DUPLICATE KEY UPDATE內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論