MySQL: INSERT...ON DUPLICATE KEY UPDATEまとめ

  • 223
    いいね
  • 0
    コメント
この記事は最終更新日から1年以上が経過しています。

INSERT...ON DUPLICATE KEY UPDATE構文を使うと

  • レコードがなければINSERT、あればUPDATE
  • 複数行の一括UPDATE
  • フィールド毎に条件判定して更新

を1度のクエリで行うことができる。集計処理などに便利。

基本

  • レコード(行)がなかったらINSERTあったらUPDATEという処理を1クエリで行える。
    • ユニークなフィールドに対して重複する行が挿入される場合はUPDATEという判定
      • なので利用には UNIQUEインデックス(かPRIMARY KEY)を指定する必要 がある
基本例:aはunique
INSERT INTO
       table (a, b, c)
   VALUES
       (1, 12, 100)
   ON DUPLICATE KEY UPDATE
        b = 20
        , c = 200;

a=1の行がなかった場合

a=1,b=12,c=100 の行が追加

a=1の行が既にあった場合

a=1の行がa=1,b=20,c=200に更新

  • VALUES()関数を使うとINSERTに使おうとした値をUPDATEに使える
VALUES()の例
INSERT INTO
       table (a, b)
   VALUES
       (1, 10)
   ON DUPLICATE KEY UPDATE
        b = b + VALUES(b)
a b
1 5

a b
1 15

複数行の一括UPDATE

複数行追加・更新の例
INSERT INTO
       table (a, b, c)
   VALUES
       (1, 10, 100), (2, 20, 200)
   ON DUPLICATE KEY UPDATE
        b = b + 21
        , c = VALUES(c) + 201;

とすると a=1,a=2 に対して追加/更新が行われる。

a b c
1 5 15

a b c
1 26 301
2 20 200

条件にあうフィールドだけ更新

  • DUPLICATE KEY UPDATE にはCASEやIFが使えるので、フィールド毎に更新条件を設定できる。
    • デフォルト値を使って値を更新されないようにもできる。
取得した数字がレコードの数字より大きければ更新
INSERT INTO
       table (user_id, fb_like, tweet, updated)
   VALUES
       (1, 12, 10, NOW()), (2, 8, 9, NOW()) ... (1000, 42, 30, NOW())
   ON DUPLICATE KEY UPDATE
       fb_like = IF(fb_like > VALUES(fb_like), fb_like, VALUES(fb_like))
       , tweet = IF(tweet > VALUES(tweet), tweet, VALUES(tweet))
        , updated = NOW()

SELECT文

INSERT INTO toppings (id, restaurant_id)
SELECT
       t.id
       , t.restaurant_id
    FROM
       toppings AS t
       LEFT OUTER JOIN pizzas AS p
            ON p.id = t.pizza_id
ON DUPLICATE KEY UPDATE
    restaurant_id = IF(p.restaurant_id > 0, p.restaurant_id, 0)