MySQLの簡単なサンプルデータを作成する方法

概要

デモサイト構築やDBの正規化について学習する上で、作り込み過ぎない（第3正規形程度）、なるべく最小限の入力情報でデータ登録ができるようサンプルデータ環境を作成方法について考えてみました。

バージョン

MySQL7.4

テーブル一覧

• ordersテーブル（注文情報）

colmun	type	option
id	INT	PK / NN / AI
stationary_id	INT
factory_id	INT
description	VARCHAR
volume	INT
total_price	INT
created_at	DATETIME	DEFAULT CURRENT_TIMESTAMP

• factoryテーブル（生産工場マスタ）

colmun	type	option
id	INT	PK / NN / AI
name	VARCHAR
price	INT
created_at	DATETIME	DEFAULT CURRENT_TIMESTAMP

• stationaryテーブル（商品情報マスタ）

colmun	type	option
id	INT	PK / NN / AI
name	VARCHAR
price	INT
created_at	DATETIME	DEFAULT CURRENT_TIMESTAMP

テーブル作成・データ登録

-- 製品工場マスタテーブル作成、データ登録
CREATE TABLE factory (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(30),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO factory(name) VALUES ('京都府');
INSERT INTO factory(name) VALUES ('大阪府');
INSERT INTO factory(name) VALUES ('兵庫県');
INSERT INTO factory(name) VALUES ('滋賀県');
INSERT INTO factory(name) VALUES ('奈良県');


-- 商品情報マスタテーブル作成、データ登録
CREATE TABLE stationary (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(30),
  price INT UNSIGNED,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO stationary(name) VALUES ('消しゴム');
INSERT INTO stationary(name) VALUES ('鉛筆');
INSERT INTO stationary(name) VALUES ('シャーペン');
INSERT INTO stationary(name) VALUES ('定規');
INSERT INTO stationary(name) VALUES ('修正テープ');
INSERT INTO stationary(name) VALUES ('クリップ');

-- 商品情報マスタ情報更新
UPDATE stationary SET
  price = CEIL(RAND() * 15) * 100 -- 単価
;

-- 注文情報テーブル作成、データ登録
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  stationary_id INT,
  factory_id INT,
  description VARCHAR(30),
  volume INT,
  total_price INT UNSIGNED,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO items () VALUES (); -- 1件
INSERT INTO items (id) SELECT 0 FROM items; -- 2件
INSERT INTO items (id) SELECT 0 FROM items; -- 4件
INSERT INTO items (id) SELECT 0 FROM items; -- 8件
INSERT INTO items (id) SELECT 0 FROM items; -- 16件
INSERT INTO items (id) SELECT 0 FROM items; -- 32件登録

-- 注文情報更新１
-- FKとしてそれぞれのPKの最大値を上限としたランダムな値をセット
UPDATE orders SET
  stationary_id = CEIL(RAND() * (SELECT MAX(stationary.id) from stationary)),
  factory_id = CEIL(RAND() * (SELECT MAX(factory.id) from factory)),
  volume = CEIL(RAND() * 10)
;

-- 注文情報更新２
-- 商品説明、価格合計を生成してセット
UPDATE orders SET
  description = CONCAT(
    (SELECT name FROM factory WHERE factory.id = orders.factory_id),
    "の",
    (SELECT name FROM stationary WHERE stationary.id = orders.stationary_id)
  ),
  total_price = (
    (SELECT price FROM stationary WHERE stationary.id = orders.stationary_id)
    *
    volume
  )
;