MYSQL で複数データを1回のSQL文でまとめてUPDATEする

2023年1月31日

MYSQLを使用したweb制作は、最近では日常茶判事です。
ホームページの本質は「情報伝達」。
ならば、最新の情報をすぐに公開することが求められるからです。

そんななか、webシステムを開発中にふと疑問に思ったことがありました。

「MYSQLのUPDATEを複数データまとめてできないか？」

ということです。
INSERT文では、複数データをまとめて1回のSQLで追加することができます。
しかし、UPDATE文ではまとめて上書きできません。

これって何とかする方法があるのではないか？
そう思い調べてみました。

なぜまとめてUPDATEする必要があるのか？

UPDATEなんて、その都度コールすればいいんじゃないの？
そう思っていました。

事実、今までUPDATE毎にコールしても問題はありませんでした。

ところが今回web制作しているwebシステムで、複数データを同時にUPDATEする機能が必要になりました。

1データごとにUPDATEをコールしていたら、目に見えて処理が遅くなりました。
これではいかんと思いました。

以前CSVで読み込んでMYSQLに登録する処理を作成したときに、INSERTを毎回コールしていたらとてつもなく遅くなったので、１つのSQL文にまとめて1回だけコールするようにしたら驚くほど速くなりました。

だたこれは、INSERT(追加)時の話です。
無論DELETE文でもWHERE句に特定データを複数指定できるので、1回の処理で複数データを削除することができます。

では、UPDATEだけなぜそれができないのでしょうか?

そんなことがきっかけで、複数データまとめてUPDATEできないか調べました。

UPDATE文でのまとめてUPDATEは「同じ内容の上書き」

UPDATE文でもそれなりに複数データの上書きはできます。
WHERE句を無指定にすれば、全データをまとめて上書きすることになります。

しかしこれは、全データに同じ値をセットする処理です。

今回やりたいことは、一つ一つのデータに異なる値をセットすることです。

特定データを判別してそのデータにのみ上書きを複数データ同時に行う。

果たしてそんなことが可能なのでしょうか?

何はともあれ、ググって調べてみました。
いろいろ四苦八苦しましたが、次の方法で実現できました。

なんとINSERT文で複数データの上書きができます

嘘のような話ですが、MYSQLではINSERT文で上書きができます。
私も半信半疑でしたが、実際にやってみたところ複数データの上書きを1回のINSERT文で実現できました。

そのやり方を解説いたします。

（１）検証に使用するMYSQLのテーブル

ごちゃごちゃするとわかりにくいのでプライマリーキーとメモ欄の単純な構造にしています。
テーブル名は「mltrep」です。

（２）1回のSQLで3件追加する

次のSQL文を流し、1回のSQLで3件追加しました。

追加に関しては、さほど難しくありません。
データの分、VALUES以降を増やせばOKです。

ちなみに、noはオートインクリメント指定のため省略しても大丈夫です。

（３）3件まとめて1回のSQLで上書きする

それではいよいよ複数データをまとめて上書きしてみます。
次のSQL文を流すことでできました。

INSERT INTO mltrep(no, memo) VALUES (‘1′,’上書き01’),(‘2′,’上書き02’),(‘3′,’上書き03’) ON DUPLICATE KEY UPDATE no=VALUES(no), memo=VALUES(memo);

INSERT文でまとめて上書きをする際の注意点

このやり方には注意事項があります。

1. データにプライマリーキー（データごとに必ず値が異なる項目）があること
2. SQL文に必ずプライマリーキーを指定すること
3. メンバ名を省略記述しないこと
4. データが存在しない場合は新規追加されること

（１）プライマリーキーを必ず指定する

上書き処理のため、「どのデータを上書きするのか?」という情報が必要になります。

プライマリーキーに関しては、そもそも個別データ上書きを前提としているシステムであれば必須です。データベース設計段階で必ず盛り込んでおく必要があります。

（２）プライマリーキーをSQL文に必ず指定する

追加処理であれば、オートインクリメント指定があれば省略してもそれなりに値が入ります。
しかし、上書き処理の場合はどのデータを上書きするかを指定しなければ上書きになりません。
プライマリーキーの記述省略はNGなのです。

（３）メンバ名とその内容を必ず記述する

