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`）が格納されます。

**テーブル作成クエリ**

```sql
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`を使用する。
構文はざっくり以下の通り。

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

## 設計
- **プロシージャ名**
    - insert_schedule
- **引数**
    - 作成する予定の数。引数名はcountとする。
- **処理内容**
    - countで指定した数の予定を作成する。各カラム値は以下の通り。
        - **TITLE**
            - 10文字以内のランダムな文字列
        - **START_DATE_TIME**
            - 現在から大体100日以内。
        - **END_DATE_TIME**
            - 開始日時から100分以内。
           
## 実装
作成したプロシージャは以下の通り。

```sql

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`で呼び出します。

```sql
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で簡単にランダムなテストデータを作成する方法](https://qiita.com/tayasu/items/c5ddfc481d6b7cd8866d)
[13.1.17 CREATE PROCEDURE and CREATE FUNCTION Syntax
](https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html)