INSERT … ON DUPLICATE KEY UPDATE 構文ってどんな場合に使うの?

MySQL でデータを挿入する時に、

重複するレコードが存在すれば UPDATE、無ければ INSERT

みたいな事をしたい場合には、REPLACE を使えばいいと思っていました。
が、調べてみたら「INSERT … ON DUPLICATE KEY UPDATE 構文」なるものがあることが判明。

どちらかと言うと、こちらの方が期待している動作に近いことが分かったので、色々と試してみました。

REPLACE と INSERT … ON DUPLICATE KEY UPDATE の違い

REPLACEINSERT … ON DUPLICATE KEY UPDATE の違いは、既存の重複レコードを削除するかしないかだけなんですが、分かり難いので、以下のようなテーブルがあるとして説明します。

テーブル : USERS

+----+--------+-------+
| id | name   | value |
+----+--------+-------+
| 1  | grimo  | test  |
| 2  | inolog | test  |
+----+--------+-------+

id が主キーで AUTO_INCREMENT、name が UNIQUE キーだとします。

この状態で、新しく blog と言うユーザーを value = test2 として追加する場合
SQL は以下のような感じになります。

REPLACE の場合

REPLACE INTO USERS (name, value) VALUES ('blog', 'test2');

INSERT … ON DUPLICATE KEY UPDATE の場合

INSERT INTO USERS (name, value) VALUES ('blog', 'test2')
ON DUPLICATE KEY UPDATE value = 'test2'

これらは、name が重複していないので普通に INSERT として処理されるので以下の様な結果になります。

+----+--------+-------+
| id | name   | value |
+----+--------+-------+
| 1  | grimo  | test  |
| 2  | inolog | test  |
| 3  | blog   | test2 |
+----+--------+-------+

この時点では REPLACE も INSERT … ON DUPLICATE KEY UPDATE も違いは有りません。

では、次。

name = grimo に value = test3 をセットしようとした場合。

REPLACE の場合、SQL は

REPLACE INTO USERS (name, value) VALUES ('grimo', 'test3');

となり、結果は

+----+--------+-------+
| id | name   | value |
+----+--------+-------+
| 2  | inolog | test  |
| 3  | blog   | test2 |
| 4  | grimo  | test3 |
+----+--------+-------+

となります。
INSERT … ON DUPLICATE KEY UPDATE の場合、SQL は

INSERT INTO USERS (name, value) VALUES ('grimo', 'test3')
ON DUPLICATE KEY UPDATE value = 'test3'

となり、結果は

+----+--------+-------+
| id | name   | value |
+----+--------+-------+
| 1  | grimo  | test3 |
| 2  | inolog | test  |
| 3  | blog   | test2 |
+----+--------+-------+

となります。

違い、わかります?

REPLACE は id が進んでいるのですが、INSERT … ON DUPLICATE KEY UPDATE の場合は id は進んでいません。

つまり、REPLACE の場合、重複レコードが存在する場合には更新するのではなく、既存レコードを削除し、新しくレコードを挿入している訳です。

一方、INSERT … ON DUPLICATE KEY UPDATE は文字通り、重複レコードが存在している場合には単純に値を更新するイメージ。

最初、REPLACE の動作を INSERT … ON DUPLICATE KEY UPDATE のイメージで考えていたのですが、まぁ置換ですからね、レコード自体が置き換わってしまうようです。

REPLACE を使う際の注意点

上記のように単体のテーブルだと分かり難いかも知れませんが、例えば他のテーブルとリレーション張って運用しているような場合、id が連携のキーとなっていたらアウトです。

また、created と modified みたいな感じで新規登録日時と、最終更新日時を記録しているようなテーブルの場合、REPLACE だと基本的に created と modified が同じ値になってしまうので、意図した動作と違うものになってしまうかも知れません。

普通のテーブルだと REPLACE よりも INSERT … ON DUPLICATE KEY UPDATE の方がイメージに合っている場合が多いんじゃないかと思ったりしますが、どうでしょうか?

INSERT … ON DUPLICATE KEY UPDATE の残念なところ

やっと本題です。

こんな感じで INSERT … ON DUPLICATE KEY UPDATE を使ってみたわけですが、何となくしっくりこない動作をするんです。

