MySQLでダミーデータを入れるときの小技

はじめに

　MySQLでテスト用に大量データを用意しなければならないことはよくあります。JavaやPHPなど、プログラムからInsertしても良いのですが、SQLだけでできるならそれに越したことはありません。試してみます。
　※類似コンテンツはインターネットでざっくり調べたところたくさんありました。NOT NULLでUNIQEのカラムの対処や、UNIQE列へのInsertでランダムに頼らないところ、動的SQLへの展開が主な差分です。

ユーザテーブルにダミーレコードを入れる

　DDLは次です。Laravel（PHP）でよく出現する形にしました。

CREATE TABLE `users` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`email_verified_at` timestamp NULL DEFAULT NULL,
`password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `users_email_unique` (`email`)
);

1行だけレコードを手で入れます。

MySQLではSelectした結果をSelectしたテーブルにInsertできます。
次のようなクエリを書き、動かない部分を少し調整すればOKです。

insert into users (id, name, email, email_verified_at, password, remember_token, created_at, updated_at)
select
`users`.`id` as 'users.id', 
`users`.`name` as 'users.name', 
`users`.`email` as 'users.email',
`users`.`email_verified_at` as 'users.email_verified_at', 
`users`.`password` as 'users.password', 
`users`.`remember_token` as 'users.remember_token', 
`users`.`created_at` as 'users.created_at', 
`users`.`updated_at` as 'users.updated_at'
FROM
users;

このままだとidの列が衝突してしまうためエラーになります。idの列を省略するという方法もありますが、私の環境では、insertされたauto_incrementの列が歯抜けになってしまう現象が起こり、原因がすぐにわからなかったので、idの列を指定することにしました。修正後のクエリは次です。

SET @max_id = 0;
SELECT MAX(id) INTO @max_id FROM users;
SELECT @max_id;

insert into users (id, name, email, email_verified_at, password, remember_token, created_at, updated_at)
select
`users`.`id` + @max_id as 'users.id', 
`users`.`name` as 'users.name', 
`users`.`email` as 'users.email',
`users`.`email_verified_at` as 'users.email_verified_at', 
`users`.`password` as 'users.password', 
`users`.`remember_token` as 'users.remember_token', 
`users`.`created_at` as 'users.created_at', 
`users`.`updated_at` as 'users.updated_at'
FROM
users;

idを、もとのmax(id)を取ってぶつからないようにしました。これで実行すると、emailの列のユニーク制約に引っかかります。ランダム文字列に頼っても良いかもしれませんが、確率的な事象を山盛りにすると思わぬところでトラブルになったりするので、idをフォーマットした数字(インクリメンタル)の列にします。これならば用意するデータの件数に注意しておけば衝突する心配がありません。

SET @max_id = 0;
SELECT MAX(id) INTO @max_id FROM users;
SELECT @max_id;

insert into users (id, name, email, email_verified_at, password, remember_token, created_at, updated_at)
select
`users`.`id` + @max_id as 'users.id', 
`users`.`name` as 'users.name', 
LPAD(@max_id + id, 5, '0') as 'users.email',
`users`.`email_verified_at` as 'users.email_verified_at', 
`users`.`password` as 'users.password', 
`users`.`remember_token` as 'users.remember_token', 
`users`.`created_at` as 'users.created_at', 
`users`.`updated_at` as 'users.updated_at'
FROM
users;

こんな感じでレコードが入ります。

ユニーク制約のある列に何かをInsertするとき、どうしてもランダム文字列に頼る必要がある場合は丁寧にクエリを書くと手間ですが、例えば乱数のMD5を取って頭からn桁みたいにすれば、ランダム文字列に近い性質の文字列を作ることができます。文字セットを指定するようなランダム文字列が必要になったら便利なプログラミング言語に逃げましょう。

横に長いテーブルのクエリを作る

　カラム数の多いテーブルのときは、SQLを作るだけでも一苦労です。MySQLではテーブルのカラム一覧を得るのが容易なので、SQLを作るSQLを書くことができます。

SET group_concat_max_len = 65535; /* concatの結果が切れないようにする */

SET @tbl = 'users'; /* selectしたいテーブル */
SET @inscols = 
  (
    SELECT
      GROUP_CONCAT(COLUMN_NAME SEPARATOR ', \n')
    FROM
      INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = database()
      AND TABLE_NAME = @tbl
  );
SET @selcols = 
  (
    SELECT
      GROUP_CONCAT(CONCAT('`', @tbl ,'`.`',COLUMN_NAME,'` AS \'', @tbl , '.', COLUMN_NAME, '\'') SEPARATOR ', \n')
    FROM
      INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = database()
      AND TABLE_NAME = @tbl
  );
 

SELECT CONCAT(
'INSERT INTO ', @tbl, ' (',
@inscols,
')\n',
'SELECT\n', 
@selcols,'\n'
'FROM\n', 
@tbl,'\n'
);

このクエリの出力は

INSERT INTO users (id, 
name, 
email, 
email_verified_at, 
password, 
remember_token, 
created_at, 
updated_at)
SELECT
`users`.`id` AS 'users.id', 
`users`.`name` AS 'users.name', 
`users`.`email` AS 'users.email', 
`users`.`email_verified_at` AS 'users.email_verified_at', 
`users`.`password` AS 'users.password', 
`users`.`remember_token` AS 'users.remember_token', 
`users`.`created_at` AS 'users.created_at', 
`users`.`updated_at` AS 'users.updated_at'
FROM
users

このようになります。
このままだと先ほどのエラーが含まれているので、いくつか帳尻を合わせて、次のように完成させることができます。

SET @max_id = 0;
SELECT MAX(id) INTO @max_id FROM users;
SELECT @max_id;

INSERT INTO users (id, 
name, 
email, 
email_verified_at, 
password, 
remember_token, 
created_at, 
updated_at)
SELECT
@max_id + id AS 'users.id', 
`users`.`name` AS 'users.name', 
LPAD(@max_id + id, 5, '0') AS 'users.email', 
`users`.`email_verified_at` AS 'users.email_verified_at', 
`users`.`password` AS 'users.password', 
`users`.`remember_token` AS 'users.remember_token', 
`users`.`created_at` AS 'users.created_at', 
`users`.`updated_at` AS 'users.updated_at'
FROM
users

微調整する箇所が増えたら、ユニーク制約のあるカラムはLPAD(@max_id + id, 5, '0') に置換するといった仕掛けを用意すれば手間は省けます。SQLに組み込んでも良いですし、エディタで置換する方法でも十分だと思います。
　SQLに仕掛けるならば、MySQLの動的SQLという仕掛けを使うと便利です。次のSQLを実行するだけでusersテーブルのレコードが1回の実行につき2倍のレコード数になります。

SET group_concat_max_len = 65535; /* concatの結果が切れないようにする */

SET @tbl = 'users'; /* selectしたいテーブル */
SET @inscols = 
  (
    SELECT
      GROUP_CONCAT(COLUMN_NAME SEPARATOR ', \n')
    FROM
      INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = database()
      AND TABLE_NAME = @tbl
  );
SET @selcols = 
  (
    SELECT
      GROUP_CONCAT(CONCAT('`', @tbl ,'`.`',COLUMN_NAME,'` AS \'', @tbl , '.', COLUMN_NAME, '\'') SEPARATOR ', \n')
    FROM
      INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = database()
      AND TABLE_NAME = @tbl
  );
 

SET @selcols = REPLACE(@selcols, '`users`.`id`', '`users`.`id` + @max_id');
SET @selcols = REPLACE(@selcols, '`users`.`email`', 'LPAD(@max_id + id, 5, \'0\')');

SET @query = CONCAT(
'INSERT INTO ', @tbl, ' (',
@inscols,
')\n',
'SELECT\n', 
@selcols,'\n'
'FROM\n', 
@tbl,'\n'
);

SET @max_id = 0;
SELECT MAX(id) INTO @max_id FROM users;
SELECT @max_id;

PREPARE insert_data FROM @query;
EXECUTE insert_data;
DEALLOCATE PREPARE insert_data;