FuelPHPのDatabase_Query_Builder_Insertでバルクインサートが使用できた話

はじめに

はい、クエリビルダネタです。どれだけの人が書いたかわからない程のネタです。今回はその中でも少しだけレア度の高そうなバルクインサートについてご紹介します。

バルクインサート？

ご存知の通りバルクインサートとは、一度のINSERT文で複数レコードを DB にインサートできるものです。プログラムからクエリ発行する場合、ループでINSERT文を発行すると残酷な運命しか待ち受けていません。それを一度のインサートでドサッと行ってしまうことで、クエリ発行数が激減、高速化が図れるというわけです。

参考
インサート（insert）の処理方式別のパフォーマンスを検証 : 株式会社インターオフィス

注意その１

普通のINSERT文の場合、PDO などの戻り値はインサートされたレコードの auto_increment 値だったりするわけですが、バルクインサートの場合は「最初にインサートされたレコードの auto_increment 値」が戻ります。FuelPHP の仕様ではなく、MySQL や PDO の方の仕様？のようです。この辺はそのうち…ね（目逸し

注意その２

ここで紹介するコードが使える範囲について、自分が使用した経験のあるDBに絞って言えば、MySQL、PostgreSQL は使用できますが Oracle はできません（めったにないことかとおもいますが、一応）。理由はバルクインサートの構文が全く違ってくるからです。下にサンプルを記載しておきますが、諸々適当です。インデントとか気にした人からバタバタ死んでいきます。

bulk_insert

/* 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

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 してみて...

bulk_insert

    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();

こうじゃ

bulk_insert

/*
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様）

bulk_insert_usage

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文いらなかった

ここまでお読みいただきありがとうございました！