MySQL

MySQL ストアドプロシージャでテストデータ作成

実行計画を取りたくて、ストアドプロシージャでテストデータを作成した時のメモ。

MySQLのバージョンは8.0。


やりたいこと

予定情報が入っているSCHEDULEテーブルに、ランダムなテストデータを大量に投入したい。


テーブル定義

SCHEDULEテーブルの定義は以下の通りです。

+-----------------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+----------------+
| SCHEDULE_ID | int(11) | NO | PRI | NULL | auto_increment |
| TITLE | varchar(10) | NO | | NULL | |
| START_DATE_TIME | datetime | NO | | NULL | |
| END_DATE_TIME | datetime | NO | | NULL | |
+-----------------+-------------+------+-----+---------+----------------+

主キーのSCHEDULE_IDが連番で振られ、予定のタイトル(TITLE)と開始日時(START_DATE_TIME)、終了日時(END_DATE_TIME)が格納されます。

テーブル作成クエリ

CREATE TABLE SCHEDULE

(SCHEDULE_ID INT, TITLE VARCHAR(10), START_DATE_TIME DATETIME, END_DATE_TIME DATETIME);

ALTER TABLE SCHEDULE ADD PRIMARY KEY(SCHEDULE_ID);
ALTER TABLE SCHEDULE MODIFY SCHEDULE_ID INT AUTO_INCREMENT;
ALTER TABLE SCHEDULE MODIFY TITLE VARCHAR(10) NOT NULL;
ALTER TABLE SCHEDULE MODIFY START_DATE_TIME DATETIME NOT NULL;
ALTER TABLE SCHEDULE MODIFY END_DATE_TIME DATETIME NOT NULL;

格納データ例

+-------------+--------+---------------------+---------------------+

| SCHEDULE_ID | TITLE | START_DATE_TIME | END_DATE_TIME |
+-------------+--------+---------------------+---------------------+
| 1 | 焼肉 | 2019-01-20 19:00:00 | 2019-01-20 21:00:00 |
| 2 | 温泉 | 2019-01-23 20:00:00 | 2019-01-23 22:00:00 |
| 3 | 飲み会 | 2019-02-01 19:00:00 | 2019-02-01 22:00:00 |
+-------------+--------+---------------------+---------------------+


ストアドプロシージャ作成

ここからが本題。

MySQLでは実行クエリが書かれたストアドプロシージャを作り、それを呼び出すことでクエリを実行することができる。

プロシージャ内でWHILEを使って繰り返し処理を記述することができ、大量のデータ投入を自動化できるのでとても便利。


構文

プロシージャを作成する際はCREATE PROCEDUREを使用する。

構文はざっくり以下の通り。

CREATE PROCEDURE プロシージャ名 ([引数[,...]])

BEGIN
処理内容
END



  • プロシージャ名


    • プロシージャ名を記載する。呼び出す際はここで定義されたプロシージャ名を指定する必要がある。




  • 引数


    • 処理内で使用する引数、戻り値として受け取る変数などを指定する。




  • 処理内容


    • 実際の処理内容のクエリを記載(SELECTINSERTなど)。




設計



  • プロシージャ名


    • insert_schedule




  • 引数


    • 作成する予定の数。引数名はcountとする。




  • 処理内容


    • countで指定した数の予定を作成する。各カラム値は以下の通り。



      • TITLE


        • 10文字以内のランダムな文字列




      • START_DATE_TIME


        • 現在から大体100日以内。




      • END_DATE_TIME


        • 開始日時から100分以内。








実装

作成したプロシージャは以下の通り。


DELIMITER //
CREATE PROCEDURE insert_schedule(IN count INT)
BEGIN
DECLARE i INT;

SET i = 0;
WHILE i < count DO
INSERT INTO SCHEDULE (
TITLE,
START_DATE_TIME,
END_DATE_TIME
)
VALUES (
LEFT(MD5(RAND()), RAND() * 10),
DATE_FORMAT(NOW() + INTERVAL RAND() * 100 DAY + INTERVAL RAND() * 100 HOUR + INTERVAL RAND() * 100 MINUTE, '%Y-%m-%d %H:%i'),
START_DATE_TIME + INTERVAL RAND() * 100 MINUTE
);

SET i = i + 1;

END WHILE;

END//
DELIMITER ;


処理的な解説


DELIMITER

MySQLはデフォルトの区切り文字が;なので、DELIMITERを変更せずにこのクエリを流すと最初のDECLARE i INT;で一旦クエリが終了しエラーになります。

なので、最初にDELIMITER //で区切り文字を変更し、CREATE PROCEDUREを全部読み終わったあとにDELIMITER ;で区切り文字を戻しています。


WHILE

WHILE i < count DOで引数にセットしたcountの数だけINSERTを繰り返しています。カウンタ変数はiで、WHILEの前に0で初期化し、毎回インクリメントしています。


データ的な解説


TITLE

10文字以内のランダムな文字列を作成しています。

MD5(RAND())でランダムな文字列を作成し、LEFT()でその文字列を指定した長さでカットしています。長さはRAND() * 10で10文字以内の長さを作っています。


START_DATE_TIME

現在日時をNOW()で取得し、適当な日時分をINTERVAL ~で加算しています。秒以下の情報はいらなかったのでDATE_FORMAT()で削っています。


END_DATE_TIME

START_DATE_TIMEに100分以内の時間をINTERVAL RAND() * 100 MINUTEで加算しています。


ストアドプロシージャ実行

作成したプロシージャは以下のようにCALLで呼び出します。

CALL insert_schedule(10);

ランダムな感じのデータが作成されました。

+-------------+-----------+---------------------+---------------------+

| SCHEDULE_ID | TITLE | START_DATE_TIME | END_DATE_TIME |
+-------------+-----------+---------------------+---------------------+
| 1 | 092 | 2019-04-25 02:01:00 | 2019-04-25 02:46:00 |
| 2 | d8378c | 2019-04-02 13:59:00 | 2019-04-02 15:22:00 |
| 3 | 2a | 2019-04-04 13:41:00 | 2019-04-04 14:21:00 |
| 4 | 2cfd1 | 2019-03-13 08:16:00 | 2019-03-13 08:51:00 |
| 5 | | 2019-02-18 23:25:00 | 2019-02-18 23:57:00 |
| 6 | 7 | 2019-02-12 15:17:00 | 2019-02-12 16:48:00 |
| 7 | 01 | 2019-04-26 23:04:00 | 2019-04-27 00:43:00 |
| 8 | 656c882c3 | 2019-03-06 19:17:00 | 2019-03-06 19:54:00 |
| 9 | d80 | 2019-03-15 00:21:00 | 2019-03-15 00:58:00 |
| 10 | cc93316 | 2019-04-20 17:16:00 | 2019-04-20 17:19:00 |
+-------------+-----------+---------------------+---------------------+


参考

MySQLで簡単にランダムなテストデータを作成する方法

13.1.17 CREATE PROCEDURE and CREATE FUNCTION Syntax