🙌

MySQLでバルクアップデートを実現するにはその2

巻嶋雄大

2023/06/18に公開

MySQL

tech

こんにちは。
NE株式会社　エンジニアの巻嶋です。

前回、こちらの記事で、バルクアップデートを取り上げさせていただきました。

今回は、こちらの記事で取り上げさせていただいた、ELTとFIELDを使う方法を更に便利に使う方法をご説明させていただきます。

おさらい

こちらのように、id列はすでに用意されているものの、まだnameが入っていないようなテーブルがあるとします。

usersテーブル

id	name
1	NULL
2	NULL
3	NULL

これを下記のように、名前が入った状態にしたいのですが、

id	name
1	佐藤
2	田中
3	山田

普通にやるなら、

UPDATE `users` SET `name` = '佐藤' WHERE `id` = 1;
UPDATE `users` SET `name` = '田中' WHERE `id` = 2;
UPDATE `users` SET `name` = '山田' WHERE `id` = 3;

のように1行ずつ更新することになります。

3レコードぐらいでしたら問題にならないのですが、通常、SQLの発行回数=通信回数になりますので、これが1000行、10000行となってくると、通信によるオーバーヘッドが馬鹿にならなくなってきます。

それを、こちらの記法で書いた場合、1回の通信で済むので、高速に実行できるという流れです。

UPDATE `users` SET 
name = ELT(FIELD(id,1,2,3),'佐藤','田中','山田') 
WHERE id IN (1,2,3)

ELTとFIELDの説明は、前回の記事をご覧ください。

ちなみに、計測してみたところ、1回にすれば何でも良いというわけではなく、あまりにも一回に送りすぎると、それはそれでオーバーヘッドが発生するようです。

13506レコードある、テーブルに対して、様々な分割数で実行してみたところ、下記のような結果になりました。

レコード数	全部更新するのにかかった秒数
1レコードずつ更新	71秒
10レコードずつ更新	7秒
100レコードずつ更新	1.3秒
1000レコードずつ更新	0.7秒
10000レコードずつ更新	1.6秒
全部一気に更新	2.0秒

全部一気にまとめて更新するのが早そうに思っていたのですが、意外と1000程度に分割するのが最速なようです。

ここまでが、前回のおさらいです。

2カラム以上同時に更新したい場合

課題

八百屋さんの商品マスタです。
id列、野菜の名前、産地、値段があります。

productsテーブル

id	name	product_of	price
1	にんじん	北海道	100
2	じゃがいも	鹿児島	90
3	トマト	熊本	120
4	きゅうり	宮崎	80

この八百屋さんは、野菜の種類は固定なのですが、産地と値段は毎日変動します。
つまり、じゃがいもの産地が鹿児島から北海道になったり、トマトの値段が120円から130円に変わったりします。

このとき、次のように更新する場合を見てみましょう。

にんじん(1)は更新しない。
じゃがいも(2)の産地は、鹿児島から北海道に変える。値段は変えない。
トマト(3)の産地は熊本から神奈川に変える。値段は120円→130円に変える。
きゅうり(4)は産地は変えない。値段は80円→100円に変える。

これを先程ご紹介したSQL文で書こうとすると・・・・・・書けないのです。
少なくとも１本では書けないのです。

一見これで行けそうだと思われるかもしれません。

UPDATE `products` SET 
product_of = ELT(FIELD(id,2,3),'北海道', '神奈川'),
price = ELT(FIELD(id,3,4),'130','100') 
WHERE id IN (2,3,4)

しかし、これを実行すると、

id	name	product_of	price
1	にんじん	北海道	100
2	じゃがいも	北海道	NULL
3	トマト	神奈川	130
4	きゅうり	NULL	100

残念ながら、IN句に指定しているけれど、ELT~FIELDに入れていないものは、NULLになってしまいます。

問題が起こる理由

これはFIELDのマニュアルに

str が見つからない場合は、0 を返します

と書いてある通り、0を返していること、

ELTのマニュアルに

N が 1 よりも小さいか、引数の数よりも大きい場合は、NULL を返します。

と書いてあるとおり、FIELDが返している0は1よりも小さいので、結果としてNULLになってしまいます。

解決法(仮)

もちろん、元の値を下記のようにセットすれば回避できます。

UPDATE `products` SET 
product_of = ELT(FIELD(id,2,3,4),'北海道', '神奈川', '宮崎'),
price = ELT(FIELD(id,2,3,4),'90','130','100') 
WHERE id IN (2,3,4);

しかし、これだと、一回元の値をSELECTして来なくてはいけなくなってしまいます。
何とかUPDATE文一本だけで書けないでしょうか？

解決法

UPDATE文には、こういった文法があります。
こちらのSQLはproduct_ofをproduct_ofの内容で更新するという意味になります。

UPDATE `products` SET
product_of = product_of;

結果として何も更新されず、意味がありませんので、普通は、これだけで使うことはありません。

普通はこのように、他のカラムの値をもとになにかしたい場合などに利用します。

