更新のお知らせ
「その2」ができました。
「その1」を読んでいただいたあとに、こちらもご覧ください。
https://zenn.dev/maxima/articles/a23a9eda0cd3ae
はじめに
Hamee Advent Calendar 1日目ということで、実用的なSQL、バルクアップデートをご紹介したいと思います！
バルクアップデートとは、1文のSQLで複数のレコードを一気に更新してしまうUPDATE文のことです。
バルクインサートはよく聞くけど、バルクアップデートは出来ないのかと疑問に思ったことはないですか？
(バルクインサートについてそもそもご存じない方はこちらの記事がシンプルで分かりやすいかと思います)
結論から言いますと、バルクアップデートは可能です。しかし、バルクインサートほど気の利いた構文があるわけではありません。
これから何種類かご紹介しますが、ここに書かれている中で本当の意味でバルクアップデートできているのは下の方にある『ELT()とFIELD()を使った方法』だけです。お急ぎの方はそちらだけでも読んでいって下さい。
この他に、『CASEを使った方法』でも完璧なバルクアップデートができます。
なぜバルクなのか？
バルクインサート・バルクアップデートはどういうときに必要なのでしょうか。
これらが必要となるのは通信回数を減らしたいときです。
SQLは一文発行するごとに通信が発生します。その通信にかかる往復の時間は「MySQLがSQL文を解釈して、データベースから該当のデータを探して、何らかの処理をする」という一連の流れを行なう時間よりも遅い場合がほとんどです。
ですからなるべくまとめられる処理はまとめることで、総実行時間を短くするというのが目的なのです。
しかし、一文で書こうとするとSQLは複雑で読みづらいSQL文になってしまいます。
ですので後々の保守性のことも考えるとすれば、レコード数が少なく、通信速度がさほど問題にならないような箇所では一文一文SQLを発行したほうが読みやすいSQLになりますので、一文一文発行するのが良いでしょう。
UPDATE文は、既に存在しているレコードの更新する場合にしか使いません。
画面上から行う場合、それほど大量のデータを一気に更新するという状況はなかなかありませんので、ほとんどの場合これで事足りてしまいます。
しかし、たとえばCSVでデータを一括アップロードするような仕組みを備えたシステムですとか、バッチ処理を行っているシステムですと大量のUPDATEが発生する場合があります。そんなときには一文で取ってくるSQLを目指すのが良いかと思います。
今回使うテーブル
まず、以下の初期データを作ってください。
ここから先はこのテーブルを更新する前提でサンプルコードを書かせていただきます。
CREATE TABLE `example` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(11) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
INSERT INTO `example` (`id`, `name`)
VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Carol'),
(4, 'Dave');
まずは普通のUPDATE文
UPDATE example SET `name` = "Eve" WHERE `id` = 1;
UPDATE example SET `name` = "Frank" WHERE `id` = 2;
UPDATE example SET `name` = "Greg" WHERE `id` = 3;
UPDATE example SET `name` = "Helen" WHERE `id` = 4;
UPDATE文を何度も発行する、もっともシンプルな書き方です。
上記の例のように4レコードくらいであればこれがベストな選択でしょう。
大量に更新するのでなければコンマ数秒のわずかな違い(むしろ遅いこともある? 後ほどベンチマークも記事に載せてみます)でしかないので、保守性を取って誰でもすぐに理解できるこのSQL文を使いましょう。
INSERT ~ ON DUPLICATE KEY UPDATE 文
こちらは基本INSERT文で、主キーもしくはUNIQUEキーが重複したらUPDATEするという文になります。
これの良い所はバルクインサートと同じ感覚でUPDATE文が書けるという点です。
自動採番値は変化しないのですが、内部的にはDELETEしてからINSERTするという処理になっているのか、変化の行数を取得すると実際に変化のあった行数の倍の行数が表示されてます。
この構文はUPDATEの場合の処理をINSERTの場合の処理とは別に書かなくてはならないのが少々面倒です。
下記の構文で使っているVALUES()関数についてはこちらを参照してください。
INSERT INTO
example (`id`, `name`)
VALUES
(1, "Isac"),
(2, "James")
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);
REPLACE 文
REPLACE文はINSERT文とUPDATE文を合わせ持った構文です。
INSERT ~ ON DUPLICATE KEY UPDATEのように条件を指定することはせずに、INSERT文と全く同じ書き方で書くことができます。
REPLACE INTO
example (`id`, `name`)
VALUES
(1, "Kate"),
(2, "Lara");
REPLACE文はシンプルで良いのですが、反面、少々クセのある挙動をします。
知らないで使うとハマる可能性があります。
REPLACE文のuniqueキーがかぶった場合の内部挙動は、「UPDATE文」ではなく、「DELETE文 + INSERT文」という処理になります。
そのため、
- 「CURRENT_TIMESTAMP」がデフォルト値で設定されているカラムは毎回値が更新される
- 「影響があった行数」としてカウントされる行数は、更新行の倍の数になる（削除した分と追加した分）
という特徴があります。
そのため、レコードの作成日を「CURRENT_TIMESTAMP」を使って管理しているカラムがある場合などには、この方法は使えません。そういった場合は他のものをご利用ください。
ELT()とFIELD()を使った方法
イノベートな非日常 - 裏MySQL入門に書かれていた記事が面白かったので大いに参考にさせていただきました。
AUTO INCREMENTも変化せず、作成日等のカラムも変化しません。
少々複雑ですが、完璧なバルクアップデートができる構文です。
ELT(), FIELD()はあまり馴染み深い関数ではないかもしれません。しかし、うまく使えば大変使い勝手の良い関数です。
ぜひ覚えて帰ってくださいね。
ELT()は第一引数に数値、第二引数以降に任意の要素を取り、第一引数の数値の位置にある要素を返します。該当するものがなければNULLを返します。
ELT("数値", "1つめの要素", "2つめの要素"・・・)
SELECT ELT(1, "東京", "大阪"); -- 「東京」を返します。
SELECT ELT(2, "東京", "大阪"); -- 「大阪」を返します。
SELECT ELT(3, "東京", "大阪"); -- NULLを返します。
FIELD()は第一引数に検索用の要素、第二引数以降に被検索要素を取り、第一引数の要素と一致する第二引数以降の要素の位置を返します。該当するものがなければ0を返します。
FIELD("検索用文字列", "1つめの要素", "2つめの要素"・・・)
SELECT FIELD("東京", "東京", "大阪"); -- 1を返します。
SELECT FIELD("大阪", "東京", "大阪"); -- 2を返します。
SELECT FIELD("京都", "東京", "大阪"); -- 0を返します。
これを組み合わせるとこんなことができるようになります！
UPDATE `example` SET
name = ELT(FIELD(id,2,4,5),'Mary','Nancy','Oliver')
WHERE id IN (2,4,5)
上記のSQL文について簡単に解説します。
このSQL文を実行するとWHERE句によってidが2, 4, 5のレコードが抽出されます。
まずidが2のときを見ていきましょう。
FIELDの定義より、idが2のとき、FIELD(id,2,4,5)の結果は1となります。
するとそれを覆っているELT節は、ELT(1, 'Mary', Nancy', 'Oliver')ということになります。この結果はMaryですね。
よってidが2のレコードのnameはMaryに置き換えられます。
4,5も同様、Nancy, Oliverに置き換えられます。
非常にCOOLな構文ですね！
次の記事へ