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

MySQL でデータを挿入する時に、重複するレコードが存在すれば UPDATE、無ければ INSERT みたいな事をしたい場合、REPLACE を使えばいいと思っていたのですが、色々と調べてみると「INSERT ... ON DUPLICATE KEY UPDATE 構文」なるものがあって、どちらかと言うとそちらの方が期待している動作に近いことが分かったので、色々と試してみました。

簡単に言うと REPLACE と INSERT ... 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 のイメージで考えていたのですが、まぁ置換ですからね、レコード自体が置き換わってしまうようです。

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

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

まぁ、普通のテーブルだと REPLACE よりも 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 を実行している感じなのかなぁっと。
で、失敗したら update を実行する感じなのかも知れないですね。

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

特に PHP とかで SQL文を生成して実行しているような場合、そもそも PHP 側で既存のレコードが有るか無いかの判定を行えば良い訳で、そもそも INSERT ... ON DUPLICATE KEY UPDATE を使う必要なんて無い訳ですよ。（処理の速さとかの考察が必要な場合は除外して）

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

ただ、何となく id が増えなければもっとスマートなのになぁっと。
ソコだけ何か引っかかって、結局今のところ採用してない構文です。