Help us understand the problem. What is going on with this article?

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

概要

MySQLでただただテスト用データが大量に欲しかったので調べて見た。

やり方

主なやり方は二つ。

  1. SQLのみで行う
  2. スクリプトを組んで行う

1. SQLのみで行う

元データを何件か登録し、その上で単純結合で増やす。

テーブル構造
CREATE TABLE IF NOT EXISTS `sample` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`data` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`create_date` datetime NOT NULL,
`update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='sampleテーブル' AUTO_INCREMENT=1 ;
INSERT INTO `sample` (`id`, `data`, `create_date`, `update_date`) VALUES
(NULL, 'sample1', '2009-07-26 00:00:00', CURRENT_TIMESTAMP),
(NULL, 'sample2', '2010-07-26 00:00:00', CURRENT_TIMESTAMP),
(NULL, 'sample3', '2011-07-26 00:00:00', CURRENT_TIMESTAMP),
(NULL, 'sample4', '2012-07-26 00:00:00', CURRENT_TIMESTAMP),
(NULL, 'sample5', '2013-07-26 00:00:00', CURRENT_TIMESTAMP),
(NULL, 'sample6', '2014-07-26 00:00:00', CURRENT_TIMESTAMP),
(NULL, 'sample7', '2015-07-26 00:00:00', CURRENT_TIMESTAMP),
(NULL, 'sample8', '2016-07-26 00:00:00', CURRENT_TIMESTAMP),
(NULL, 'sample9', '2017-07-26 00:00:00', CURRENT_TIMESTAMP),
(NULL, 'sample10', '2082-07-26 00:00:00', CURRENT_TIMESTAMP);
insert into sample(
select
sample.id = NULL,
sample.data,
sample.create_date,
sample.update_date
from sample, sample sample2, sample sample3, sample sample4, sample sample5, sample sample6, sample sample7, sample sample8
);

2. スクリプトを組んで行う

@PlanetMeron さんが書いている MySQLで大量のテストデータを作製するには??
にもある通りPythonなどでrandom関数などを活用して、数値も文字もランダムに生成する。

参考

なんとなくやりたかった。MySQLのクエリのみで簡単に1億ものデータを作成する方法 | LIG 勝利 さん
MySQLで大量のテストデータを作製するには?? | @PlanetMeron
さん

 ぼやき

テストの時エラーになりそうなパターンカバーしたテストデータとか作れないのだろうか・・・

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
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  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
ユーザーは見つかりませんでした