深入mysql "ON DUPLICATE KEY UPDATE" 語法的分析
更新時(shí)間：2013年06月29日 10:49:33 作者：
本篇文章是對mysql "ON DUPLICATE KEY UPDATE"語法進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下
mysql "ON DUPLICATE KEY UPDATE" 語法
如果在INSERT語句末尾指定了ON DUPLICATE KEY UPDATE，并且插入行后會(huì)導(dǎo)致在一個(gè)UNIQUE索引或PRIMARY KEY中出現(xiàn)重復(fù)值，則在出現(xiàn)重復(fù)值的行執(zhí)行UPDATE；如果不會(huì)導(dǎo)致唯一值列重復(fù)的問題，則插入新行。
例如，如果列 a 為 主鍵 或 擁有UNIQUE索引，并且包含值1，則以下兩個(gè)語句具有相同的效果：
INSERT INTO TABLE (a,c) VALUES (1,3) ON DUPLICATE KEY UPDATE c=c+1;
UPDATE TABLE SET c=c+1 WHERE a=1;
如果行作為新記錄被插入，則受影響行的值顯示1；如果原有的記錄被更新，則受影響行的值顯示2。
這個(gè)語法還可以這樣用:
如果INSERT多行記錄(假設(shè) a 為主鍵或 a 是一個(gè) UNIQUE索引列):
INSERT INTO TABLE (a,c) VALUES (1,3),(1,7) ON DUPLICATE KEY UPDATE c=c+1;
執(zhí)行后, c 的值會(huì)變?yōu)?4 (第二條與第一條重復(fù), c 在原值上+1).
INSERT INTO TABLE (a,c) VALUES (1,3),(1,7) ON DUPLICATE KEY UPDATE c=VALUES(c);
執(zhí)行后, c 的值會(huì)變?yōu)?7 (第二條與第一條重復(fù), c 在直接取重復(fù)的值7).
注意：ON DUPLICATE KEY UPDATE只是MySQL的特有語法，并不是SQL標(biāo)準(zhǔn)語法！
這個(gè)語法和適合用在需要 判斷記錄是否存在,不存在則插入存在則更新的場景.
INSERT INTO .. ON DUPLICATE KEY更新多行記錄
如果在INSERT語句末尾指定了ON DUPLICATE KEY UPDATE，并且插入行后會(huì)導(dǎo)致在一個(gè)UNIQUE索引或PRIMARY KEY中出現(xiàn)重復(fù)值，則執(zhí)行舊行UPDATE；如果不會(huì)導(dǎo)致唯一值列重復(fù)的問題，則插入新行。例如，如果列a被定義為UNIQUE，并且包含值1，則以下兩個(gè)語句具有相同的效果：
INSERT INTO TABLE (a,b,c)
VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
UPDATE TABLE SET c=c+1 WHERE a=1;
如果行作為新記錄被插入，則受影響行的值為1；如果原有的記錄被更新，則受影響行的值為2。
如果你想了解更多關(guān)于INSERT INTO .. ON DUPLICATE KEY的功能說明，詳見MySQL參考文檔：13.2.4. INSERT語法
現(xiàn)在問題來了，如果INSERT多行記錄， ON DUPLICATE KEY UPDATE后面字段的值怎么指定？要知道一條INSERT語句中只能有一個(gè)ON DUPLICATE KEY UPDATE，到底他會(huì)更新一行記錄，還是更新所有需要更新的行。這個(gè)問題困擾了我很久了，其實(shí)使用VALUES()函數(shù)一切問題都解決了。
舉個(gè)例子，字段a被定義為UNIQUE，并且原數(shù)據(jù)庫表table中已存在記錄(2,2,9)和(3,2,1)，如果插入記錄的a值與原有記錄重復(fù)，則更新原有記錄，否則插入新行：
INSERT INTO TABLE (a,b,c) VALUES
(1,2,3),
(2,5,7),
(3,3,6),
(4,8,2)
ON DUPLICATE KEY UPDATE b=VALUES(b);
以上SQL語句的執(zhí)行，發(fā)現(xiàn)(2,5,7)中的a與原有記錄(2,2,9)發(fā)生唯一值沖突，則執(zhí)行ON DUPLICATE KEY UPDATE，將原有記錄(2,2,9)更新成(2,5,9)，將(3,2,1)更新成(3,3,1)，插入新記錄(1,2,3)和(4,8,2)
注意：ON DUPLICATE KEY UPDATE只是MySQL的特有語法，并不是SQL標(biāo)準(zhǔn)語法！
如果在INSERT語句末尾指定了ON DUPLICATE KEY UPDATE，并且插入行后會(huì)導(dǎo)致在一個(gè)UNIQUE索引或PRIMARY KEY中出現(xiàn)重復(fù)值，則在出現(xiàn)重復(fù)值的行執(zhí)行UPDATE；如果不會(huì)導(dǎo)致唯一值列重復(fù)的問題，則插入新行。
例如，如果列 a 為 主鍵 或 擁有UNIQUE索引，并且包含值1，則以下兩個(gè)語句具有相同的效果：
復(fù)制代碼 代碼如下:
INSERT INTO TABLE (a,c) VALUES (1,3) ON DUPLICATE KEY UPDATE c=c+1;
UPDATE TABLE SET c=c+1 WHERE a=1;
如果行作為新記錄被插入，則受影響行的值顯示1；如果原有的記錄被更新，則受影響行的值顯示2。
這個(gè)語法還可以這樣用:
如果INSERT多行記錄(假設(shè) a 為主鍵或 a 是一個(gè) UNIQUE索引列):
復(fù)制代碼 代碼如下:
INSERT INTO TABLE (a,c) VALUES (1,3),(1,7) ON DUPLICATE KEY UPDATE c=c+1;
執(zhí)行后, c 的值會(huì)變?yōu)?4 (第二條與第一條重復(fù), c 在原值上+1).
復(fù)制代碼 代碼如下:
INSERT INTO TABLE (a,c) VALUES (1,3),(1,7) ON DUPLICATE KEY UPDATE c=VALUES(c);
執(zhí)行后, c 的值會(huì)變?yōu)?7 (第二條與第一條重復(fù), c 在直接取重復(fù)的值7).
注意：ON DUPLICATE KEY UPDATE只是MySQL的特有語法，并不是SQL標(biāo)準(zhǔn)語法！
這個(gè)語法和適合用在需要 判斷記錄是否存在,不存在則插入存在則更新的場景.
INSERT INTO .. ON DUPLICATE KEY更新多行記錄
如果在INSERT語句末尾指定了ON DUPLICATE KEY UPDATE，并且插入行后會(huì)導(dǎo)致在一個(gè)UNIQUE索引或PRIMARY KEY中出現(xiàn)重復(fù)值，則執(zhí)行舊行UPDATE；如果不會(huì)導(dǎo)致唯一值列重復(fù)的問題，則插入新行。例如，如果列a被定義為UNIQUE，并且包含值1，則以下兩個(gè)語句具有相同的效果：
復(fù)制代碼 代碼如下:
INSERT INTO TABLE (a,b,c)
VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
UPDATE TABLE SET c=c+1 WHERE a=1;
如果行作為新記錄被插入，則受影響行的值為1；如果原有的記錄被更新，則受影響行的值為2。
如果你想了解更多關(guān)于INSERT INTO .. ON DUPLICATE KEY的功能說明，詳見MySQL參考文檔：13.2.4. INSERT語法
現(xiàn)在問題來了，如果INSERT多行記錄， ON DUPLICATE KEY UPDATE后面字段的值怎么指定？要知道一條INSERT語句中只能有一個(gè)ON DUPLICATE KEY UPDATE，到底他會(huì)更新一行記錄，還是更新所有需要更新的行。這個(gè)問題困擾了我很久了，其實(shí)使用VALUES()函數(shù)一切問題都解決了。
舉個(gè)例子，字段a被定義為UNIQUE，并且原數(shù)據(jù)庫表table中已存在記錄(2,2,9)和(3,2,1)，如果插入記錄的a值與原有記錄重復(fù)，則更新原有記錄，否則插入新行：
復(fù)制代碼 代碼如下:
INSERT INTO TABLE (a,b,c) VALUES
(1,2,3),
(2,5,7),
(3,3,6),
(4,8,2)
ON DUPLICATE KEY UPDATE b=VALUES(b);
以上SQL語句的執(zhí)行，發(fā)現(xiàn)(2,5,7)中的a與原有記錄(2,2,9)發(fā)生唯一值沖突，則執(zhí)行ON DUPLICATE KEY UPDATE，將原有記錄(2,2,9)更新成(2,5,9)，將(3,2,1)更新成(3,3,1)，插入新記錄(1,2,3)和(4,8,2)
注意：ON DUPLICATE KEY UPDATE只是MySQL的特有語法，并不是SQL標(biāo)準(zhǔn)語法！
相關(guān)文章
這篇文章主要為大家詳細(xì)介紹了mysql5.6.zip格式壓縮版安裝圖文教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2018-12-12
這篇文章主要介紹了詳解Mysql 30條軍規(guī),文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2019-08-08
探討:sql插入空,默認(rèn)1900-01-01 00:00:00.000的解決方法詳解
本篇文章是對sql插入空,默認(rèn)1900-01-01 00:00:00.000的解決方法進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
淺談選擇mysql存儲(chǔ)引擎的標(biāo)準(zhǔn)
本文介紹了如何選擇mysql存儲(chǔ)引擎，從存儲(chǔ)引擎的介紹、幾個(gè)常用引擎的特點(diǎn)三個(gè)方面進(jìn)行講解，感興趣的小伙伴們可以參考一下2015-07-07
使用xshell實(shí)現(xiàn)代理功能并navicat?for?MySQL?進(jìn)行測試
本文介紹使用xshell實(shí)現(xiàn)代理功能并使用navicat?for?MySQL進(jìn)行測試,文章主要利用SSH連接工具xshell就可以實(shí)現(xiàn)簡單的代理功能，下面實(shí)現(xiàn)過程，需要的小伙伴可以參考一下2022-02-02
mysql中g(shù)rant?all?privileges?on賦給用戶遠(yuǎn)程權(quán)限方式
這篇文章主要介紹了mysql中g(shù)rant?all?privileges?on賦給用戶遠(yuǎn)程權(quán)限方式，具有很好的參考價(jià)值，希望對大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-04-04
我們在創(chuàng)建表的過程中難免會(huì)考慮不周，因此后期會(huì)修改表。本篇文章就介紹了在mysql中修改表的實(shí)現(xiàn)方法。需要的朋友參考下2013-05-05
MySQL 8.0.18 Hash Join不支持left/right join左右連接問題
在MySQL 8.0.18中，增加了Hash Join新功能，它適用于未創(chuàng)建索引的字段，做等值關(guān)聯(lián)查詢。這篇文章給大家介紹MySQL 8.0.18 Hash Join不支持left/right join左右連接，感興趣的朋友一起看看吧
2019-11-11 
我們知道,一個(gè)服務(wù)器開放N個(gè)鏈接給客戶端連接的,就會(huì)有大并發(fā)的更新操作,但是從服務(wù)器的里面讀取binlog線程得僅有一個(gè),當(dāng)某個(gè)SQL在從服務(wù)器上執(zhí)行的時(shí)間稍長?或由某個(gè)SQL要鎖表就會(huì)導(dǎo)致主服務(wù)器的SQL大量積壓，未被同步到從服務(wù)器,就導(dǎo)致了主從不一致,也就是主從延遲
2022-01-01