【MySQL】MySQL上で大量のランダムデータを用意するためのプロシージャの例

　稀にランダムなデータをSQL上で直に流し込んでテストしたい時があります。どういった時に使えるプロシージャの例を紹介します。これは次です。具体的に何をしているかはコメントを参照です。

DELIMITER //
-- total_records インサートするデータの総件数
-- chunk_size 一度にバルクインサートする件数
CREATE PROCEDURE InsertRandomSchedules(total_records INT, chunk_size INT)
BEGIN
    -- 変数の初期化
    DECLARE current_chunk_size INT; -- 現在のバルクインサートのレコード数
    DECLARE inserted_records INT DEFAULT 0; -- 既にインサートされたレコード数
    DECLARE bulk_insert_query TEXT; -- クエリ文字列を保存する変数
    DECLARE i INT DEFAULT 0;
    -- 一つのバルクインサートの範囲を制御するためのカウンタ変数

    -- total_recordsに到達するまでループ
    WHILE inserted_records < total_records DO
        -- 残りのレコード数とchunk_sizeを比較して、小さい方を現在のチャンクサイズとする
        SET current_chunk_size =
                LEAST(chunk_size, total_records - inserted_records);
        -- バルクインサート用にテーブル名とカラム名を入れたINSERT文の始まりを用意
        SET bulk_insert_query =
                'INSERT INTO schedules(schedule_name, schedule_at) VALUES ';
        SET i = 0;
        -- 一つのバルクインサートで追加するレコード群を作成
        WHILE i < current_chunk_size DO
            -- 作りたいランダムな値をずらっと並べてクエリに追加
            -- 好き勝手作った値を QUOTE 関数にかけることで出来上がるINSERT分のシンタックスエラーを防ぐ
            SET bulk_insert_query = CONCAT(
                    bulk_insert_query,
                    '(',
                    QUOTE(CONCAT('Schedule-', FLOOR(1 + RAND() * 9999))),
                    ',',
                    QUOTE(TIMESTAMPADD(SECOND, -FLOOR(RAND() * 2592000), NOW())),
                    ')'
                );

            -- 最後のレコード以外は、カンマで区切る
            IF i < current_chunk_size - 1 THEN
                SET bulk_insert_query = CONCAT(bulk_insert_query, ',');
            END IF;

            SET i = i + 1;
        END WHILE;

        -- 作成したクエリの実行
        SET @query = bulk_insert_query;
        PREPARE stmt FROM @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        -- 既にインサートされたレコード数を更新
        SET inserted_records = inserted_records + current_chunk_size;
    END WHILE;
END//
DELIMITER
;

-- 使用例
CALL InsertRandomSchedules(10000, 1000);

　やっていることはよくある大量のランダムデータの生成と同じです。メモリなりSQLの制約なりの長さに気を付けてバルクインサートを繰り返します。一度ずつインサートするとインサートする度に起きる様々な副作用が原因で処理全体が完了するまでが遅くなるためバルクインサートを用います。また常に全てをバルクインサートしようとするとMySQLはそれを処理できず落ちます。このため多すぎず少なすぎずの量をまとめてインサートすることによって全体のインサート回数を少なめの回数に押さえます。

　テーブルごとに異なるプロシージャを用意するのはいささか手間ですが、とりあえずこういったプロシージャを用意すれば比較的楽にランダムな値を持つレコードをすぐに大量に用意できます。