INSERT…ON DUPLICATE KEY UPDATE

Posted by muchag | MySQL | 2015-02-09 (月) 16:07:51

ON DUPLICATE KEY UPDATE という、すごく便利な命令を随分前に知って使っていたけど、まだまだ奥が深かったので、メモ。

【環境】
[MySQL] 5.5.27
基本
  1. INSERT INTO table_name (id, name)
  2.     VALUES (val1, val2)
  3.     ON DUPLICATE KEY UPDATE name = val2;

これで
・新しいレコードを挿入してね
・値は、id が val1 で、name は val2 でお願いね
・でももし val1 という値が id に既にあったら、そのレコードの name を val2 で上書きしてね
というお願いになる。
便利だよね~。
事前に存在を確認して、あれば UPDATE、なければ INSERT を発行する、という手間がなくなった。
 

DUPLICATE KEY

ここで「でももし val1 という値が id に既にあったら」この部分について。

特定のキーとは、Uniqueキーとして登録してあるか、Primary Keyとして登録してある必要がありますが、1つのカラムに限らず、複数のカラムでも一つの Uniqueキー(Primary Key)の設定を行うことで対応が可能です。
 
ただ、Uniqueキーの設定が必要ですので、設定するカラムで複数レコードが存在するような仕組みの場合は利用することはできません。

— snip —

「Unique」は、nullは重複しないと見なします。
「Primary Key」は、nullは重複するとみなすため、nullを入れようとするとエラーになります。

有限会社 エス技研:Insert On Duplicate Key Update構文の使い方

ふむふむ。キー周りの知識が怪しいので、ひとまずはこれにて。
 

式も入れられる

参考サイトを読み取っただけで、実証していない。

  1. INSERT INTO table_name (id, name, point)
  2.     VALUES (val1, val2, val3)
  3.     ON DUPLICATE KEY UPDATE name = val2, point = val3;

このとき

  1. INSERT INTO table_name (id, name, point)
  2.     VALUES (val1, val2, val3)
  3.     ON DUPLICATE KEY UPDATE name = val2, point = point + VALUES(val3);

このようにすることで、id が被った場合は、point に val3 を上書きするのではなく、元の値に val3 を加算して上書きする。

  1. INSERT INTO table_name (id, name, point)
  2.     VALUES (val1, val2, val3)
  3.     ON DUPLICATE KEY UPDATE name = val2, point = point + 10;

こうすると、元の値に 10 を加算して上書きする。(新しい値 val3 は無視)
すごい。。。でも、少し複雑になってきた。
 

条件式

CASE や IF も使えるらしい・・・。
すぐにはわからないので、丸コピー。

DUPLICATE KEY UPDATE にはCASEやIFが使えるので、フィールド毎に更新条件を設定できる。
デフォルト値を使って値を更新されないようにもできる。

Qiita:MySQL: INSERT…ON DUPLICATE KEY UPDATEまとめ

  1. INSERT INTO
  2.        table (user_id, fb_like, tweet, updated)
  3.    VALUES
  4.        (1, 12, 10, NOW()), (2, 8, 9, NOW())...()...,(1000, 42, 30, NOW())
  5.    ON DUPLICATE KEY UPDATE
  6.        fb_like = IF(fb_like > VALUES(fb_like), fb_like, VALUES(fb_like))
  7.        , tweet = IF(tweet > VALUES(tweet), tweet, VALUES(tweet))
  8.         , updated = NOW()

 

プリペアドステートメント

プリペアドステートメントを利用する場合は、下記のように記述。

  1. INSERT INTO table_name (id, name, point)
  2.     VALUES (?,?,?)
  3.     ON DUPLICATE KEY UPDATE name = VALUES(name), point = VALUES(point);

 

REPLACE

同じ結果を得られるものとして、REPLACE 文がある。
両者の違いは、下記の通りらしい。

ON DUPLICATE KEY UPDATE:UPDATE
Replace:DELETE & INSERT

 

参考サイト

Qiita:MySQL: INSERT…ON DUPLICATE KEY UPDATEまとめ
極上の人生:MySQL でプレースホルダを使ったINSERT … ON DUPLICATE KEY UPDATE 文を書く
有限会社 エス技研:Insert On Duplicate Key Update構文の使い方

MySQL | 2015-02-09 (月) 16:07:51 |

コメントはまだありません »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a comment