MySQL TIPS 6 ストアドプロシージャで指定した数のダミーデータを用意する3

mysql

ユーザ変数の文字列バッファ操作に時間が掛かっているので、一旦テンポラリーテーブルに格納してからgroup_concatでまとめて結合という手段を取ってみた。

phpで言うところの配列(array)に格納しておいて、あとでimplodeでまとめて結合するというテクニックですね

<?php
  for($i=0;$i++;$i<100000){
    $str.=$i;
  }
  echo $str

<?php
  for($i=0;$i++;$i<100000){
    $str[]=$i;
  }
  echo implode('',$str);

解説

  • ストアドプロシージャ中でテンポラリーテーブルを使っているので、事前に同名のテンポラリーテーブルがある場合はDROP TABLE IF EXISTSを使って初期化している。DROP TABLE IF EXISTはテーブルが既に存在していた場合にWARNINGを吐くので、前後でset session sql_notesを使って警告を無効化している。
  • GROUP CONCATの最大長が巨大になるので、buf_sizeにクエリー長を計算した値を格納しgroup_concat_max_lenを設定
DROP   PROCEDURE insert_generate_series;
delimiter //
CREATE PROCEDURE insert_generate_series(
   IN target    VARCHAR(255) , -- insert対象のテーブル、カラム名を指定
   IN value     VARCHAR(255) , -- valuesを記述 @numがgenerate_seriesで生成された値になる
   IN start_num INT ,          -- 開始値
   IN end_num   INT ,          -- 終了値
   IN step      INT            -- 増分
)
begin
  set @num:= start_num;
  set @sql:= concat('INSERT INTO ',target,' VALUES ');
  set session sql_notes=0;
  drop temporary table if exists t_values;
  set session sql_notes=1;
  create temporary table t_values (val varchar(255));
  set @buf_size:=length(@sql);
  while @num <= end_num do
    set @buf:=concat('(',replace(value,'@num',@num),')');
    insert into t_values VALUES ( @buf );
    set @buf_size:=@buf_size+length(@buf)+1;
    set @num:=@num + step;
  end while;
  set session group_concat_max_len=@buf_size;
  set session max_allowed_packet=@buf_size;
  set @sql:= concat(@sql,(select group_concat(val) from t_values));
  prepare stmt from @sql;
  execute stmt; 
end;
//
delimiter ;

call insert_generate_series('user(user_name)','concat(md5(@num),@num)',1,100000,1);

Query OK, 100000 rows affected (5.52 sec)

めっちゃ早くなった。

8/4追記

  • max_allowed_packet不足になっていたので設定を追加
  • max_allowed_packetがread_onlyなので困った
  • メモリを消費し過ぎなので、折衷案を検討中