MySQLのストアドプロシージャを使ってデータを大量作成する基本メモ

MySQLのストアドプロシージャを使ってデータを作成する機会もありますよね。
自分も幾度かテストデータの作成などで利用しています。

しかし毎回ストアドプロシージャの作り方を忘れたり、作成に手間をかけることが多いので
今回はメモとしてまとめておこうと思います。

対象

MySQL: バージョン: 5.6

テーブル

desc users;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | <null>  | auto_increment |
| name         | varchar(255) | NO   |     | <null>  |                |
| age          | int(11)      | NO   |     | <null>  |                |
+--------------+--------------+------+-----+---------+----------------+

クエリ

下に解説を載せておきます。

drop procedure if exists insert_testdata;

delimiter //
create procedure insert_testdata(in x int)
begin
  declare max_id int;
  declare i int;
  declare data_length_to_create int;
  select max(id) into max_id from users;
  set i = ifnull(max_id , 0);
  set data_length_to_create = i + x;
  while i < data_length_to_create do
    set i = i + 1;
    set @i = i;
    insert into users(
        name,
        age
    )
    values (
        concat('name', @i),
        FLOOR(RAND() * 40 + 20)
    );
  end while;
end
//

delimiter ;
call insert_testdata(1000000);

クエリ解説

insert_testdata(x) 関数を作成し、xの数だけ新たにデータを生成しています。

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

drop procedure if exists insert_testdata;

すでにストアドプロシージャが存在する場合に削除します。
ストアドプロシージャを作成しながら作業をする場合には毎回更新必要があるので、
毎回処理前に実行しておきましょう。

delimiter //

区切り文字を // に定義しています。
ストアドプロシージャの中の各処理を定義する時に、区切り文字 ; を使って定義しているため、
各処理の区切りとストアドプロシージャ自体の定義の区切りを判別できるようにするためです。

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

create procedure insert_testdata(in x int)
begin
  declare max_id int;
  declare i int;
  declare data_length_to_create int;
  select max(id) into max_id from users;
  set i = ifnull(max_id , 0);
  set data_length_to_create = i + x;
  while i < data_length_to_create do
    set i = i + 1;
    set @i = i;
    insert into users(
        name,
        age
    )
    values (
        concat('name', @i),
        FLOOR(RAND() * 40 + 20)
    );
  end while;
end
//

ポイント

ポイントを３つ紹介します。

1 ifnull で条件分岐

  select max(id) into max_id from users;
  set i = ifnull(max_id , 0);

• 既存のデータの最大idを取得しています。
• ifnull関数 を使用し、もし既存のデータがない場合は 0 が代入されます。

2. concat で 変数を結合した文字列生成

concat('name', @i)

• @iを結合することで、idを含めた文字列を生成しています。

3. FLOOR(RAND() * x + y) で指定範囲中でランダムな整数を生成

FLOOR(RAND() * 40 + 20)

• FLOOR(): 引数以下のもっとも大きな整数値を返却します。
• RAND(): 0 <= v < 1.0 の範囲内で、ランダムな浮動小数点値 v を返却します。
• * x + y とすることで、 y ~ x+y-1 の範囲の生成を作成することができます。

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

delimiter ;

実際に実行する前に、区切り文字を戻しておきます。

call insert_testdata(1000000);

callを使ってストアドプロシージャを呼び出しています。今回は100万行作成しています。

最後に

今回は簡単なテーブル構成例をもとにストアドプロシージャを作成しましたが、複雑なテーブル構成となっていても応用で使える部分はあると思います。
ほぼ自分のメモのように書きましたが、どなたかの参考になってくれれば幸いです。

もっと手軽に作れるツールなどがあれば導入したいですね。