[Sy] 【技術メモ】MySQLのストアドプロシージャを使ってinsertとかdeleteとかをループさせて実行

2014/04/23

前回、前々回と MySQL の ストアドプロシージャ について書いてきました。

⇒ [Sy] 【技術メモ】MySQLで初めてストアドプロシージャを作ってみた ⇒ [Sy] 【技術メモ】MySQLのストアドプロシージャで引数を扱う

元々、ストアドプロシージャを使おうと思ったきっかけが今回書く内容で、insertやdeleteをループさせて実行するストアドプロシージャを作りたかったんです。

ということで、その手順について。

サンプルテーブル

テーブルは、以下のものを使います。

mysql> select * from member;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | utano     |
|  2 | kobayashi |
+----+-----------+
2 rows in set (0.00 sec)

まずは単にループさせてselect文を複数回実行

まずは試しに、ストアドプロシージャ内でループをさせてみます。

xからyまで、一致するidのレコードを検索するストアドプロシージャをつくります。

mysql> delimiter //
mysql> create procedure loop_select_member(in x int, in y int)
    -> begin
    ->   while x <= y do
    ->     select * from member where id = x;
    ->     set x = x + 1;
    ->   end while;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

※インデントは見やすくするために入れてますが、実際の入力では必要ないです。

ここで補足しておくと、最初のdelimiter //ってところですが、今回のストアドプロシージャをコンソール上で入力していくと、途中に;が出てくるので、普通に入力してしまうとそこでクエリが発行されちゃいます。

それを避けるために、delimiter //の部分でクエリの終端記号（delimiter）を//に変更しています。そして//でストアドプロシージャを登録。最後にdelimiter ;で終端記号を;に戻して終わり、ということです。

そして、登録したストアドプロシージャを実行すると、こうなります。

mysql> call loop_select_member(1, 2);
+----+-------+
| id | name  |
+----+-------+
|  1 | utano |
+----+-------+
1 row in set (0.00 sec)

+----+-----------+
| id | name      |
+----+-----------+
|  2 | kobayashi |
+----+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

ループしてselectが2回実行されているのがわかります。

このようにループさせることで何が便利かというと、例えば大量のデータをdeleteしたい場合など、タイムアウトが起こりやすい処理を行いたいという時にクエリを細かく分けて実行することができるので、タイムアウトを避けつつ、大量のデータを扱うことができたりします。

idのmaxを取得して、そのレコードを検索する

もう一つ試しておきます。

以下のストアドプロシージャは、idの最大値を一度select文で取得してから、その結果を使って再度select文を投げて該当のレコードを取ってくるというものです。

mysql> delimiter //
mysql> create procedure get_last_member()
    -> begin
    ->   declare max_id int;
    ->   select max(id) into max_id from member;
    ->   select * from member where id = max_id;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

これを実行すると、

mysql> call get_last_member();
+----+-----------+
| id | name      |
+----+-----------+
|  2 | kobayashi |
+----+-----------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

うまくidが最大のレコードを検索できてるのがわかります。

これは何を試したかというと、declare max_id int;の部分で宣言したmax_idというint型の変数に、最初のselect文で取得したmax(id)の値を代入して、次のクエリで使ってみるという処理です。

ループさせて大量にサンプルデータをinsertする。

ここからが本題。

ループさせてinsert文を複数回実行してくれるストアドプロシージャを作ってみます。

以下は、まずidのmaxを取得して、その値をインクリメントしながらx件のサンプルデータをinsertしていく、という処理を登録しています。

mysql> delimiter //
mysql> create procedure loop_insert_member(in x int)
    -> begin
    ->   declare max_id int;
    ->   declare i int;
    ->   set i = 0;
    ->   select max(id) into max_id from member;
    ->   while i < x do
    ->     set i = i + 1;
    ->     insert into member (max_id + i, concat('sample_', i));
    ->   end while;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

そして実行。

mysql> call loop_insert_member(3);
Query OK, 1 row affected (0.01 sec)

エラーは出ませんでした。では、実際にinsertされているのか確認します。

mysql> select * from member;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | utano     |
|  2 | kobayashi |
|  3 | sample_1  |
|  4 | sample_2  |
|  5 | sample_3  |
+----+-----------+
5 rows in set (0.00 sec)

できた！

ループさせて大量にサンプルデータをdeleteする

insertはうまくいったので、次はdeleteです。

まずidのminを取得して、x件ずつidがyに達するまで削除しつづけるストアドプロシージャをつくります。

また、確認のためにdelete文の後に、その時点のidの最小値を取得して表示するようにしています。

mysql> delimiter //
mysql> create procedure loop_delete_member(in x int, in y int)
    -> begin
    ->   declare min_id int;
    ->   declare i int;
    ->   select min(id) into min_id from member;
    ->   set i = min_id + x;
    ->   while i <= y do
    ->     delete from member where id < i;
    ->     select min(id) from member;
    ->     set i = i + x;
    ->   end while;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

いざ実行。

mysql> call loop_delete_member(10, 101);
+---------+
| min(id) |
+---------+
|      11 |
+---------+
1 row in set (0.01 sec)

+---------+
| min(id) |
+---------+
|      21 |
+---------+
1 row in set (0.01 sec)

+---------+
| min(id) |
+---------+
|      31 |
+---------+
1 row in set (0.01 sec)

+---------+
| min(id) |
+---------+
|      41 |
+---------+
1 row in set (0.01 sec)

+---------+
| min(id) |
+---------+
|      51 |
+---------+
1 row in set (0.01 sec)

+---------+
| min(id) |
+---------+
|      61 |
+---------+
1 row in set (0.01 sec)

+---------+
| min(id) |
+---------+
|      71 |
+---------+
1 row in set (0.01 sec)

+---------+
| min(id) |
+---------+
|      81 |
+---------+
1 row in set (0.01 sec)

+---------+
| min(id) |
+---------+
|      91 |
+---------+
1 row in set (0.01 sec)

+---------+
| min(id) |
+---------+
|     101 |
+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

若いidから順に、10件ずつ削除されていっているのが確認できました。

まとめ

今回初めてストアドプロシージャを使ってみて思ったこととして、

レガシーな感じがちょっと楽しい。
思っていたよりも便利。
デバッグが面倒。

というところです。特にデバッグについては、今回は簡単な例なので良かったですが、ちょっと複雑なことをしようとするとデバッグが大変そう だなーっと感じました。

どうやらストアドプロシージャをファイルに保存してそれを読み込ませる方法もあるようで、それなら多少はやりやすいかなとか思いますが、まぁ普通にスクリプト言語やらでバッチ書いたほうがいいんじゃないかと。

今回はちょっとワケあって、バッチ書いたりデプロイしたりするの手間だな・・・という状況だったのでストアドプロシージャで組んでみましたが、まわりで使ってる人を見たことなかったし。

ただ、一度作ったことで、MySQLを扱う手段の幅を広げられたのは良かったな、と思いました。

Syntax Error.