そ、そのクエリ、大丈夫？ 知らないと損するREPLACE句の挙動

こんにちは。開発チームのワイルド担当、まんだいです。
今回は、普段何気なく使っている（かも知れない）REPLACE句について、細かい挙動を追いかけてみたいと思います。

 

データ更新、どうしてますか？

弊社では、システム開発においてMySQLを主なRDBMSとして利用しています。
通常、ユニーク制約があるテーブルへの更新処理の際には

• SELECTして重複チェックを行った上でINSERTするかUPDATEするか分岐させる
• INSERTしてみてDuplicateしたら例外をキャッチしてUPDATE処理に回すワイルド更新

の二択を迫られると思いますが、実は（でもなんでもないですが）REPLACE句による更新処理も存在します。

このREPLACE句ですが、単に字面の通り、置き換える（= REPLACE）すると思って実行すると、データの不整合が発生して思わぬ事態を招くやも知れません。

簡単な例で、その挙動を見ていきます。

実際に動きを見てみよう

まず、以下の様なテーブルを作成します。

mysql> CREATE TABLE `users` (`id` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(16) NOT NULL UNIQUE, `level` INT UNSIGNED NOT NULL DEFAULT 1);
Query OK, 0 rows affected (0.02 sec)

mysql> DESC `users`;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(16)      | NO   | UNI | NULL    |                |
| level | int(10) unsigned | NO   |     | 1       |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

nameフィールドをユニークキーに設定しています。
続きまして、簡単なダミーデータを流し込んでいきます。

今回は、generatedata.comを利用して、ダミーデータの生成をしてみました。

以下のような雰囲気になるかと思います。

mysql> INSERT INTO `users` (`name`,`level`) VALUES ("Pamela",8),("Dexter",4),("Ava",1),("Kelly",8),("Leila",2),("Quin",8),("Salvador",8),("Orli",2),("Ignacia",9),("Brennan",8);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM `users`;
+----+----------+-------+
| id | name     | level |
+----+----------+-------+
|  1 | Pamela   |     8 |
|  2 | Dexter   |     4 |
|  3 | Ava      |     1 |
|  4 | Kelly    |     8 |
|  5 | Leila    |     2 |
|  6 | Quin     |     8 |
|  7 | Salvador |     8 |
|  8 | Orli     |     2 |
|  9 | Ignacia  |     9 |
| 10 | Brennan  |     8 |
+----+----------+-------+
10 rows in set (0.00 sec)

ここで、nameが重複するようなレコードをインサートしてみたいと思います。

mysql> INSERT INTO `users` (`name`,`level`) VALUES ("Pamela",1);
ERROR 1062 (23000): Duplicate entry 'Pamela' for key 'name'

mysql> SELECT * FROM `users`;
+----+----------+-------+
| id | name     | level |
+----+----------+-------+
|  1 | Pamela   |     8 |
|  2 | Dexter   |     4 |
|  3 | Ava      |     1 |
|  4 | Kelly    |     8 |
|  5 | Leila    |     2 |
|  6 | Quin     |     8 |
|  7 | Salvador |     8 |
|  8 | Orli     |     2 |
|  9 | Ignacia  |     9 |
| 10 | Brennan  |     8 |
+----+----------+-------+
10 rows in set (0.00 sec)

Duplicateしているのでエラーが発生しました。
予想通りの動きですね。

通常であれば、ここでUPDATE句を使って、レコードを更新をします。

mysql> UPDATE `users` SET `level`=10  WHERE `name` = "Pamela";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from users;
+----+----------+-------+
| id | name     | level |
+----+----------+-------+
|  1 | Pamela   |    10 |
|  2 | Dexter   |     4 |
|  3 | Ava      |     1 |
|  4 | Kelly    |     8 |
|  5 | Leila    |     2 |
|  6 | Quin     |     8 |
|  7 | Salvador |     8 |
|  8 | Orli     |     2 |
|  9 | Ignacia  |     9 |
| 10 | Brennan  |     8 |
+----+----------+-------+
10 rows in set (0.00 sec)

今度はREPLACE句を使って、レコードの更新をしてみます。

mysql> REPLACE INTO `users` (`name`,`level`) VALUES ("Pamela",1);
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM `users`;
+----+----------+-------+
| id | name     | level |
+----+----------+-------+
|  2 | Dexter   |     4 |
|  3 | Ava      |     1 |
|  4 | Kelly    |     8 |
|  5 | Leila    |     2 |
|  6 | Quin     |     8 |
|  7 | Salvador |     8 |
|  8 | Orli     |     2 |
|  9 | Ignacia  |     9 |
| 10 | Brennan  |     8 |
| 12 | Pamela   |     1 |
+----+----------+-------+
10 rows in set (0.00 sec)

この時点で、恐ろしい事が起こった事に気づかれましたか？
そう、どうしてか、name = "Pamela" のレコードのプライマリーキーが変更されているんです！
稲川淳二の怪談並に怖いこの事実を細かく見ていきたいと思います。

REPLACE句が単なる置き換えではない

この事実は先ほどの実験で明らかですが、どうしてこのような事が起こったのか。
ちなみに、ですが、idの11が抜けているのは、INSERT句を実行したからで、これは今回の怪談の内容ではありません。

こうなる理由を端的に言うと

REPLACE句はDELETEした後、「同じデータを持つレコードをINSERTする」

という動きをするからです。
怖いですねぇ。
プライマリーキーが勝手に変わるなんて、怖いですねぇ。
勝手にINSERTかUPDATEか判別してくれる便利なクエリだと思ってバンバン使ってたとしたら
地雷を踏まなかっただけで、気づけば地雷の海の中かも知れません。

とはいえ、この現象が発生するのは、

• オートインクリメントされているプライマリーキーを持ち
• 同時にユニークキーにされているフィールドが存在し
• ユニークキーが重複（Duplicate）した時

に限定されます。
こう書くと、サロゲートキーなんか付けるからだと言う人もおられるようですが、REPLACE句の挙動を知った上で実装しなかったから、というのが正解ではないかと思います。

似たような動作をする構文に「INSERT ... ON DUPLICATE KEY UPDATE 構文」というものがありますが
ステートメントベースのレプリケーションや複合主（一意）キーを持つテーブルに対して使うのは安全ではない
という事ですので、使い方は限定されるのかなと思います。
また、フレームワークのO/Rマッパーも標準で対応していないものが多い（標準SQLではなくMySQLの方言だから）ので使うならO/Rマッパーの恩恵には預かれないという事になります。
とはいえ、ON DUPLICATE KEY UPDATE 構文はSELECT → INSERT(UPDATE)するより格段に高速に処理できるという話もあるようなので
MySQLを使うシステムでデータ更新系の処理に時間がかかっているのなら、敢えて使ってみるのもアリなのかも知れません。

MySQL 5.6系の日本語ドキュメント
MySQL5.7系のドキュメント（英語）
（5.7系の英語版ドキュメントでも内容はだいたい同じです）

また、とりあえずINSERTは、無駄にオートインクリメントをカウントアップしてしまうので
プライマリーキーが枯渇するという悲劇を生むかも知れません。
万全を期すなら、SELECT → INSERT(UPDATE)するのが手堅い、という事なのかなと思います。

いやぁ、generatedata.comは本当に便利ですね。