MySQL

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

More than 3 years have passed since last update.

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)