INSERT…ON DUPLICATE KEY UPDATE
ON DUPLICATE KEY UPDATE という、すごく便利な命令を随分前に知って使っていたけど、まだまだ奥が深かったので、メモ。
[MySQL] 5.5.27
- INSERT INTO table_name (id, name)
- VALUES (val1, val2)
- ON DUPLICATE KEY UPDATE name = val2;
これで
・新しいレコードを挿入してね
・値は、id が val1 で、name は val2 でお願いね
・でももし val1 という値が id に既にあったら、そのレコードの name を val2 で上書きしてね
というお願いになる。
便利だよね～。
事前に存在を確認して、あれば UPDATE、なければ INSERT を発行する、という手間がなくなった。
ここで「でももし val1 という値が id に既にあったら」この部分について。
特定のキーとは、Uniqueキーとして登録してあるか、Primary Keyとして登録してある必要がありますが、1つのカラムに限らず、複数のカラムでも一つの Uniqueキー（Primary Key）の設定を行うことで対応が可能です。
ただ、Uniqueキーの設定が必要ですので、設定するカラムで複数レコードが存在するような仕組みの場合は利用することはできません。— snip —
「Unique」は、nullは重複しないと見なします。
「Primary Key」は、nullは重複するとみなすため、nullを入れようとするとエラーになります。有限会社 エス技研：Insert On Duplicate Key Update構文の使い方
ふむふむ。キー周りの知識が怪しいので、ひとまずはこれにて。
参考サイトを読み取っただけで、実証していない。
- INSERT INTO table_name (id, name, point)
- VALUES (val1, val2, val3)
- ON DUPLICATE KEY UPDATE name = val2, point = val3;
このとき
- INSERT INTO table_name (id, name, point)
- VALUES (val1, val2, val3)
- ON DUPLICATE KEY UPDATE name = val2, point = point + VALUES(val3);
このようにすることで、id が被った場合は、point に val3 を上書きするのではなく、元の値に val3 を加算して上書きする。
- INSERT INTO table_name (id, name, point)
- VALUES (val1, val2, val3)
- ON DUPLICATE KEY UPDATE name = val2, point = point + 10;
こうすると、元の値に 10 を加算して上書きする。（新しい値 val3 は無視）
すごい。。。でも、少し複雑になってきた。
CASE や IF も使えるらしい・・・。
すぐにはわからないので、丸コピー。
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()
プリペアドステートメントを利用する場合は、下記のように記述。
- INSERT INTO table_name (id, name, point)
- VALUES (?,?,?)
- ON DUPLICATE KEY UPDATE name = VALUES(name), point = VALUES(point);
同じ結果を得られるものとして、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構文の使い方