93
Help us understand the problem. What are the problem?

More than 3 years have passed since last update.

posted at

updated at

SQLで大量のテストデータ作成

以前に 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句に記述したテーブルのレコード数を掛け算したレコード数が返される
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Sign upLogin
93
Help us understand the problem. What are the problem?