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

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

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

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

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

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

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 が増えなければもっとスマートなのになぁっと。

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