【MySQL】複数レコードを一発でupdateする方法メモ 〜 bulk update

はじめに

mysqlはちょいちょい使ってきたけど今回はちょっと感動でした。

やりたかったことは
バッチ処理などで集計結果でテーブルを更新する場合などでした

テーブル例はこんな感じ
totalsテーブル

カラム	内容
id	PK
count	集計結果

今まではこんな感じ実装していました

// 集計処理
$total = array(
    array("id" => 1, "count" => 123),
    array("id" => 2, "count" => 456),
    ・・・
);

// 1行ずつ更新
foreach ($total as $v) {
  $query = "update totals set count = count + {$v["count"]} where id = {$v["id"]}";
  //$query実行
}

更新回数は多くなるけどPKでの更新なので問題になるほど遅くなるようなこともないだろうと考えていました。
が、今回処理速度も意識しなくてはならないケースで改善が必要でした

実装

// 集計処理
$total = array(
    array("id" => 1, "count" => 123),
    array("id" => 2, "count" => 456),
    ・・・
);

// values句を組み立てる　
$values = "";
foreach ($total as $v) {
    $values .= "($v["id"], $v["count"]),";
}
$values = substr($values, 0, -1);

// sql実行
$query = "insert into totals (id, count) values {$values}"
               ."on duplicate key update count = count + values(count)";

/*
insert into totals (id, count) values (1, 123), (2, 456)
on duplicate key update count = count + values(count)
*/

マシンのスペックにもよるので数字は一例ですが
更新対象が1000レコードの場合で

1000回upadteする場合 = 約5秒
bulk updateで一括更新する場合 = 0.01秒！

こんな書き方ができたんですね、、、この方法は速いという以外にもメリットがあったのですが今度書いてみたいと思います

以上です