2013年06月19日

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乗分のデータを作成することが出来ます。

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


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

posted by 管理人 at 16:11 | データベース