例えば、先ほどの状態から更に www と言うユーザーを value が test4 とかで追加するとします。

すると、以下の様 SQL 文を実行する感じになるのですが、

INSERT INTO USERS (name, value) VALUES ('www', 'test4')
ON DUPLICATE KEY UPDATE value = 'test4'

結果は以下のような感じになります。

+----+--------+-------+
| id | name   | value |
+----+--------+-------+
| 1  | grimo  | test2 |
| 2  | inolog | test  |
| 3  | blog   | test2 |
| 5  | www    | test4 |
+----+--------+-------+

id が飛んでます。

おそらく grimo を更新する際のクエリで一回 INSERT を実行しているので、その時点で AUTO_INCREMENT値が一つ進んでしまった状態になり、実際には失敗しているので id = 4 は使われず id = 1 の update を実行しているのですが、AUTO_INCREMENT値はそのままになっているので、www の時には id = 5 で INSERT が実行されたのかなと。

まぁ、気にしなくてもいいような事かもしれないですが、スマートな感じはしないですよね。

特に PHP とかで SQL文を生成して実行しているような場合には、事前にレコードの存在確認を行い、INSERT か UPDATE かの分岐を入れることが出来るので、そもそも INSERT … ON DUPLICATE KEY UPDATE を使う必要なんて無い訳ですよ。
(処理の速さとかの考察が必要な場合は除外して)

なので、どんな場合に INSERT … ON DUPLICATE KEY UPDATE を使うのかなぁっと思ったわけですが、書いていて思ったんですけど、やっぱり SQL だけで制御したい場合は便利でしょうね。

ただ、何となく id が増えなければもっとスマートなのになぁっと。

ソコだけ何か引っかかって、結局今のところ採用してない構文です。

このページはINOLOGから移植されました

コメント

  1. ぱむ より:

    とても参考になる記事ありがとうございます!
    ところで、
    >そもそも INSERT … ON DUPLICATE KEY UPDATE を使う必要なんて無い
    とのことですが、
    http://www.24w.jp/blog/?p=32
    上記のような例もあり、やっぱりそれなりの必要性はあるのではないでしょうか。
    たしかにIDが飛んでしまう挙動は謎だしスマートではありませんが、ON DUPLICATE KEY UPDATEに頼る以外の代替手段が無いので仕方ないのかなぁと個人的には思います。
    REPLACEに関してはもう論外というか落とし穴すぎるというか、普通の人が一般的に期待するような動作ではありませんよね。まさか一度DELETEしてるなんて・・・。

  2. 道草 より:

    はむさん
    コメントありがとうございます。
    確かに、このリンクの様に、何らかのカウントを記録する場合には有効ですね。
    この手のカウントを行うような処理の場合にはエラーが起きること自体が問題だと思うので ON DUPLICATE KEY UPDATEが有効だと思います。
    ただ、一般的にインサートやアップデートでエラーを返しても問題ないケースの場合には使わない方が良いのかなと。

  3. ぱむ より:

    ご返信ありがとうございます。
    そうですね、ケースバイケースですよね。
    ところで、
    「ON DUPLICATE KEY UPDATE的な動作」を、
    IDが飛び飛びにならないように実現するには
    どうしたら一番良いと思われますか?
    やっぱりPHPを使って、
    1:まずselectでレコードの存在確認
    2:1で無ければinsert、あればupdate
    というように、2回のクエリに分ける方法しかないでしょうか。
    なんだかスマートではないけれど、他の代替手段が思いつきません。
    そもそもこんなに需要が多そうな機能が、なぜMySQLに標準で備わっていないのか不思議ですね。
    ググってみても同じような悩みがたくさんあるのに・・・。

  4. 道草 より:

    ぱむさん
    そうですね、まさにその手順が一番簡単で一般的だと思います。
    このあたりはDB含めてシステムというか全体の設計なんだよなと思います。
    selectした結果でinsertするかupdateするか決めて実行したとしても、一般的なWEBのシステムであれば問題になるようなことってほとんどないと思いますが、それが問題になる場合にはロックかけるとか、更新処理が並列で走らないようにするしかないでしょうね。
    どちらにしても、replaceがinsertに失敗したらupdateするような仕組みなら良いんですけどね・・・
    痒いところに手が届いてないですね、仕方がないですが。

タイトルとURLをコピーしました