SQL だけで100万行 のテストデータを用意する方法

軽量プログラミング言語を使ってデータ生成してもいいのだけど接続情報書くのが面倒なので SQL だけでサクッとやりたい。そんなときは SQL の直積を利用すると楽である。今回動作検証に用いた環境は Mac に入っている MySQL 5.6.13。

準備

まずはテーブルの作成とデータ生成のためのデータ用意。
用意するものは0から9の数字が入ったテーブル1つだけ。

CREATE TABLE digit(num integer);
INSERT INTO digit VALUES
(0),
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9);

早速 100 万行のデータ生成

実際の SQL。このサンプルでは動作確認のため ORDER BY を使っているが無くてもよい。

SELECT
    d1.num + d2.num*10 + d3.num*100 + d4.num*1000 + d5.num*10000 + d6.num*100000 as num
FROM
    digit d1, digit d2, digit d3, digit d4, digit d5, digit d6
ORDER BY
    num;

応用

users テーブルにユニークなEメールアドレスを100万行追加したいという場合は以下のような SQL を記載する。ただし、この方法だと最初のユーザIDは0になる。

CREATE TEMPORARY TABLE foo(
    SELECT
        d1.num + d2.num*10 + d3.num*100 + d4.num*1000 + d5.num*10000 + d6.num*100000 as num
    FROM
        digit d1, digit d2, digit d3, digit d4, digit d5, digit d6
    ORDER BY
        num
);
INSERT INTO users(id, email)
SELECT num, CONCAT("user+", num, "@example.com") FROM foo;

まとめ

この方法は7年ぐらい前に SQL に詳しいおじさんから教えてもらった。私はパフォーマンステストでテストデータを用意する際にこのような SQL をよく書く。
凝ったテストデータが要求されないならばこの方法は SQL のみで完結しとても高速に動くのでおすすめである。