UPDATE文であれば、全てのメンバ名を記述しなくてもよく、変更のかかったメンバー名だけSQLに記述すればOKです。

しかし、INSERT文でUPDATEを行う場合は、全メンバ名とその中身の値をすべてSQL文に記述する必要があります。

試しに省略してみたら、省略した部分にはNULLがセットされました。

（４）データがない場合は新規追加される

UPDATE文の場合は、WHERE句で指定したデータがなかった場合エラーになります。
しかし、今回のやり方の場合「新規追加」されます。

試しにやってみたら、次のようになりました。

INSERT INTO mltrep(no, memo) VALUES (‘1′,’上書き01’),(‘2′,’上書き02’),(‘3′,’上書き03’),(‘4′,’存在しないデータ’) ON DUPLICATE KEY UPDATE no=VALUES(no), memo=VALUES(memo)

試しにやってみたSQL文

INSERTでUPDATEを行うにはきおつけることがありますが、慣れればとても便利です。

文法についての補足

今回、INSERT文に「ON DUPLICATE KEY UPDATE」を記述しています。
これは、重複データが存在していたら上書きするという意味です。

また後ろに記述している「no=VALUES(no), memo=VALUES(memo)」は、メンバ項目に実際にセットされる値を記述します。

今回この部分がなかなか理解できなくで苦労しました。
なぜならば、この部分の文例は1データ単位の記述が多かったからです。

ググって調べた文例では次のように記述されていました。

INSERT INTO mltrep(no, memo) VALUES (‘1′,’上書き01′) ON DUPLICATE KEY UPDATE no=1, memo=’上書き01’;

よく見かけた文例

文例の後ろの記述には、実際にセットされる値が記述されていました。
最初にこの文例を見たため、複数同時に記述するときは、次の書き方だろうと勘違いしました。

INSERT INTO mltrep(no, memo) VALUES (‘1′,’上書き01’),(‘2′,’上書き02’),(‘3′,’上書き03′) ON DUPLICATE KEY UPDATE no=1, memo=’上書き01′, no=2, memo=’上書き02′, no=3, memo=’上書き03’;

勘違いして失敗したSQL文

当然ですが文法エラーになりました。

この部分には値を直接記述できますが、そのやり方だと1データ分しか記述できません。

複数データを同時に記述するためには代入記述しなければいけなかったのです。

つまり、no=VALUES(no)、memo=VALUES(memo)の部分になります。
これは、INSERT文の最初のVALUESに記述された値を使用するという意味になります。
最初のVALUESに記述された値は、複数記述が可能であるため必然的に複数データに対応できるということになります。

私は、この部分がなかなか理解できなくて、失敗ばかり繰り返しました。
そもそもレアな使い方なので、詳しく解説している資料もなかなか見つけ出せませんでした。

たまたま、複数データ同時上書きの文例を見つけ、試してみたところうまくいったので、理解することができました。

トライ＆エラーってホントに大事です。

システム制作に携わるならば高速化は避けて通れない道

例えば、１～3件程度のデータを追加したり上書きしたりする場合には、1回1回データベースを呼び出してもさほどと遅くなりません。

しかし、1万件のデータを追加したり、上書きしたりする場合はどうでしょう？

環境や構造によってかかる時間は異なりますが、テスト環境で1万件を1件ずつ登録したら5分ほどかかりました。

ところがこれをSQL文に1万件分まとめて記述し1回のコールでやってみたところ1秒もかからず1万件の処理が終わりました。

びっくりするぐらいの高速化です。

無論MYSQLに引き渡しできるSQL文の長さの限界というものもあるため、無制限に1文に詰め込めるわけではありません。エラーになる場合は適度にSQL文を分割することも必要になってきます。

ただでさえ、WEBシステムはインターネットを介してサーバーと通信するロス時間があるるため、遅れるものです。ましてや1回の処理に10秒かかると使っている人は待てません。

遅くとも通信時間を含め3秒以内に処理を完結させることも大事なのです。

プログラムを作成するというのは、仕様を実現することが第1に求められます。
しかし、不具合がなくても処理に時間がかかりすぎては問題になります。

高速化処理を考えるということは思いのほか重要なことなのです。