MySQLのクエリだけでダミーデータを大量に挿入してみる

今年の梅雨はどこいったんだと思ってたら
大阪ではここ数日まとまった雨になりそうですね。
お久しぶりです。タイラです。

さて今回は珍しくデータベースのお話で
MySQLのクエリだけでダミーデータを大量に挿入する小技を紹介します。

以下のようなテーブルに対して10万件のダミーデータを入れてみます。

 CREATE TABLE IF NOT EXISTS `sample` (
  `id`         int(11)      NOT NULL AUTO_INCREMENT,
  `data`       varchar(128) NOT NULL,
  `created_at` datetime     NOT NULL,
  `updated_at` timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 +------------+--------------+------+-----+-------------------+----------------+
 | Field      | Type         | Null | Key | Default           | Extra          |
 +------------+--------------+------+-----+-------------------+----------------+
 | id         | int(11)      | NO   | PRI | NULL              | auto_increment |
 | data       | varchar(128) | NO   |     | NULL              |                |
 | created_at | datetime     | NO   |     | NULL              |                |
 | updated_at | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
 +------------+--------------+------+-----+-------------------+----------------+

まず最終データと同じ形式のダミー用テンポラリテーブルを作成します。

 CREATE TABLE IF NOT EXISTS `tmp` (
  `id`         int(11)      NOT NULL AUTO_INCREMENT,
  `data`       varchar(128) NOT NULL,
  `created_at` datetime     NOT NULL,
  `updated_at` timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

テンポラリテーブルに対してダミーの元になるデータを入れます。

 INSERT INTO `tmp`
  (`data`, `created_at`)
 VALUES
  ('サンプル', NOW()),
  ('サンプル', NOW()),
  ('サンプル', NOW()),
  ('サンプル', NOW()),
  ('サンプル', NOW()),
  ('サンプル', NOW()),
  ('サンプル', NOW()),
  ('サンプル', NOW()),
  ('サンプル', NOW()),
  ('サンプル', NOW());

ダミーデータを対象となるテーブルへ挿入します。

 INSERT INTO `sample`(
  SELECT t1.id = NULL, t1.data, t1.created_at, t1.updated_at
  FROM (tmp t1, tmp t2, tmp t3, tmp t4, tmp t5)
 );

不要になったテンポラリテーブル削除します。

 DROP TABLE `tmp`;

これで挿入された件数を確認すると

 SELECT COUNT(*) FROM `sample`;

 +----------+
 | COUNT(*) |
 +----------+
 |   100000 |
 +----------+

今回の場合ですと、元データが10件で5回クロス結合しているので
10万件のレコードが入ります。
挿入時の tmp t1, tmp t2 ... tmp tN と増やすことで
元データのN乗分のデータを作成することが出来ます。

※ただしクエリの実行時間やメモリ消費量は一切考慮してないので
億単位のデータを挿入する場合は注意が必要です。

あまり実用性は低いですが、ネタの一つとして覚えておくと良いかもしれません。
それではー