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

以前に [SQLでテーブルデータの一括作成、複製](http://cobot.hatenablog.com/entry/2013/05/04/040231) という記事を書いたのですがもう少しかみ砕いて、かつPostgreSQLにも対応した内容で書き直してみます。

RDBMSを利用したアプリケーションを開発していて数千件を超える大量のデータを作成する必要が発生した場合に知っておくと便利なテクニックの紹介です。なお、以下のようなケースを想定しています。

* SQLのパフォーマンス検証のために大量のレコードが必要
  - 1テーブルに100万件以上
* 動作検証・評価作業のためにテスト内容に準じたデータが一定数必要
  - 1セット100件を100セット

## 事前準備
SELECT文の `直積(CROSS JOIN)` を利用します。
事前に一定数のレコードを保持するテーブルが必要です。
ここでは `sample` というテーブルを作成して `直積(CROSS JOIN)` のSELECT文に利用します。
MySQLとPostgreSQLでのSQLの書き方を紹介しますが、Oracleでも `ROWNUM` を使えば同様に実現できます。
(Oracle版のSQLを書かないのは実行環境を作るのが面倒だからとか推測してはいけません)

### MySQL
```sql
CREATE TABLE sample(
  id INT(11) NOT NULL AUTO_INCREMENT,
  value INT(5) NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);
```

```sql
INSERT INTO sample(value)
VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
```

#### 直積のSELECT文
MySQLには行番号を返す仕組がないので `ユーザー定義変数` を利用しています。
`@rownum` が `ユーザー定義変数` です。
以下のSQLを実行して帰ってくるレコードの値、件数を確認してください。

```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
```sql
CREATE TABLE sample(
  id SERIAL,
  value integer NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);
```

```sql
INSERT INTO sample(value)
VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
```

#### 直積のSELECT文
以下のSQLを実行して返ってくるレコードの値、件数を確認してください。

```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
```sql
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)
);
```

```sql
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

```sql
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)
);
```

```sql
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
```sql
SELECT
  @rownum := @rownum + 1
FROM
  account,
  (SELECT @rownum := 0) AS v
LIMIT 1000
```

### PostgreSQL
```sql
SELECT
  ROW_NUMBER() OVER ()
FROM
  account
LIMIT 1000
```

`sample` テーブルを利用したケースと同様に連番の数字が返ってきます。
`LIMIT` 句を外せば1～10000の範囲で1万件のレコードが返されます。
1万件のレコードの生成に `sample` テーブルで `直積` を行いましたが十分なレコードを持つテーブルがあるならそれを使えば必要なレコード数はまかなえます。

## 応用編2
`直積` を利用して既存レコードの複製を行います。
まずは事前準備として `item` テーブルの作成と複製元となるレコードを作成します。

### MySQL
```sql
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)
);
```

```sql
INSERT INTO item(account_id, master_item_id)
VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5);
```

### PostgreSQL
```sql
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)
);
```

```sql
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
```sql
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
```sql
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句に記述したテーブルのレコード数を掛け算したレコード数が返される