【大阪 / 横浜】インフラ / サーバーサイドエンジニア募集中!

【大阪 / 横浜】インフラ / サーバーサイドエンジニア募集中!

【2024年2月~】25年卒 エンジニア新卒採用の募集を開始!

【2024年2月~】25年卒 エンジニア新卒採用の募集を開始!

【導入実績 500社以上】AWS 構築・運用保守・監視サービス

【導入実績 500社以上】AWS 構築・運用保守・監視サービス

【CentOS 後継】AlmaLinux OS サーバー構築・移行サービス

【CentOS 後継】AlmaLinux OS サーバー構築・移行サービス

【WordPress 専用】クラウドサーバー『ウェブスピード』

【WordPress 専用】クラウドサーバー『ウェブスピード』

【格安】Webサイト セキュリティ自動診断「クイックスキャナー」

【格安】Webサイト セキュリティ自動診断「クイックスキャナー」

【低コスト】Wasabi オブジェクトストレージ 構築・運用サービス

【低コスト】Wasabi オブジェクトストレージ 構築・運用サービス

【予約システム開発】EDISONE カスタマイズ開発サービス

【予約システム開発】EDISONE カスタマイズ開発サービス

【100URLの登録が0円】Webサイト監視サービス『Appmill』

【100URLの登録が0円】Webサイト監視サービス『Appmill』

【中国現地企業に対応】中国クラウド / サーバー構築・運用保守

【中国現地企業に対応】中国クラウド / サーバー構築・運用保守

【YouTube】ビヨンド公式チャンネル「びよまるチャンネル」

【YouTube】ビヨンド公式チャンネル「びよまるチャンネル」

そ、そのクエリ、大丈夫? 知らないと損する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は本当に便利ですね。

この記事がお役に立てば【 いいね 】のご協力をお願いいたします!
4
読み込み中...
4 票, 平均: 1.00 / 14
18,700
X facebook はてなブックマーク pocket
【2024.6.30 CentOS サポート終了】CentOS サーバー移行ソリューション

【2024.6.30 CentOS サポート終了】CentOS サーバー移行ソリューション

【2024年2月~】25年卒 エンジニア新卒採用の募集を開始いたします!

【2024年2月~】25年卒 エンジニア新卒採用の募集を開始いたします!

【大阪 / 横浜】インフラエンジニア・サーバーサイドエンジニア 積極採用中!

【大阪 / 横浜】インフラエンジニア・サーバーサイドエンジニア 積極採用中!

この記事をかいた人

About the author

萬代陽一

ソーシャルゲームのウェブ API などの開発がメイン業務ですが、ありがたいことにマーケティングなどいろんな仕事をさせてもらえています。
なおビヨンド内での私の肖像権は CC0 扱いになっています。