程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> 深入mysql ON DUPLICATE KEY UPDATE 語法的分析

深入mysql ON DUPLICATE KEY UPDATE 語法的分析

編輯:關於MYSQL數據庫

    mysql "ON DUPLICATE KEY UPDATE" 語法
    如果在INSERT語句末尾指定了ON DUPLICATE KEY UPDATE,並且插入行後會導致在一個UNIQUE索引或PRIMARY KEY中出現重復值,則在出現重復值的行執行UPDATE;如果不會導致唯一值列重復的問題,則插入新行。
    例如,如果列 a 為 主鍵 或 擁有UNIQUE索引,並且包含值1,則以下兩個語句具有相同的效果:

    復制代碼 代碼如下:
    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。
    這個語法還可以這樣用:
    如果INSERT多行記錄(假設 a 為主鍵或 a 是一個 UNIQUE索引列):

    復制代碼 代碼如下:
    INSERT INTO TABLE (a,c) VALUES (1,3),(1,7) ON DUPLICATE KEY UPDATE c=c+1;


    執行後, c 的值會變為 4 (第二條與第一條重復, c 在原值上+1).

    復制代碼 代碼如下:
    INSERT INTO TABLE (a,c) VALUES (1,3),(1,7) ON DUPLICATE KEY UPDATE c=VALUES(c);


    執行後, c 的值會變為 7 (第二條與第一條重復, c 在直接取重復的值7).
    注意:ON DUPLICATE KEY UPDATE只是MySQL的特有語法,並不是SQL標准語法!
    這個語法和適合用在需要 判斷記錄是否存在,不存在則插入存在則更新的場景.

    INSERT INTO .. ON DUPLICATE KEY更新多行記錄
    如 果在INSERT語句末尾指定了ON DUPLICATE KEY UPDATE,並且插入行後會導致在一個UNIQUE索引或PRIMARY KEY中出現重復值,則執行舊行UPDATE;如果不會導致唯一值列重復的問題,則插入新行。例如,如果列a被定義為UNIQUE,並且包含值1,則以下 兩個語句具有相同的效果:

    復制代碼 代碼如下:
    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。
    如果你想了解更多關於INSERT INTO .. ON DUPLICATE KEY的功能說明,詳見MySQL參考文檔:13.2.4. INSERT語法

    現 在問題來了,如果INSERT多行記錄, ON DUPLICATE KEY UPDATE後面字段的值怎麼指定?要知道一條INSERT語句中只能有一個ON DUPLICATE KEY UPDATE,到底他會更新一行記錄,還是更新所有需要更新的行。這個問題困擾了我很久了,其實使用VALUES()函數一切問題都解決了。

    舉個例子,字段a被定義為UNIQUE,並且原數據庫表table中已存在記錄(2,2,9)和(3,2,1),如果插入記錄的a值與原有記錄重復,則更新原有記錄,否則插入新行:

    復制代碼 代碼如下:
    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語句的執行,發現(2,5,7)中的a與原有記錄(2,2,9)發生唯一值沖突,則執行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標准語法!

    1. 上一頁:
    2. 下一頁:
    Copyright © 程式師世界 All Rights Reserved