MySQL の procedure で大量のデータを用意してみる（速くはない）

適当に以下のようなデータをほしいとします。今回は10万レコードを作成してみます。

その1 procedure でやってみる

create table hoge (num int, name varchar(255));

delimiter //
create procedure insert_hoge(in x int)
begin
  declare i int default 1;
  set @q = "insert into hoge values ()";
  while i < x do
    set i = i + 1;
    set @q = concat(@q, ',()');
  end while;
  prepare stmt from @q;
  execute stmt;
end
//
delimiter ; 

call insert_hoge(100000);

/* 適当に null で10万行埋めてから、update でランダムな値にする */

update hoge set
  num = round(rand()*100000),
  name = concat('name', round(rand()*100000));

これはそんなに速くなくて15秒くらいかかります。

その2 cross join でやってみる

こっちの方が早いです5秒くらいですみます。

create table piyo (num int, name varchar(255));

insert into piyo values (), (), (), (), (), (), (), (), (), ();

insert into piyo (select piyo.num, piyo.name from piyo, piyo p2, piyo p3, piyo p4, piyo p5);

update piyo set
  num = round(rand()*100000),
  name = concat('name', round(rand()*100000));

その3 procedure で、値もいれてみる

update を無くして、最初からランダムを insert してみます。これは遅くて30秒くらい

delimiter //
create procedure hoge(in x int)
begin
  declare i int default 0;
  set @q = "insert into hoge (num, name) values ";
  while i < x do
    set i = i + 1;
    set @name_value = concat('name', round(rand()*10000));
    set @value = concat('(', i , ', "', @name_value, '")');
    if i = 1 then
      set @q = concat(@q, @value);
    else
      set @q = concat(@q, concat(',', @value));
    end if;
  end while;
  prepare stmt from @q;
  execute stmt;
end
//
delimiter ;

その4 procedure でバルクインサートしない場合

while の中で毎回 insert などをすると、たぶん2分以上かかると思います。

まとめ

procedure でぴったり狙った件数のデータを出すのはかっこよさそうですが、速さは cross join でやる方が早いので、そちらの方が実用的かと思います。

procedure の中で、user-defined variable や prepared statement 使ってみたりして勉強になりました。