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

• プロシージャ名
  • プロシージャ名を記載する。呼び出す際はここで定義されたプロシージャ名を指定する必要がある。
• 引数
  • 処理内で使用する引数、戻り値として受け取る変数などを指定する。
• 処理内容
  • 実際の処理内容のクエリを記載（SELECTやINSERTなど）。

設計

• プロシージャ名
  • 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