タイトル通りのランダムなデータを含むレコードを大量に生成したかったのでやってみました。こういう時はストアドプロシージャを使うのがたぶん一般的じゃないかと思います。というか私がそれしか知らない…。

テーブルと前提


tokenというテーブルのaccount_idtoken_hashというカラムにそれぞれランダムな値を割り当てて、大量にINSERTしたいとします。

delimiterについて


ストアドの前にdelimiterについて。通常SQLの区切り文字は;なのですが、そのままではストアドプロシージャ内で続けて処理を実行できません。(;が出現した時点で処理が終了してしまう)のでdelimiterで区切り文字を指定してやる必要があります。

今回はdelimiterには//を指定しています。これで、ストアドは//がSQLの終わりであると認識するようになります。

ストアド作成


account_idカラムには「0~100000」のランダムな数値をtoken_hashカラムに20文字のランダムな文字列を。という条件で1000件INSERTします。

というわけで、まず初めに下記のようなものを作ってみました。

1
2
3
4
5
6
7
8
9
10
11
12
delimiter //

create procedure CreateRecords()
begin
declare n int default 1000;
while n > 0 do
INSERT INTO token(account_id, token_hash, created_at, updated_at) VALUES
(CEIL(RAND() * 100000), SUBSTRING(MD5(RAND()), 1, 20), '2018-04-20', '2018-04-20');
set n = n - 1;
end while;
end
//

実行


下記のようにcall ストアド名で作成したストアドを実行できます。

1
call CreateRecords();

これを実行してみると16.797秒かかりました。意外と遅いですね…。

BULK INSERT


というわけで、もう少し速くできないかとBULK INSERTにしてみます。MySQLのストアドはORACLEと違ってアップデートできないので下記のコマンドで先ほど作成したストアドをドロップします。

1
drop procedure CreateRecords;

BULK INSERTはVALUESの後に連続して記述することでできます。20行一気にINSERTするようにしてみました。INSERTする行数は1000行なのでループ回数は50行に設定しています。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
delimiter //

create procedure CreateRecords()
begin
declare n int default 50;
while n > 0 do
INSERT INTO token(account_id, token_hash, created_at, updated_at) VALUES
(CEIL(RAND() * 100000), SUBSTRING(MD5(RAND()), 1, 20), '2018-04-20', '2018-04-20'),
(CEIL(RAND() * 100000), SUBSTRING(MD5(RAND()), 1, 20), '2018-04-20', '2018-04-20'),
(CEIL(RAND() * 100000), SUBSTRING(MD5(RAND()), 1, 20), '2018-04-20', '2018-04-20'),
(CEIL(RAND() * 100000), SUBSTRING(MD5(RAND()), 1, 20), '2018-04-20', '2018-04-20'),
(CEIL(RAND() * 100000), SUBSTRING(MD5(RAND()), 1, 20), '2018-04-20', '2018-04-20'),
(CEIL(RAND() * 100000), SUBSTRING(MD5(RAND()), 1, 20), '2018-04-20', '2018-04-20'),
(CEIL(RAND() * 100000), SUBSTRING(MD5(RAND()), 1, 20), '2018-04-20', '2018-04-20'),
(CEIL(RAND() * 100000), SUBSTRING(MD5(RAND()), 1, 20), '2018-04-20', '2018-04-20'),
(CEIL(RAND() * 100000), SUBSTRING(MD5(RAND()), 1, 20), '2018-04-20', '2018-04-20'),
(CEIL(RAND() * 100000), SUBSTRING(MD5(RAND()), 1, 20), '2018-04-20', '2018-04-20'),
(CEIL(RAND() * 100000), SUBSTRING(MD5(RAND()), 1, 20), '2018-04-20', '2018-04-20'),
(CEIL(RAND() * 100000), SUBSTRING(MD5(RAND()), 1, 20), '2018-04-20', '2018-04-20'),
(CEIL(RAND() * 100000), SUBSTRING(MD5(RAND()), 1, 20), '2018-04-20', '2018-04-20'),
(CEIL(RAND() * 100000), SUBSTRING(MD5(RAND()), 1, 20), '2018-04-20', '2018-04-20'),
(CEIL(RAND() * 100000), SUBSTRING(MD5(RAND()), 1, 20), '2018-04-20', '2018-04-20'),
(CEIL(RAND() * 100000), SUBSTRING(MD5(RAND()), 1, 20), '2018-04-20', '2018-04-20'),
(CEIL(RAND() * 100000), SUBSTRING(MD5(RAND()), 1, 20), '2018-04-20', '2018-04-20'),
(CEIL(RAND() * 100000), SUBSTRING(MD5(RAND()), 1, 20), '2018-04-20', '2018-04-20'),
(CEIL(RAND() * 100000), SUBSTRING(MD5(RAND()), 1, 20), '2018-04-20', '2018-04-20'),
(CEIL(RAND() * 100000), SUBSTRING(MD5(RAND()), 1, 20), '2018-04-20', '2018-04-20');
set n = n - 1;
end while;
end
//

これを実行してみると…1.563秒とかなり高速化できました。

実際はもっと大量にINSERTしようとするとタイムアウトになったりすると思うのでその辺りの調整も必要になってきますが、とりあえずこれでザックリと大量のデータを作成することができます。