皆さんは、UPSERT処理という言葉をご存知でしょうか。UPDATEとINSERTを合わせた言葉で、INSERTをする際に、すでにそのデータが存在していない場合はINSERTを行って、その行がある場合はUPDATEを行うという処理になります。
今回は、MySQLでUPSERT処理を実現する方法を紹介していきたいと思います。
検証環境
今回はdockerで建てたMySQLを使用します。以下のコマンドでdockerを建ててローカルからアクセスをします。
% docker run --platform linux/x86_64 -p 3307:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest
アクセス方法は以下の通りになります。
% mysql -uroot -pmy-secret-pw -h127.0.0.1 -P3307
執筆時点では、以下の通り8.
mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.31 | +-----------+ 1 row in set (0.01 sec)
データベースを作成してuseしておきましょう。
mysql> CREATE DATABASE michibushin; mysql> use michibushin
今回は、以下のようなサロゲートキーのIDと製作者の名前
mysql> CREATE TABLE `items` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `maker_name` varchar(255) NOT NULL, `item_name` varchar(255) NOT NULL, `quantity` int NOT NULL DEFAULT '0', UNIQUE KEY `id` (`id`), UNIQUE KEY `idx_item_name_maker_name` (`item_name`,`maker_name`) );
プライマリーキーはIDとして、製作者の名前と商品の名前でユニークになるようにindexを貼ってます。
MySQLでUPSERT処理を実現する方法
MySQLでUPSERT処理を実現する方法としては、以下の2個の方法が知られていると思います。
- INSERT ON DUPLICATE KEY UPDATE構文
- REPLACE構文
これらについて、異なる点と注意点に関して、それぞれまとめていきたいと思います。
INSERT ON DUPLICATE KEY UPDATE 構文
UPSERT処理を行うと考えると、一番初めに出てくるのがこの構文だと思います。INSERT時にVALUESで指定した値がかぶっている場合、ON DUPLICATE KEY UPDATEで指定した値をUPDATEするという仕組みになります。
たとえば、次のように木村の作成したケーキを1個増やす場合を考えます。その場合、以下のようなSQLになります。
mysql> INSERT INTO items (maker_name, item_name, quantity) VALUES('木村', 'ケーキ', 1) ON DUPLICATE KEY UPDATE quantity=quantity+1;
これを最初に実行すると以下のようになります。
mysql> SELECT * FROM items; +----+------------+-----------+----------+ | id | maker_name | item_name | quantity | +----+------------+-----------+----------+ | 1 | 木村 | ケーキ | 1 | +----+------------+-----------+----------+ 1 row in set (0.03 sec)
もう一度同一のクエリを実行してみましょう。
mysql> INSERT INTO items (maker_name, item_name, quantity) VALUES('木村', 'ケー キ', 1) ON DUPLICATE KEY UPDATE quantity=quantity+1;
Query OK, 2 rows affected (0.02 sec)
問題なく実行できました。warningも出ていないことが確認できます。続いて結果を確認してみましょう。
mysql> SELECT * FROM items; +----+------------+-----------+----------+ | id | maker_name | item_name | quantity | +----+------------+-----------+----------+ | 1 | 木村 | ケーキ | 2 | +----+------------+-----------+----------+ 1 row in set (0.01 sec)
このように新しい行ができるのではなくて、UPDATEされた結果2が入っていることがわかります。
これは一般的なUPDATE文にも言えることなのですが、今回のquantityカラムのように数値を更新する場合は、直値でUPDATEする値を入れた場合に問題が起こることがあるので注意しましょう。複数のトランザクションが同時に走る場合、別トランザクションで更新されていた時に不整合が起きてしまうため注意しましょう。
注意点：今回 ON DUPLICATE KEY UPDATE構文で更新をかけた際に、結果にQuery OK, 2 rows affected (0.と表示されているところで疑問に思った方もいるかも知れませんが、この構文を使用すると、INSERTしようとした時にAUTO INCREMENTの値を進めてしまいます。これは結果としてUPDATEとなった場合もそうです。これを避けるにはinnodb_
INSERTする時に渡す値は、INSERTに必要なカラムすべて指定する必要があります。あるからといってサボると、NOT NULL制約などがある場合エラーが発生します。
ユニークとなるキーを自動で比較する仕組みなので、それ以外の指定を行うことができません。また、そういったキーが存在しない場合は判定ができないためただのINSERT文として動作します。
また、今回のように複数のユニークキーが存在するときに2つユニークなキーを指定した場合、どちらが優先されるかは不明瞭です。そういったことが起こらないように注意しましょう。
REPLACE構文
続いてREPLACE構文について紹介していきます。REPLACE構文も以下のように使えます。
たとえば、次のようにkimuraの作成したケーキを1個にする場合を考えます。その場合、以下のようなSQLになります。
REPLACE items (maker_name, item_name, quantity) VALUES('kimura', 'ケーキ', 1);
Query OK, 1 row affected (0.05 sec)
これを実行した結果が以下になります。
mysql> SELECT * FROM items; +----+------------+-----------+----------+ | id | maker_name | item_name | quantity | +----+------------+-----------+----------+ | 1 | 木村 | ケーキ | 2 | | 3 | kimura | ケーキ | 1 | +----+------------+-----------+----------+ 2 rows in set (0.01 sec)
以上のように、インサートされていることがわかります。行が存在しない場合に何が指定されるのか疑問に思った方もいると思いますが、デフォルト値と計算結果の値が保存されるため、今回は1が入っています。
続いて次のようにクエリを実行してみましょう。
mysql> REPLACE items (maker_name, item_name, quantity) VALUES('kimura', 'ケーキ', 2);
Query OK, 2 rows affected (0.03 sec)
実行結果の最後が微妙に違ったのがわかるでしょうか？ 何故か2件と出てます。この原因は、REPLACE構文がDELETEしてINSERTする仕組みになっているためです。それを確認するためにSELECTをしてみましょう。
mysql> SELECT * FROM items; +----+------------+-----------+----------+ | id | maker_name | item_name | quantity | +----+------------+-----------+----------+ | 1 | 木村 | ケーキ | 2 | | 4 | kimura | ケーキ | 2 | +----+------------+-----------+----------+ 2 rows in set (0.01 sec)
特に見てほしいところとしては、IDが3から4に変わっていることです。削除して入れ直したため、そのような挙動になってます。
注意点：今回REPLACEする際に、なぜ直値を使ったのかと疑問に思った方もいるかも知れません。では、ON DUPLICATE KEY UPDATEのときのようにquantityを+1するという指定をしてみましょう。
mysql> REPLACE items (maker_name, item_name, quantity) VALUES('kimura', 'ケーキ', quantity+1);
Query OK, 2 rows affected (0.06 sec)
動いていることがわかるのですが、実はこれは問題があります。
mysql> SELECT * FROM items; +----+------------+-----------+----------+ | id | maker_name | item_name | quantity | +----+------------+-----------+----------+ | 1 | 木村 | ケーキ | 2 | | 5 | kimura | ケーキ | 1 | +----+------------+-----------+----------+ 2 rows in set (0.01 sec)
何故か1件になってしまっていると思います。こちら公式のドキュメントにも記載があるのですが、DEAFULT値+1として扱われているため0+1で1となってしまいます。こういった用途で使用するのは厳しい印象があります。
REPLACE構文ですが、見てもらったとおり、重複したものが見つかったらDELETEしてINSERTを行っているため、IDがどんどん更新されてしまっていることがわかります。大抵の場合サロゲートキーのIDを指定するような場合AUTO INCREMENTになっていると思うのですが、そういった場合はどんどんIDが更新されていってしまうため、大量にUPSERTが発生する場合、IDがどんどん更新されて飛んでいってしまいます。加えて、IDが連番で無いと困るような場合には使用できないので注意しましょう。
また、こちらの構文もユニークとなるキーを自動で比較する仕組みなので、それ以外の指定を行うことができません。また、そういったキーが存在しない場合は判定ができないため、ただのINSERT文として動作します。
まとめ
今回は、MySQLでUPSERT処理を実現する方法に関して紹介させていただきました。これ以外にも、実際にはプログラム上でユニークキーに対してINSERTをした際に、発生した例外をキャッチして治すなどの選択があると思います。
それぞれできることや挙動が微妙に異なるので、実現したいことに合わせて適切な処理を選べるようになると、良いかと思います。