はじめに
はい、クエリビルダネタです。どれだけの人が書いたかわからない程のネタです。今回はその中でも少しだけレア度の高そうなバルクインサートについてご紹介します。
バルクインサート？
ご存知の通りバルクインサートとは、一度のINSERT文で複数レコードを DB にインサートできるものです。プログラムからクエリ発行する場合、ループでINSERT文を発行すると残酷な運命しか待ち受けていません。それを一度のインサートでドサッと行ってしまうことで、クエリ発行数が激減、高速化が図れるというわけです。
参考
インサート（insert）の処理方式別のパフォーマンスを検証 : 株式会社インターオフィス
注意その１
普通のINSERT文の場合、PDO などの戻り値はインサートされたレコードの auto_increment 値だったりするわけですが、バルクインサートの場合は「最初にインサートされたレコードの auto_increment 値」が戻ります。FuelPHP の仕様ではなく、MySQL や PDO の方の仕様？のようです。この辺はそのうち…ね（目逸し
注意その２
ここで紹介するコードが使える範囲について、自分が使用した経験のあるDBに絞って言えば、MySQL、PostgreSQL は使用できますが Oracle はできません（めったにないことかとおもいますが、一応）。理由はバルクインサートの構文が全く違ってくるからです。下にサンプルを記載しておきますが、諸々適当です。インデントとか気にした人からバタバタ死んでいきます。
/* MySQL, PostgreSQL */
INSERT
INTO BULK_INS_TEST
(COLUMN_1, COLUMN_2, COLUMN_3, COLUMN_4, COLUMN_5)
VALUES
(1, 2, 3, 4, 5),
(1, 2, 3, 4, 5),
(1, 2, 3, 4, 5),
(1, 2, 3, 4, 5),
(1, 2, 3, 4, 5);
/* Oracle */
INSERT ALL
INTO BULK_INS_TEST (COLUMN_1, COLUMN_2, COLUMN_3, COLUMN_4, COLUMN_5) VALUES (1, 2, 3, 4, 5)
INTO BULK_INS_TEST (COLUMN_1, COLUMN_2, COLUMN_3, COLUMN_4, COLUMN_5) VALUES (1, 2, 3, 4, 5)
INTO BULK_INS_TEST (COLUMN_1, COLUMN_2, COLUMN_3, COLUMN_4, COLUMN_5) VALUES (1, 2, 3, 4, 5)
INTO BULK_INS_TEST (COLUMN_1, COLUMN_2, COLUMN_3, COLUMN_4, COLUMN_5) VALUES (1, 2, 3, 4, 5)
INTO BULK_INS_TEST (COLUMN_1, COLUMN_2, COLUMN_3, COLUMN_4, COLUMN_5) VALUES (1, 2, 3, 4, 5)
SELECT * FROM DUAL;
Oracle 長い超長いカラム名毎回とか…。
とはいえこれは別テーブルに一文でインサートできるとんでも構文なのですが。
その点 MySQL と PostgreSQL はテーブルごとに発行する必要があるので、トレードオフ関係になってるんでしょうかね？
FuelPHP のクエリビルダでやってみよう
へっぽこなので FuelPHP 以外には Zend Framework の1.x系しかまだ使用したことがありませんので、もしかしたら他のフレームワークではできるのかもしれませんが、Zend Framework ではクエリビルダでバルクインサートのクエリを作成できなかった気がします。自力でSQLを書いてました。
ここに雑なテーブルがあるじゃろ？
create table BULK_INS_TEST (
COLUMN_1 int NOT NULL AUTO_INCREMENT,
COLUMN_2 int,
COLUMN_3 int,
COLUMN_4 int,
COLUMN_5 int,
primary key (COLUMN_1)
);
試しにクエリビルダの実行結果を echo してみて...
echo \DB::insert(
'BULK_INS_TEST'
)->columns(array(
'COLUMN_1',
'COLUMN_2',
'COLUMN_3',
'COLUMN_4',
'COLUMN_5',
))->values(
array(1,2,3,4,5)
)->values(
array(1,2,3,4,5)
)->values(
array(1,2,3,4,5)
)->values(
array(1,2,3,4,5)
)->values(
array(1,2,3,4,5)
)->compile();
こうじゃ
/*
INSERT INTO BULK_INS_TEST (COLUMN_1, COLUMN_2, COLUMN_3, COLUMN_4, COLUMN_5) VALUES (1, 2, 3, 4, 5), (1, 2, 3, 4, 5), (1, 2, 3, 4, 5), (1, 2, 3, 4, 5), (1, 2, 3, 4, 5)
*/
実際使うことを目指して書いてみる
長いクエリだとSQL長すぎぃ！って怒られてしまうので、大量のレコードを扱う場合には数十～百行ずつくらいに分けた方がいいと思います。分ける行数が多すぎても少なすぎてもパフォーマンスが落ちるのでチューニング必須です。
戻り値については下記の記事を参考に
FuelPHP,Database_Queryクラスのexecuteメソッドの戻り値（@bonon0様）
class Model_Hoge extends \Model
{
/**
* 雑にメソッド書くとこんな感じ
*
* @param array $values レコード形式で
*/
public static function bulk_ins_test(array $values)
{
$builder = \DB::insert(
'BULK_INS_TEST'
)->columns(array(
'COLUMN_1',
'COLUMN_2',
'COLUMN_3',
'COLUMN_4',
'COLUMN_5',
));
foreach ($values as $value)
{
$builder = $builder->values($value);
}
return $builder->execute();
}
}
おわりに
別にCREATE文いらなかった
ここまでお読みいただきありがとうございました！