UPDATE `products` SET
price_tax_in = price * 1.08;

今回はこの自分自身で更新する方法と、IF関数とFIELD関数を組み合わせて、こんな記法を作ってみました。

UPDATE `products` SET 
product_of = IF(FIELD(id,2,3) = 0, product_of, ELT(FIELD(id,2,3),'北海道', '神奈川')),
price = IF(FIELD(id,3,4) = 0, price, ELT(FIELD(id,3,4),'130','100'))
WHERE id IN (2,3,4)

ちょっと複雑になってきました。
でも、一つずつ噛み砕いて考えていけば、必ず理解できます。

ゆっくり順番に説明していきますね。
まず、WHERE句にid IN (2,3,4)がセットされていますから、idが2,3,4のいずれかのレコードについては、更新処理をしようとします。

product_ofは2,3だけを、priceは3,4だけを更新する必要があります。
つまり、逆に言えば、idが4のとき、product_ofは更新してほしくないですし、idが2のとき、priceは更新してほしくないわけです。

しかし、WHERE句にセットしてしまっている以上、更新処理はどうやっても走ってしまいます。
そしたらどうするか？

今の自分自身の値で更新処理を行えば、実質更新していないのと同じ になるわけです。

更新がされる場合の文法解説

この部分だけ抜き出してみてみましょう。

product_of = IF(FIELD(id,2,3) = 0, product_of, ELT(FIELD(id,2,3),'北海道', '神奈川'))

まず、idが2の場合

idには2が入りますから、FIELD関数の第一引数には2が入りますね。

product_of = IF(FIELD(2,2,3) = 0, product_of, ELT(FIELD(id,2,3),'北海道', '神奈川'))

FIELD関数は第1引数と第2引数の結果が同じなので、1を返します。
FIELD関数の仕様についてはマニュアルをご覧ください。

すると、下記のようになります。

product_of = IF(1 = 0, product_of, ELT(FIELD(id,2,3),'北海道', '神奈川'))

IF関数は、第1引数の結果が真なら第2引数を、偽なら第3引数を返します。

1 = 0は偽ですから、第3引数を返します。

product_of = ELT(FIELD(id,2,3),'北海道', '神奈川');

ここまでくれば、後は元のバルクアップデートと同じです。

idに2が入り、

product_of = ELT(FIELD(2,2,3),'北海道', '神奈川');

FIELD関数の結果が1になり、

product_of = ELT(1,'北海道', '神奈川');

ELT関数の結果が北海道になります。

product_of = '北海道';

大変長い道のりでしたが、これで、id=2のときの、産地が「北海道」になりました。

更新がされない場合の文法の解説

もう一度、元の形を出しておきましょう。

UPDATE `products` SET 
product_of = IF(FIELD(id,2,3) = 0, product_of, ELT(FIELD(id,2,3),'北海道', '神奈川')),
price = IF(FIELD(id,3,4) = 0, price, ELT(FIELD(id,3,4),'130','100'))
WHERE id IN (2,3,4)

今度は、idが4で、product_ofが更新されない場合のパターンを考えていきます。
こちらは先程のより簡単です。

この部分だけ抜き出して考えてみましょう。

product_of = IF(FIELD(id,2,3) = 0, product_of, ELT(FIELD(id,2,3),'北海道', '神奈川'))

idは4ですから、4が入ります。

product_of = IF(FIELD(4,2,3) = 0, product_of, ELT(FIELD(id,2,3),'北海道', '神奈川'))

FIELD関数はもう慣れましたでしょうか？
第1引数が4ですが、第2引数以降に一致するものがないので、FIELD関数の結果は0となります。
一応もう一度マニュアルを掲載しておきますね。

product_of = IF(0 = 0, product_of, ELT(FIELD(id,2,3),'北海道', '神奈川'))

0 = 0 は真ですから、IF関数の結果は、第2引数にある、product_ofが採用されます。

product_of = product_of

product_ofをproduct_ofで更新するわけですから、何も変化しません。

ちなみにこの場合、更新行数としてカウントされませんので、on UPDATE CURRENT_TIMESTAMPの更新対象にはなりません。

最後に

バルクアップデートを業務で実運用していると、意外と、同じカラムだけ一気に更新というシーンは少ないということに気づき、今回の方法を考えてみました。

たとえば、弊社で提供していますネクストエンジンはネットショップの販売管理ツールなのですが、例に上げたように、「この商品は値段だけ変えたいけど、この商品はタイトルだけ変えたい」といったシーンはよくあります。セール品の商品名に「セール」とつけたりするケースは結構ありますよね。

一括更新の速度を極めたい方にはおすすめです！
ぜひご利用ください！

以上よろしくお願いします！

NE株式会社の開発ブログPublication

NE株式会社のエンジニアを中心に更新していくPublicationです。 NEでは、「コマースに熱狂を。」をパーパスに掲げ、ECやその周辺領域の事業に取り組んでいます。 Homepage: ne-inc.jp/