仕事でデータ移行するときに、ストアドプロシージャを使ったので基本的なことをまとめておく。
※ MySQLでしかストアドプロシージャを使ったことがないので、MySQL前提で。
プロシージャ必要なら Ruby/PHP/Python/Go とかでスクリプト書けば良くないって思うけど、どうなんだろね?
ストアドプロシージャとは?
DBへの一連の操作をひとまとめにして、関数のように呼び出せるもの。
プロシージャを使えば、データを1万件ずつループしてINSERT/UPDATEするみたいな処理を書くことが可能。
大規模なデータ移行のときに役立ちそう!!
ストアドプロシージャの使い方
CREATE PROCEDURE プロシージャ名
で作成し、CALL プロシージャ名
で実行、DROP PROCEDURE プロシージャ名
で削除といった流れ。
-- プロシージャの定義 DELIMITER // CREATE PROCEDURE sample() -- プロシージャ名 BEGIN -- プロシージャで実行したい処理 END // DELIMITER ; -- プロシージャの実行 CALL sample(); -- プロシージャの削除 -- 「sample」部分はプロシージャ名 DROP PROCEDURE sample; -- 個人的には、とりあえずIF EXISTSつけておく DROP PROCEDURE IF EXISTS sample;
DELIMITERって
SQLの区切り文字といえば「セミコロン( ; )」だが、プロシージャを書くときは厄介もの。
CREATE PROCEDURE print_now() SELECT NOW();
な感じでプロシージャを作れるが、「;」で終了しちゃうので、複数処理書けない。
そこで、DELIMITER
をつかって一時的に区切り文字を「//」に変更している。
※ 変更した区切り文字は最後にセミコロンに戻してあげる
プロシージャを書くときは、複数処理まとめたい場合が多いはずだから、「DELIMITER
で囲ってBEGIN ~ END
の間に書くもの」って覚えても良いと思う。
MySQLでのストアドプロシージャのサンプルコード
シナリオ
次の構成のcity
テーブルがあり、これと全く同じ構成の city_new
テーブルを作成して、city
テーブルから city_new
テーブルに指定件ずつデータを移行していく。
Field | Type | Key |
city_id | smallint unsigned | PRI |
city | varchar(50) | |
country_id | smallint unsigned | MUL |
last_update | timestamp |
サンプルコード
プロシージャには引数を渡すこともできる。
引数として、何件ずつINSERTするのか(step
)を指定できるようにしている。
DELIMITER // CREATE PROCEDURE insert_city_new(IN step INT) BEGIN -- 移行先のテーブルの最大IDから実行 SET @start = (SELECT CASE WHEN max(city_id) IS NULL THEN 0 ELSE max(city_id) END FROM city_new); -- 移行元のテーブルの最大IDまで SET @end = (SELECT max(city_id) FROM city); -- ループしてstep数ずつINSERT WHILE @end > @start DO START TRANSACTION; INSERT INTO city_new (city_id, city, country_id, last_update) SELECT city_id, city, country_id, last_update FROM city WHERE city_id > @start AND city_id <= @start + step; SET @insert_count = (SELECT row_count()); COMMIT; -- 進捗表示 SELECT @insert_count, @start start_city_id; SET @start = @start + step; -- スリープしたければ -- SELECT sleep(1); END WHILE; END // DELIMITER ;
実行
mysql> CALL insert_city_new(100); +---------------+---------------+ | @insert_count | start_city_id | +---------------+---------------+ | 100 | 0 | +---------------+---------------+ 1 row in set (0.01 sec) +---------------+---------------+ | @insert_count | start_city_id | +---------------+---------------+ | 100 | 100 | +---------------+---------------+ 1 row in set (0.01 sec) +---------------+---------------+ | @insert_count | start_city_id | +---------------+---------------+ | 100 | 200 | +---------------+---------------+ 1 row in set (0.02 sec) +---------------+---------------+ | @insert_count | start_city_id | +---------------+---------------+ | 100 | 300 | +---------------+---------------+ 1 row in set (0.02 sec) +---------------+---------------+ | @insert_count | start_city_id | +---------------+---------------+ | 100 | 400 | +---------------+---------------+ 1 row in set (0.02 sec) +---------------+---------------+ | @insert_count | start_city_id | +---------------+---------------+ | 100 | 500 | +---------------+---------------+ 1 row in set (0.02 sec) Query OK, 0 rows affected (0.02 sec)
プロシージャ関連のコマンドまとめ
プロシージャの作成
DELIMITER // CREATE PROCEDURE [プロシージャ名] BEGIN -- 処理 END // DELIMITER ;
作成されているプロシージャ一覧
SHOW PROCEDURE STATUS \G
↑だと色々出力されるので、表示内容を絞りたい場合は information_schema.ROUTINES
をSELECTする
SELECT ROUTINE_SCHEMA, /* ストアドプロシージャがあるデータベース */ ROUTINE_NAME, FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE';
作成されているプロシージャの内容確認
SHOW CREATE PROCEDURE [プロシージャ名];
プロシージャの削除
プロシージャが無い場合にエラーにしたくない場合はIF EXISTS
をつける。
エラーになった方が良ければなくてもOK。
DROP PROCEDURE IF EXISTS [プロシージャ名];