こんにちは、あっきー(IwswAkht)です。
今回はLaravelで複数データを一括で更新する方法、通称bulk updateについて書いていこうかと思います。
技術選定は以下になります。(MySQLとPostgreSQL両方で動作確認しますので各バージョンを記載しています)
- PHP 7.3
- Laravel 8.8.0
- MySQL 8.0.1
- PostgreSQL 13.1
【概要】bulk updateとは
そもそもbulk updateってなんやねん。という方へ向けて簡単に説明していきます。
そんなことは知っているよって方はこちらは読み飛ばしてください。
通常Laravelでアプリケーションを開発していてデータベースへの更新処理をかけるとしたら以下のような実装になることが多いかと思います。
Laravel
1 2 3 4 5 6 |
public function update() { $model = Model::find(1); $model['name'] = '更新する値'; $model->save(); } |
ですが、例えば画面にチェックボックス並んでいてチェックした値に全部に更新を加えるという場合はどのように実装するべきでしょうか?
以下のような実装をイメージする人も少なくはないのではないでしょうか。
Laravel
1 2 3 4 5 6 7 8 9 10 |
public function update() { $ids = [1,2,3,4,5]; $model = Model::query->whereIn('id', $ids)->get(); foreach($model as $row) { $row['name'] = '更新する値'; $row->save(); } } |
データが数件とかの更新でしたらこれでも問題はないかと思います。ですがチェックボックスの選択の数がめちゃくちゃ多かった場合、またはバッチ処理で数万件のレコードに対して更新処理をかける場合でしたらどうでしょう。
何百から何万回とデータベースへの更新処理がかかることになってしまいますね。
大前提で認識しておいていただきたいのですが、基本的にデータベースへアクセスするのは重たい処理です。
その重たい処理を何万回と繰り返したら速度が遅くなることは容易に想像できますよね。
そこで登場するのがbulk updateです。
レコード毎に更新値を設定→アップデート。でなく更新値を一括でまとめる→アップデート。一言で言ってしまえばこれがbulk updateの正体です。
環境によって差異は出るとは思いますが、実際に1万件のデータを一件ずつ更新した時とbulk updateで更新をしたの速度の違いとしては、1件ずつの更新には約1分ほどかかり、bulk updateの更新では15秒ほどで更新が完了しました。
bulk updateの威力をなんとなく感じていただけたでしょうか。それでは実際の実装の仕方を確認していきましょう。
bulk updateのSQLの書き方
Laravelでの実装を確認する前にbulk updateで一括更新するためのSQLの書き方を確認していきます。
というのもLaravelでは残念ながらEloquent ORMやクエリビルダでbulk updateを実行するメソッドは用意されていないようです。
そのため、自分でSQLを組み立ててbulk updateを実行してあげる必要性があるからです。
データベース名はlaravelとし、テーブル構成は以下とします。
テーブルのデータの内容は以下とします。
以下のSQL(MySQL)を実行します。
ターミナル
1 2 3 4 5 6 7 8 |
$ mysql -u root -p mysql> use laravel mysql> UPDATE members SET name = -> CASE id -> WHEN 1 THEN 'id1のnameを更新' -> WHEN 2 THEN 'id2のnameを更新' -> END -> WHERE id IN (1,2); |
PostgreSQLの場合は以下を実行します。
ターミナル
1 2 3 4 5 6 7 |
$ psql -d laravel Laravel=# UPDATE members SET name = Laravel-# CASE id Laravel-# WHEN 1 THEN 'id1のnameを更新' Laravel-# WHEN 2 THEN 'id2のnameを更新' Laravel-# END Laravel-# WHERE id IN (1,2); |
実行結果の確認
MySQLとPostgreSQL両方とも同じSQLで更新ができることが確認できました。
SQLも直訳で直感的に何をしているのかわかるかと思います。
membersテーブルからid、1と2を含むデータのnameカラムをCASE以下の条件で更新します。
条件:idが1の時はnameカラムに「id1のnameを更新」に更新、idが2の時はnameカラムを「id2のnameを更新」に更新。
実際のコードでは更新する値などを直接書くなどはないので以下のようにバインド変数を使用しプレースホルダーでのSQLの組み立てを行なっていきます。
Laravel
1 2 3 4 5 6 7 8 |
UPDATE members SET name = CASE id WHEN 1 THEN ? WHEN 2 THEN ? END WHERE id IN (1, 2); # ?の部分に更新する値を割り当てる |
ちなみにCASEはもちろんid以外のカラムで指定しても問題ありません。またCASEによって複数のカラムを更新したい、複数カラムを各々の値で更新+共通の値で更新などする場合は以下のようなSQLを組み立てます。
Laravel
1 2 3 4 5 6 7 8 9 10 11 12 13 |
UPDATE members SET name = CASE id WHEN 1 THEN ? WHEN 2 THEN ? END, gender = CASE gender WHEN 1 THEN ? WHEN 2 THEN ? END, updated_at = NOW() WHERE id IN (1, 2); # ?の部分に更新する値を割り当てる |
CASEの条件によってnameとgenderカラムを指定した値に更新して更新日updated_atには共通で現在日時を設定しています。
ここまでの例を踏まえて実際にLaravelでの実装方法を書いていきます。
Laravelでbulk update
playgroundというLaravelプロジェクトを以下の手順にそって作成してください。
ターミナル
1 2 3 |
$ laravel new playground $ cd playground $ php artisan make:model Member --migration --seed |
makeコマンドで生成された各種ファイルを編集します。
app/Models/Member.php
1 2 3 4 5 6 7 8 9 10 11 12 13 |
<?php namespace App\Models; use Illuminate\Database\Eloquent\Factories\HasFactory; use Illuminate\Database\Eloquent\Model; class Member extends Model { use HasFactory; protected $table = 'members'; } |
database/migrations/xxxx_xx_xx_xxxxxx_create_members_table.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
<?php use Illuminate\Database\Migrations\Migration; use Illuminate\Database\Schema\Blueprint; use Illuminate\Support\Facades\Schema; class CreateMembersTable extends Migration { public function up() { Schema::create('members', function (Blueprint $table) { $table->id(); $table->string('code'); $table->string('name'); $table->char('gender', 1)->default('1'); $table->timestamps(); }); } public function down() { Schema::dropIfExists('members'); } } |
database/seeders/MemberSeeder.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<?php namespace Database\Seeders; use Illuminate\Database\Seeder; use Illuminate\Support\Facades\DB; use Illuminate\Support\Carbon; class MemberSeeder extends Seeder { public function run() { $now = Carbon::now(); DB::table('members')->insert([ ['code' => 'M000001', 'name' => '田中太郎', 'gender' => '1', 'created_at' => $now, 'updated_at' => $now], ['code' => 'M000002', 'name' => '山田花子', 'gender' => '2', 'created_at' => $now, 'updated_at' => $now], ['code' => 'M000003', 'name' => '鈴木一郎', 'gender' => '1', 'created_at' => $now, 'updated_at' => $now], ['code' => 'M000004', 'name' => '渡辺敦', 'gender' => '1', 'created_at' => $now, 'updated_at' => $now] ]); } } |
database/seeders/DatabaseSeeder.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<?php namespace Database\Seeders; use Illuminate\Database\Seeder; class DatabaseSeeder extends Seeder { public function run() { $this->call([ MemberSeeder::class ]); } } |
.env
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
# MySQLの場合 DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=3306 DB_DATABASE=laravel DB_USERNAME=root DB_PASSWORD={your_database_password} #デフォルトは未設定 DB_CONNECTION=pgsql DB_HOST=127.0.0.1 DB_PORT=5432 DB_DATABASE=laravel DB_USERNAME=postgres DB_PASSWORD={your_database_password} #デフォルトは未設定 #使用しない方は#でコメントアウトしてください |
ここまでできたらマイグレートしてテーブルを作成します。
ターミナル
1 |
$ php artisan migrate --seed |
データベースの構築が完了しましたら各実装を行なっていきます。
controller
今回は簡易的にルートにアクセスしたらcontrollerの内容にアップデートする実装にします。
以下の編集を加えてください。
app/Http/Controllers/MemberController.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
<?php namespace App\Http\Controllers; use Illuminate\Http\Request; use Illuminate\Support\Facades\DB; use Illuminate\Support\Carbon; class MemberController extends Controller { public function index() { $rows = [ ['id' => 1, 'name' => 'aaa', 'gender' => '0'], ['id' => 2, 'name' => 'bbb', 'gender' => '9'], ['id' => 3, 'name' => 'ccc', 'gender' => '2'], ['id' => 4, 'name' => 'ddd', 'gender' => '0'] ]; $dataSet = []; foreach($rows as $idx => $row) { $cases[] = "WHEN {$row['id']} THEN ?"; $ids[] = $row['id']; $name[] = $row['name']; $gender[] = $row['gender']; if(count($rows) === $idx + 1) { $strCases = implode(' ', $cases); $strIds = implode(',', $ids); $sql = "UPDATE members SET name = "; $sql .= "CASE id {$strCases} END, "; $sql .= "gender = CASE id {$strCases} END, "; $sql .= "updated_at = NOW() "; $sql .= "WHERE id IN ({$strIds})"; $data['sql'] = $sql; $data['params'] = [...$name, ...$gender]; $dataSet[] = $data; } } foreach($dataSet as $data) { DB::update($data['sql'], $data['params']); } $members = Member::all(); return view('member.index', compact('members')); } } |
view
viewは変更した内容を出力だけしておきます。
memberフォルダを作成し、その配下にindex.blade.phpを作成して以下の編集を加えてください。
resources/views/member/index.blade.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
<!doctype html> <html lang="ja"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0"> <meta http-equiv="X-UA-Compatible" content="ie=edge"> <title>Document</title> <!-- CSS only --> <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-giJF6kkoqNQ00vy+HMDP7azOuL0xtbfIcaT9wjKHr8RbDVddVHyTfAAsrekwKmP1" crossorigin="anonymous"> <script src="https://cdn.jsdelivr.net/npm/vue@2.6.12"></script> <style> .table-contents { overflow-x: scroll; width: 100%; } table { white-space: nowrap; border-collapse: collapse; min-width: 100%; } </style> </head> <body> <div class="container"> <div id="app" class="mt-5"> <h1 class="h3">members_table</h1> <div class="table-contents"> <table class="table table-striped table-bordered"> <tr> <th>id</th> <th>code</th> <th>name</th> <th>gender</th> <th>created_at</th> <th>updated_at</th> </tr> @foreach ($members as $member) <tr> <td>{{ $member->id }}</td> <td>{{ $member->code }}</td> <td>{{ $member->name }}</td> <td>{{ $member->gender }}</td> <td>{{ $member->created_at }}</td> <td>{{ $member->updated_at }}</td> </tr> @endforeach </table> </div> </div> </div> |
ルーティング
以下の編集を加え、ルーティングを設定してからhttp://127.0.0.1:8000/にアクセスします
routes/web.php
1 2 3 |
<?php use Illuminate\Support\Facades\Route; Route::get('/', 'MemberController@index'); |
以下の表示結果を確認できます。
Target class [xxxController] does not exist.
この記事通りに環境構築をしますと画面にアクセスした時にこのエラーに遭遇するかと思います。
これはLaravel8リリースノートの「ルートの名前空間の向上」に記載がありますようにRouteServiceProviderの変更に伴って起きるそうです。
Laravel8系以前からバージョンアップした場合はこのエラーは発生しませんが、Laravel8系で新規プロジェクトを作成した方はルーティングを以下に変更すれば表示されます。
routes/web.php
1 2 3 |
<?php use Illuminate\Support\Facades\Route; Route::get('/', 'App\Http\Controllers\MemberController@index'); |
文字列型以外でのbulk update
ここまでの実装例でのデータベース方はname:VARCHAR(255)とgender:CHAR(1)と共にString型での実装です。
データベースをPostgreSQLで実装した方は、更新対象に数値型などの他のデータ型を指定すると以下のようなエラーが発生するかと思います。
エラーの内容通りなのですが、データ型が一致しないエラーですね。ループする配列のデータでは数値型で実装しててもずっとこのエラーが発生して僕も少しハマりました。
結論としてはPHP側であれこれデータ型をキャストしても意味なくて、SQLを組み立ててる箇所で明示的にデータ型を指定してあげることで解決することができました。
先ほどのコードを参考にするならMemberController.phpの23行目を以下のように修正すればOKです。
app/Http/Controllers/MemberController.php
1 |
$cases[] = "WHEN {$row['id']} THEN ?::integer"; |
chunk(分割)してbulk update
データ件数によりますが、bulk updateなど大量のデータを更新する時にもうひとつ考えてあげることがあります。
それがデータをchunk(分割)してあげることです。SQLを何回も呼び出すことは通信に負荷がかかることは冒頭に言いましたが、でかいデータを更新かけるにもこれまた負荷はかかります。
なので、基本的にbulk処理を実装する時は一緒にchunk処理を実装しておくことで動作が安定すると思います。
chunkする目安はカラム数、データ件数など様々な要因で処理速度が変わるので一概に言えず速度をはかりながら最適化してください。が答えではあるのですが僕の個人の意見で言うなら大体1000〜5000件位のchunkでいつも落ち着いてる気がしますのでご参考までに。
では、実際に実装してみましょう。と言うことで先ほどのコードを以下の様にリファクタリングしてください。
app/Http/Controllers/MemberController.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
<?php namespace App\Http\Controllers; use Illuminate\Http\Request; use Illuminate\Support\Facades\DB; use App\Models\Member; class MemberController extends Controller { public function index() { $rows = [ ['id' => 1, 'name' => 'hoge', 'gender' => '0'], ['id' => 2, 'name' => 'fuga', 'gender' => '9'], ['id' => 3, 'name' => 'foo', 'gender' => '2'], ['id' => 4, 'name' => 'bar', 'gender' => '0'] ]; $dataSet = []; $chunk = 2; $count = 1; foreach($rows as $idx => $row) { $cases[] = "WHEN {$row['id']} THEN ?"; $ids[] = $row['id']; $name[] = $row['name']; $gender[] = $row['gender']; if(count($rows) === $idx + 1) { $dataSet[] = self::bulkUpdate($cases, $ids, $name, $gender); break; } if($count >= $chunk) { $dataSet[] = self::bulkUpdate($cases, $ids, $name, $gender); $cases = []; $ids = []; $name = []; $gender = []; $count = 0; } $count ++; } foreach($dataSet as $data) { DB::update($data['sql'], $data['params']); } $members = Member::all(); return view('member.index', compact('members')); } public static function bulkUpdate($cases, $ids, $name, $gender) { $strCases = implode(' ', $cases); $strIds = implode(',', $ids); $sql = "UPDATE members SET name = "; $sql .= "CASE id {$strCases} END, "; $sql .= "gender = CASE id {$strCases} END, "; $sql .= "updated_at = NOW() "; $sql .= "WHERE id IN ({$strIds})"; $data['sql'] = $sql; $data['params'] = [...$name, ...$gender]; return $data; } } |
データが更新されることが確認できたらOKです。お疲れ様でした。
さいごに
さいごまで読んでいただきありがとうございます!
bulk updateは業務でも使えるテクニックかと思いますので、ぜひ参考にしていただけたらうれしいです。
本日の内容の要点をおさらいしましょう。
- SQLは重い処理なので通信回数は減らす
- bulk updateなどの一括処理でSQLの回数を減らす
- でかいデータの通信も重たい
- chunk処理で1通信のデータサイズを小さくする
bulk updateの他にもbulk insertやbulk upsertなる一括処理もありますので、その辺も次の記事のネタにしようかと思いますので引き続きよろしくお願いします。
この記事を気に入っていただけましたらTwitterdでもプログラミングに関してのツイートをリアルタイムでしていますので
ご一緒にフォローもお願いします。
僕は未経験からSESの企業にエンジニアとして転職し、その後はフリーラン、現在は受託開発企業に転職しました。
エンジニアとしていろいろな働き方を経験し、いろいろな転職サイトや転職エージェントの方にお話を伺いました。
その経験の中でだいじだと感じたことは、ITに特化した転職エージェントのサービスを利用することです。
幅広い業界に対応した転職エージェントはIT転職に特化したエージェントと比べると紹介先の数が少ないです。
そのため希望してない紹介先に転職し、思ってたのと違うとなることもあります。
そうならないためにエンジニアを目指すなら必ずIT特化の転職サービスを登録し、たくさんの選択肢の中から自分が行きたいと思う企業を探してください。
僕はフリーランスでの案件探し、受託開発企業の転職の時もレバテックのかたに紹介していただきました。
とても満足できましたので皆さんもぜひ利用してみてください!
\無料のIT系転職サービス/
/優良案件がたくさん\