はじめに
情報システム部の横山です。
MySQLでバルクアップデートをしたいことはままあります。そこで使用するのがこのINSERT ... ON DUPLICATE KEY UPDATEなのですが、Web上では「どうやってアップデート元のレコードが決定されるのか」まで触れた使い方の記事が見つけられなかったので、自分の備忘録も兼ねて書くことにしました。
バージョン
MySQL 5.7.36で動作確認しています
なぜ一括でUPDATEをするべきなのか
理由はシンプルで、個別のUPDATE文を発行するよりもパフォーマンスが良いからです。
- DB-クライアント間で毎回通信しなくて済む
- 1クエリで更新すれば、構文のパースもインデックスファイルの更新も1回で済む
構文の概要
INSERT ... ON DUPLICATE KEY UPDATE を端的に説明すると以下のように3つの文で説明できます。使う際はこの3つを必ず押さえてください。
- 既にあったらUPDATEで、まだなかったらINSERT、つまりUPSERTの構文。なので、更新したい各行ではINSERTができるだけの情報量が必要。
- UPDATEしたいカラムがどこなのかは、クエリの
ON DUPLICATE KEY UPDATEの後に明示する必要がある - UPDATEする元のレコードがどのレコードなのかは暗黙に決定されるので、クエリに書く必要はない
ではどのように既存のレコードがあるのかを突き合わせているかというと、以下のいずれかで決定されます。
- プライマリキーが同一である
- ユニークキー(複合ユニークでも可)が同一である
よって、この構文を使うためにはテーブルにプライマリキーかユニークキーが設定されている必要があります。 もしそれらのキーが無かったら、MySQLがどこの行をUPDATEしたら良いかが分かりません。
注意点
プライマリキーかユニークキーのいずれかが既存のレコードに合っていれば、UPDATEになってしまいます。 たとえばプライマリキーがもうあるレコードで、かつユニークキーが新しいレコードの場合は、UPDATEとして扱われ、新しいユニークキーは照合時に無視されます。エラーになりません。
逆にユニークキーがもうあるレコードで、かつプライマリキーが新しいレコードの場合でも、UPDATEとして扱われ、新しいプライマリキーは照合時に無視されます。エラーになりません。
照合時に無視されるだけなので、たとえばプライマリキーで照合させてユニークキー側を更新させることも可能です。
この具体例については、「注意すべき挙動」の項目で書いています。
具体例で理解する
例示のための初期データを定義
ユーザーテーブルがあったとします。
注意すべき点はidがプライマリキーになっているところと、emailがユニークキーになっているところです。
-- テーブル定義 CREATE TABLE `users` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL COMMENT '名前', `email` varchar(255) NOT NULL COMMENT 'メールアドレス', `favorite` varchar(255) DEFAULT NULL COMMENT '好きなプログラミング言語', PRIMARY KEY (`id`), UNIQUE KEY `index_users_on_email` (`email`) );
ではいきなり、 INSERT ... ON DUPLICATE KEY UPDATE で初期データを投入しましょう。これは初回なのでINSERTです。
-- 初期データの定義 INSERT INTO `users` ( `id`, `name`, `email`, `favorite` ) VALUES ( 1, '一条太郎', 'taro.ichijo@test.com', 'Ruby' ), ( 2, '二条次郎', 'jiro.nijo@test.com', 'TypeScript' ) ON DUPLICATE KEY UPDATE `users`.`name` = VALUES (`name`), `users`.`favorite` = VALUES (`favorite`);
投入された初期データは以下の通りです。
1 一条太郎 taro.ichijo@test.com Ruby 2 二条次郎 jiro.nijo@test.com TypeScript
クエリに ON DUPLICATE KEY UPDATE をつけたことで、後から name や favorite を適当に更新しても元に戻せるようになっています。
是非実際に動かして確認してみてください。
基本的な挙動
名前を更新してみましょう。ON DUPLICATE KEY UPDATE の後に name を指定しているので、 name カラムに更新が走ります。
INSERT INTO `users` ( `id`, `name`, `email` ) VALUES ( 1, '山田太郎', 'taro.ichijo@test.com' ), ( 2, '佐藤次郎', 'jiro.nijo@test.com' ) ON DUPLICATE KEY UPDATE `users`.`name` = VALUES (`name`);
結果はこうなります。
1 山田太郎 taro.ichijo@test.com Ruby 2 佐藤次郎 jiro.nijo@test.com TypeScript
一方で、ダメな例も示します。
以下は email が入っていないため、 Field 'email' doesn't have a default value でエラーになります。「別に email は更新しないし良いじゃん…」と思うかもしれませんが、この構文は最低限INSERT文として成立していないといけない制約があります。email はデフォルト値もないし NULL も許されないのでエラーになります。
-- エラー INSERT INTO `users` ( `id`, `name` ) VALUES ( 1, '山田太郎' ), ( 2, '佐藤次郎' ) ON DUPLICATE KEY UPDATE `users`.`name` = VALUES (`name`);
それでは今度は name と favorite を両方変えてみますが、 ON DUPLICATE KEY UPDATE の後には name のみの変更を指示します。
INSERT INTO `users` ( `id`, `name`, `email`, `favorite` ) VALUES ( 1, '田中太郎', 'taro.ichijo@test.com', 'Python' ), ( 2, '高橋次郎', 'jiro.nijo@test.com', 'Rust' ) ON DUPLICATE KEY UPDATE `users`.`name` = VALUES (`name`);
結果はこうなります。favorite は変更されません。
1 田中太郎 taro.ichijo@test.com Ruby 2 高橋次郎 jiro.nijo@test.com TypeScript
favoriteも更新したい場合は、ON DUPLICATE KEY UPDATE の後に更新したいカラムを明示する必要があります。
-- (略) ON DUPLICATE KEY UPDATE `users`.`name` = VALUES (`name`), `users`.`favorite` = VALUES (`favorite`);
もちろん明示してあげたら以下のように更新されます。
1 田中太郎 taro.ichijo@test.com Python 2 高橋次郎 jiro.nijo@test.com Rust
注意すべき挙動
では初期状態に戻しましょう。
1 一条太郎 taro.ichijo@test.com Ruby 2 二条次郎 jiro.nijo@test.com TypeScript
もし、プライマリキーとユニークキー（※ここではメールアドレス）で不整合が起きていたら？ 以下の三条さんはプライマリキーが新しい3なのに、メールアドレスが一条さんと同一で、ユニーク制約に違反しています。
INSERT INTO `users` ( `id`, `name`, `email`, `favorite` ) VALUES ( 3, '三条三郎', 'taro.ichijo@test.com', 'C#' ) ON DUPLICATE KEY UPDATE `users`.`name` = VALUES (`name`), `users`.`favorite` = VALUES (`favorite`);
結果はこうです。ユニークキーが優先され、一条さんが更新されて三条さんになってしまいました！ あらやだ！
1 三条三郎 taro.ichijo@test.com C# 2 二条次郎 jiro.nijo@test.com TypeScript
それでは逆に、プライマリキーは既にあるのにユニークキー(メールアドレス)が新しい状態にしてみたら？
INSERT INTO `users` ( `id`, `name`, `email`, `favorite` ) VALUES ( 1, '一条隆', 'takashi.ichijo@test.com', 'Haskell' ) ON DUPLICATE KEY UPDATE `users`.`name` = VALUES (`name`), `users`.`favorite` = VALUES (`favorite`);
おおっと！ またしても更新されてしまったようです。どうも、プライマリキーかユニークキーのいずれかが合っていれば、そのレコードを更新してしまうようです。
1 一条隆 taro.ichijo@test.com Haskell 2 二条次郎 jiro.nijo@test.com TypeScript
では更に、プライマリキーもユニークキーも既にあるものだけど、それぞれ別々のレコードを参照している場合はどうなってしまうのでしょうか？
id: 1 は一条さんだし、 email: jiro.nijo@test.com は二条さんです。
INSERT INTO `users` ( `id`, `name`, `email`, `favorite` ) VALUES ( 1, 'キメラ太郎', 'jiro.nijo@test.com', 'Brainfuck' ) ON DUPLICATE KEY UPDATE `users`.`name` = VALUES (`name`), `users`.`favorite` = VALUES (`favorite`);
結果
1 キメラ太郎 taro.ichijo@test.com Brainfuck 2 二条次郎 jiro.nijo@test.com TypeScript
エラーなく更新され、一条さんはキメラになってしまいました。恐ろしいですね。 INSERT ... ON DUPLICATE KEY UPDATE をお使いの際は、よくお気をつけください。
おわりに
INSERT ... ON DUPLICATE KEY UPDATEでは、プライマリキーかユニークキーのいずれかが既存のレコードに合っていれば、UPDATEになってしまいます。そしてプライマリキーとユニークキーで照合可能なレコードが別々の場合は、そのいずれかで更新がされてしまうということが実験によって分かりました。しかし具体的にどのような優先順位で照合がされるかはMySQLのソースコードを読まない限り不明です。
うっかり想定と違うレコードが更新されないよう、不整合なデータを作らないよう、気をつけてこの構文を使っていきましょう。
