MySQL でダミーデータの作り方

MySQL でダミーデータを作る

結論から書いていきます。

まずはデータベースとテーブルを作成します。

CREATE DATABASE `sample`;

CREATE TABLE `sample`.`parent`
(
  `id` INT NOT NULL AUTO_INCREMENT,
  `count` INT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `sample`.`child`
(
  `id` INT NOT NULL AUTO_INCREMENT,
  `parent_id` INT NOT NULL,
  `age` INT NOT NULL,
  PRIMARY KEY (`id`, `parent_id`),
  INDEX `idx_parent_id` (`parent_id` ASC),
  CONSTRAINT `fk_parent_id`
    FOREIGN KEY (`parent_id`)
    REFERENCES `sample`.`parent` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

別にどんなテーブルでもいいですが、child は parent に外部参照するような基本的な構成です。

では、作成したテーブルを用いて、ダミーデータを作成しましょう！

-- INSERTするテーブルをselectすることで指数関数的にデータを増やします
-- 今回は3万件を超えるデータを作成しています
INSERT INTO `sample`.`parent` (`id`,`count`) VALUES (1, 0);
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;

-- INSERT child
INSERT INTO `sample`.`child` (`parent_id`, `age`) select id, 10 from `sample`.`parent` ORDER BY RAND();

これだけです。

ダミーデータ増やし方の説明

肝の部分を説明します

2 箇所大事なところがあります

データを増やすこと
→ INSERT 時の VALUES 部分に適当なテーブルを指定して SELECT すること
ランダムなデータを増やすこと
→ INSERT 時の VALUES 部分に適当なテーブルを指定してランダムな順番になるように SELECT すること

データを増やすこと

まずはダミーデータを増やすところです

INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;

1 件以上データが入っているテーブルを SELECT して返ってくるカラム数と同等のデータを作成する処理を SQL で表現しています

この SQL は叩けば叩くほど指数関数的にデータが増えていきます

通常 INSERT 文を作成する時は以下のようになっています。 INSERT する順番と INSERT する値を書いているのですね。

INSERT INTO `sample`.`parent` (`count`) VALUES (??)

この INSERT 文の ?? 部分に入る値を SELECT 文を使って選択しているのが今回用いているダミーデータ作成の肝です。これにより、VALUES の中には SELECT 文で返ってきた行数分のデータを INSERT できるようになるのです。

例えば、 sample.parent の中身が 1 件であれば１件分。10000 件であれば、10000 件のレコードを INSERT できるような計算になります。また、このクエリは叩けば叩くほどに、指数関数的に INSERT 文が発行される仕組みなります。

INSERT INTO `sample`.`parent` (`id`,`count`) VALUES (1, 0);
-- 合計1件のレコードが生成されている

---------------- 以下データを増やす処理 ------------------

INSERT INTO `sample`.`parent` (`count`) VALUES (??);
-- 合計2件のレコードが生成されている状態

INSERT INTO `sample`.`parent` (`count`) VALUES (??);
-- 合計4件のレコードが生成されている状態

INSERT INTO `sample`.`parent` (`count`) VALUES (??);
-- 合計8件のレコードが生成されている状態

INSERT INTO `sample`.`parent` (`count`) VALUES (??);
-- 合計16件のレコードが生成されている状態

INSERT INTO `sample`.`parent` (`count`) VALUES (??);
-- 合計32件のレコードが生成されている状態

INSERT INTO `sample`.`parent` (`count`) VALUES (??);
-- 計64件のレコードが生成されている状態

こんな流れになっています

INSERT でデータを増やす → 増やしたデータを持つテーブルで INSERT してデータを増やす

ランダムなデータを増やすこと

次にランダムなデータを生成します。

今回はテーブルにあるデータを使って生成しています。

INSERT INTO `sample`.`child` (`parent_id`, `age`) select id, 10 from `sample`.`parent` ORDER BY RAND();

データを増やすことでやった手法にランダム要素を加えただけです。RAND() 関数を使って、あるテーブルのデータをランダムな順番で引っ張ってくるだけです。また、今回の場合には sample.parent のダミーデータを増やした後だったので、そのテーブルに対して SELECT をすれば一発で大量のダミーデータを生成することができました。

sample.parentにあるデータ(select id ←これです from sample.parent)を SELECT しましたが、別にランダムな値は自作で生成しても大丈夫です。

SUBSTRING(MD5(RAND()), 1, 10) -- ランダムな文字列の生成
CEIL(RAND() * 100) -- ランダムな数値の生成

まとめ

開発しているとデータが増えた場合の挙動や負荷対策をする状況が発生しますが、今回の記事を参考に何百万とあるダミーデータテーブルを作成してスロークエリが発生しないかを調査してみてはいかがでしょうか。