以前に SQLでテーブルデータの一括作成、複製 という記事を書いたのですがもう少しかみ砕いて、かつPostgreSQLにも対応した内容で書き直してみます。
RDBMSを利用したアプリケーションを開発していて数千件を超える大量のデータを作成する必要が発生した場合に知っておくと便利なテクニックの紹介です。なお、以下のようなケースを想定しています。
- SQLのパフォーマンス検証のために大量のレコードが必要
- 1テーブルに100万件以上
- 動作検証・評価作業のためにテスト内容に準じたデータが一定数必要
- 1セット100件を100セット
事前準備
SELECT文の 直積(CROSS JOIN) を利用します。
事前に一定数のレコードを保持するテーブルが必要です。
ここでは sample というテーブルを作成して 直積(CROSS JOIN) のSELECT文に利用します。
MySQLとPostgreSQLでのSQLの書き方を紹介しますが、Oracleでも ROWNUM を使えば同様に実現できます。
(Oracle版のSQLを書かないのは実行環境を作るのが面倒だからとか推測してはいけません)
MySQL
CREATE TABLE sample(
id INT(11) NOT NULL AUTO_INCREMENT,
value INT(5) NOT NULL DEFAULT 0,
PRIMARY KEY (id)
);
INSERT INTO sample(value)
VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
直積のSELECT文
MySQLには行番号を返す仕組がないので ユーザー定義変数 を利用しています。
@rownum が ユーザー定義変数 です。
以下のSQLを実行して帰ってくるレコードの値、件数を確認してください。
SELECT
@rownum := @rownum + 1
FROM
sample,
(SELECT @rownum := 10000) AS v;
SELECT
@rownum := @rownum + 1
FROM
sample AS s1,
sample AS s2,
(SELECT @rownum := 10000) AS v;
PostgreSQL
CREATE TABLE sample(
id SERIAL,
value integer NOT NULL DEFAULT 0,
PRIMARY KEY (id)
);
INSERT INTO sample(value)
VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
直積のSELECT文
以下のSQLを実行して返ってくるレコードの値、件数を確認してください。
SELECT
ROW_NUMBER() OVER (),
ROW_NUMBER() OVER () + 10000
FROM
sample AS s1,
sample AS s2
1万件のレコードのINSERT
account テーブルを作成して name カラムに NAME + id という値を持つレコードを1万件追加します。
(なぜuserテーブルじゃないの？という疑問がわくと思いますが、PostgreSQLでは user は予約語なのです・・・)
MySQL
CREATE TABLE account(
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
INSERT INTO account(name)
SELECT
CONCAT('NAME' , @rownum := @rownum + 1)
FROM
sample AS s1,
sample AS s2,
sample AS s3,
sample AS s4,
(SELECT @rownum := 0) AS v
PostgreSQL
CREATE TABLE account(
id SERIAL,
name character varying(50) NOT NULL,
created_at timestamp with time zone DEFAULT NOW(),
updated_at timestamp with time zone DEFAULT NOW(),
PRIMARY KEY (id)
);
INSERT INTO account(name)
SELECT
'NAME' || ROW_NUMBER() OVER ()
FROM
sample AS s1,
sample AS s2,
sample AS s3,
sample AS s4
応用編1
さて account テーブルには1万件のレコードが作成されたわけですが、以下のSQLを実行してみてください。
MySQL
SELECT
@rownum := @rownum + 1
FROM
account,
(SELECT @rownum := 0) AS v
LIMIT 1000
PostgreSQL
SELECT
ROW_NUMBER() OVER ()
FROM
account
LIMIT 1000
sample テーブルを利用したケースと同様に連番の数字が返ってきます。
LIMIT 句を外せば1～10000の範囲で1万件のレコードが返されます。
1万件のレコードの生成に sample テーブルで 直積 を行いましたが十分なレコードを持つテーブルがあるならそれを使えば必要なレコード数はまかなえます。
応用編2
直積 を利用して既存レコードの複製を行います。
まずは事前準備として item テーブルの作成と複製元となるレコードを作成します。
MySQL
CREATE TABLE item(
id INT(11) NOT NULL AUTO_INCREMENT,
account_id INT(11) NOT NULL,
master_item_id INT(11) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
INSERT INTO item(account_id, master_item_id)
VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5);
PostgreSQL
CREATE TABLE item(
id SERIAL,
account_id integer NOT NULL,
master_item_id integer NOT NULL,
created_at timestamp with time zone DEFAULT NOW(),
updated_at timestamp with time zone DEFAULT NOW(),
PRIMARY KEY (id)
);
INSERT INTO item(account_id, master_item_id)
VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5);
さて、 account_id = 1 と同じ item データを account_id 2～10000 にも同様に作成したい言われたらどうしましょう？
9999回INSERT文を実行しますか？
ノンノン、既に便利な 直積 というテクニックを得ていますね。
以下のSQLを1回実行するだけでレコードの複製が完了します。
MySQL
INSERT INTO item(account_id, master_item_id)
SELECT
a.id,
i.master_item_id
FROM
item AS i,
account AS a
WHERE
i.account_id = 1
AND
a.id BETWEEN 2 AND 10000
PostgreSQL
INSERT INTO item(account_id, master_item_id)
SELECT
a.id,
i.master_item_id
FROM
item AS i,
account AS a
WHERE
i.account_id = 1
AND
a.id BETWEEN 2 AND 10000
まとめ
- SELECT文の
外積(CROSS JOIN)とINSERT ~ INTO構文を利用するとレコードの作成、複製が自由自在 - SELECT文の仕様、挙動に少し詳しくなれたかも
- SELECT文はFROM句に記述したテーブルのレコード数を掛け算したレコード数が返される