BackEnd

MySQLでバルクアップデート処理

投稿日:

はじめに

バッチ処理の実装では大量データを更新する場面がちょいちょい出てくると思います。
パフォーマンスを考えて出来る限りSQL発行回数を少なくして一括更新したい所です。
そんな時に使えるかもしれないバルクアップデート処理を紹介したいと思います。

使用するテーブル

テスト用に使用するテーブルです。

パターン数が少ないシンプルな更新

いつもやっている様なシンプルな更新処理で書けます。
例えば、テストの点数によって生徒のステータスを更新する場合、
パターンが一つしかない場合は、シンプルにwhereで条件付けて書けます

パターンが少数の場合は、case文で対応する事も可能です。

パターン数が多い場合

生徒の出席日数を更新したいと思います。
先程と異なり値の規則性がない為、whereやcaseで条件を付けるのは辛いものがあります。
その場合は、FIELD関数とELT関数の組み合わせで対応する事が可能です。

FIELD関数

第一引数のインデックスを返す関数。
下の例の場合、 ドイツ の文字列が文字リストの2番目にある為、2が返却されます。

詳細はこちら

ELT関数

引数から指定した番号の要素を返す関数。
下の例の場合、文字リストの1番目に当たる 日本 が返却されます。

詳細はこちら

組み合わせ

この2つを組み合わせて、student毎に出席日数をupdate出来ます。
where句で指定しているnameのインデックスをFIELD関数で取得し、それをELT関数の取得インデックスに利用しています。
結果的に、where句でname指定されている生徒の出席日数をそれぞれ指定された日数(10,20,30,40)に更新しています。
同様に、FILED関数の引数、ELT関数の引数、WHERE句に更新したい分の値を入れてやれば、一度のSQLでバルクアップデートが行う事が出来ます。

パフォーマンス

試しに1万レコードを一つずつアップデートする場合とバルクアップデートする場合で所要時間を比較してみたいと思います。
テストした際のコードは以下の通りです。
バルクアップデートする場合としない場合で、処理時間を計測してみました。

私のローカル環境ではバルクアップデートした場合0.5秒、1つずつアップデートした場合で20秒程度掛かっていました。
1万レコードでこれなので数が増えると結構な差が生まれそうです。

さいごに

関数の組み合わせで複数レコードを一括更新する方法を紹介しました。バッチ処理位しか使う場面はないと思いますが、機会があれば一度試してみてください。

おすすめ書籍

MySQL徹底入門 第4版 MySQL 8.0対応 達人に学ぶ SQL徹底指南書 SQL 第2版 ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)

blog-page_footer_336




blog-page_footer_336




-BackEnd
-

執筆者:


comment

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

CAPTCHA


関連記事

Go言語

Go言語、ゴルーチン(goroutine)で並列処理を

1 はじめに2 ゴルーチン2.1 go文2.2 ゴルーチンの終了条件2.3 WaitGroup3 チャネル3.1 チャネルの型3.2 チャネルの生成3.3 チャネルの送受信3.4 チャネルとゴルーチン ...

Go言語

Go 1.16でのgo getとgo installの変更点

1 はじめに2 go getとgo installの役割2.1 go getの役割2.2 go installの役割3 この変更で何が改善されたのか3.1 go:generateと併用する4 その他の ...

laravel logo

Laravelのブラウザテスト「Dusk」をシンプルなCRUDで始めてみよう

1 はじめに2 Laravel Duskのセットアップ3 新規登録のテスト3.1 テスト対象となるコード3.2 Duskのテストコード3.3 テストの実行4 更新のテスト4.1 テスト対象となるコード ...

php logo

PHP入門 〜文字列と数値の操作〜

1 はじめに2 基本ルール2.1 開始と終了のタグ2.2 ホワイトスペース2.3 大文字と小文字の区別2.4 コメント2.5 変数の定義3 文字列の操作3.1 文字列の定義3.2 無駄なスペースを削除 ...

rails

半年ぶりにRails Tutorialをやったメモ

はじめに Railsを触り始めて半年ほどたちました、tonnyです。 復習もかねてRails Tutorialを実施したので、そのメモを残します。 やはり2回目でも気づくことは多いので、非常に勉強にな ...

フォロー

blog-page_side_responsive

2022年11月
 12345
6789101112
13141516171819
20212223242526
27282930  

アプリ情報

私たちは無料アプリもリリースしています、ぜひご覧ください。 下記のアイコンから無料でダウンロードできます。