SQLに慣れるにあたり、必須とも言えるのがテストデータの作成だ。色々調べてみたが、テストデータ作成の方法には以下の２つがあるらしい。
- SQLで直にデータを生成する。
- スクリプトファイルを呼び出してデータを生成する。
後者は、事前にプログラムを実行してスクリプトファイルを生成しなければならない。プログラムを書くとなると手間と時間がかかりそうだ・・・。（しかし、ライブラリ等を利用すればよりリアルなデータが作成できると思う。）
そのため、今回は前者の方法でテストデータを作成してみた。作成にあたっては、以下の記事を参考にさせていただいた。同記事内で紹介されている方法とほぼ同じ方法で進めるが、アウトプットのため自分でも記事を書くことにする。
MySQLの関数を使用する
MySQLには様々な関数がある。公式サイトを見ると途方も無い数に思えるが、この中からテストデータの作成に役に立つ関数を紹介する。
単純に関数を実行したい場合は、データベースやテーブルが無くとも、MySQLにログインさえすればSELECT文で実行可能だ。色々遊びながら試すことができる。
公式サイトに事細かに紹介されているので、こちらも参考に。
文字列の生成
英数字の文字列（４文字）を生成する。文字列は16進数として生成されるので、英字はa~fの範囲となる。
これだけでも、既に３つの関数が使用されている。
mysql> SELECT SUBSTRING(MD5(RAND()), 1, 4);
+------------------------------+
| SUBSTRING(MD5(RAND()), 1, 4) |
+------------------------------+
| f508 |
+------------------------------+
1 row in set (0.00 sec)
-
RAND関数で0以上1未満の数字（浮動小数点値）を生成する。 -
MD5関数により、生成された数字を32文字の16進数に変換する。（本来は暗号化に使用される関数らしい。） -
SUBSTRING関数に引数を指定し、生成された文字列の1番目から4文字分を取り出す。
数字の生成
10000以下の数字を生成する。
mysql> SELECT CEIL(RAND() * 10000);
+----------------------+
| CEIL(RAND() * 10000) |
+----------------------+
| 3121 |
+----------------------+
1 row in set (0.00 sec)
-
RAND関数で0以上1未満の数字（浮動小数点値）を生成し、10000を乗算する。 -
CEIL関数で指定した引数以上で最小の整数値を返す。（仮に引数が9999.99の場合は、10000が返される。戻り値は0~10000の範囲となる。）
日付の生成
2018年中の日付を生成する。少し曖昧な点があった。
mysql> SELECT DATE_ADD('2018-1-1', INTERVAL 365*RAND() DAY);
+-----------------------------------------------+
| DATE_ADD('2018-1-1', INTERVAL 365*RAND() DAY) |
+-----------------------------------------------+
| 2018-09-22 |
+-----------------------------------------------+
1 row in set (0.00 sec)
-
DATE_ADD関数を使用し、引数に基準日と間隔（INTERVAL）、単位（DAY）を指定する。 - 間隔は365に
RAND関数を乗算しているため、0~365になる。（仮に365になった場合、'2019-01-01'になってしまう？） - INTERVAL後に指定した間隔の値は
四捨五入されるようだ。そのため364*RAND()と指定すべきだろうか？
以下のSQLでは0~3年間隔でランダムな日付を生成しているため、2018年から2021年までの1月1日が生成対象となる。
mysql> SELECT DATE_ADD('2018-1-1', INTERVAL 3*RAND() YEAR);
+----------------------------------------------+
| DATE_ADD('2018-1-1', INTERVAL 3*RAND() YEAR) |
+----------------------------------------------+
| 2021-01-01 |
+----------------------------------------------+
1 row in set (0.00 sec)
テストデータを作成する
データベースの作成
sample_dbデータベースを作成する。
mysql> CREATE DATABASE sample_db;
Query OK, 1 row affected (0.01 sec)
USE文で使用データベースを切り替える。SELECT DATABASE();で、現在使用しているデータベースの確認もできる。
mysql> USE sample_db;
Database changed
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| sample_db |
+------------+
1 row in set (0.00 sec)
テーブルの作成
money_checkテーブルを作成する。
カラムはid,name,date,income（収入）,expense（支出）とする。PRIMARY KEY（主キー）であるidは、AUTO_INCREMENTを指定して自動的に番号が付与されるようにした。
mysql> CREATE TABLE money_check (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(4),
-> date DATE,
-> income INT,
-> expense INT
-> );
Query OK, 0 rows affected (0.02 sec)
テストデータの生成
空データを作成していく。INSERT文はSELECT文で抽出したデータをそのまま挿入することができる。
まず最初に、空データを1件作成する。その後はSELECT文で抽出した現存するデータ（idカラムを除く）をそのまま挿入し、データ数を倍増させている。
SELECT文で選択列を指定しているが、0というのは（先に選択した）id以外の列という解釈で良いのだろうか？
しかし、以下のSQLでは連番になるはずのidがところどころ抜けてしまった。理由はわからない・・・。
mysql> INSERT INTO money_check
-> VALUES ();
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO money_check (id)
-> SELECT 0 FROM money_check;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> INSERT INTO money_check (id) SELECT 0 FROM money_check;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO money_check (id) SELECT 0 FROM money_check;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> INSERT INTO money_check (id) SELECT 0 FROM money_check;
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
その後、UPDATE文で関数を利用してランダムな値を生成し、空データを上書きする。WHERE句を利用しないので、全てのデータに対してUPDATE文が適用される。
mysql> UPDATE money_check SET
-> name = SUBSTRING(MD5(RAND()), 1, 4),
-> date = DATE_ADD('2018-1-1', INTERVAL 365*RAND() DAY),
-> income = CEIL(RAND() * 10000),
-> expense = CEIL(RAND() * 10000);
Query OK, 16 rows affected (0.01 sec)
Rows matched: 16 Changed: 16 Warnings: 0
これで各カラムにランダムに生成されたデータが上書きされた。若干理解ができなかった部分もあるが、このまま進めることにする。
これからSQL文を覚えるにあたり、このテーブルを利用したいと思う。
今回はここまで。