サービス・リニューアルの際のデータ移行とか、運用・保守フェーズなんかで、コマンドラインからデータベースの中身を調べてたりする時、しばしばSELECT句やWHERE句のカラムやテーブル指定を動的に行いたい！…というシーンに出くわす。
アプリケーション側の開発であれば、使用しているプログラム言語のメソッドを使ったり、OSのシェルコマンド使ったりしてSQLを動的に生成すれば対応するのは造作もないんだが、恒久的には発生しない一時的なデータ調査作業でそんな処理を書くのは非効率的で面倒でもある。
もうコンソールでDBにログインしてしまっているし…とかいうケースならなおさら、もうそのままSQLコマンドで完結してしまいたいものだろう。
そんな時に有効ななのが動的SQLだ。まぁ、ストアドプロシージャを使うという手もあるんだが、一回こっきり的な作業をそこまで大事（おおごと）にしたくもないっちゅーケースはままあるだろう。ただし、将来的に同じようなデータ抽出作業が発生する見込みがあるのであれば、逆にストアド使って関数を保存しておいた方がデータベースの保守性が向上するので、そこはケースバイケースだ。
さて、そんなわけで、MySQLの動的SQL利用例を紹介してみたい。
クエスト: 連番化したカラム名を大量に有するテーブルから、特定行の連番化カラムの値だけを検索せよ
──と、RPG的なお題目を出してみた（苦笑）
対象のテーブル構造は、こんな感じだ。
CREATE TABLE `user_meta` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_id` varchar(32) NOT NULL,
`activated` tinyint(1) NOT NULL DEFAULT '0',
`created_at` datetime NOT NULL,
`modified_at` datetime NOT NULL,
`ext1` text,
`ext2` text,
:
`ext128` text,
PRIMARY KEY (`id`),
KEY `FK_user_master` (`user_id`),
CONSTRAINT `FK_user_master` FOREIGN KEY (`user_id`) REFERENCES `user_master` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED
多目的カラムがext1からext128まで、128個あるテーブルである。
一昔前のCRMなどのユーザー管理テーブルとかにありがちな、各クライアント別に拡張項目を自由に設定できるように、拡張用カラムを大量に用意してあるテーブル構造だと思ってもらいたい。
今時なテーブル設計であれば、拡張項目はキー・バリュー型のオプション・テーブルとしてリレーションさせるのが一般的だと思うが、こういうレガシーなテーブル構造を持っているWEBサービスは割と多い。
お題としては、このテーブルから拡張用の番号付きカラムだけのデータを取得したい──つまりは、このテーブルから「ext*」のカラム値だけを取得したいということだ。
普通に考えると、このお題をクリアするには、下記のようなSELECT文が必要になる。
SELECT ext1,ext2,ext3,ext4,……,ext128 FROM user_meta WHERE user_id = '0001';
ユーザーIDが0001の行から、拡張項目である ext1～ext128 のカラムの値だけを取得するというクエリだ。このSQL、コマンドラインから128個のカラム名をSELECT句に記述して発行するというのはあまりにも馬鹿らしい。時間の無駄だし、オペミスも起こりやすい、およそ現実的な運用手段ではない。
かと言って、上記のSELECT文を生成するのにデータベースのコマンドラインを抜けて、バッチやシェルを作ったりするのも手間だし、面倒である。
もしOracleのPL/SQLとかだと、SQLでFORループが使えるので、こういう連続する数値関連のSQLを動的に生成できるのだが、MySQLなどのオープンソース系DBMSにはそういう高度なSQL関数は準備されていない。
まぁ、本来こういう高度な処理はSQLではなく、アプリケーション側で実装されるべきものだろうから、PL/SQLの方がリッチすぎるのだろうとは思うが…。
では、どうしたものか？
そう、こういう時こそ、動的SQLを使って、SELECT句のext1～ext128までの指定を動的に記述してしまうのだ。やり方は下記のようになる。
SET group_concat_max_len = 65535;
SET @cols = (SELECT GROUP_CONCAT(column_name) FROM information_schema.columns WHERE table_schema = 'test' AND table_name = 'user_meta' AND column_name REGEXP '^ext[0-9]{0,3}$');
SET @query = CONCAT("SELECT ", @cols, " FROM user_meta WHERE user_id = '0001'");
PREPARE getExtends FROM @query;
EXECUTE getExtends;
DEALLOCATE PREPARE getExtends;
まず、検索対象となるテーブルのカラム名をinformation_schemaデータベースのcolumnsテーブルから正規表現を使って取得して、GROUP_CONCAT()を使ってカンマ区切りで連結した文字列を変数@colsに格納する。この時、カラム名が長かったりして連結文字列数がMySQLの既定最大値を超えてしまうとエラーになるので、事前にgroup_concat_max_lenを変更して連結文字列の最大値を拡張しておくと安全だ。
次に、変数@colsに格納されているカラム群を対象にした最終的な検索クエリを文字列連結で作成して変数@queryとして定義する。
PREPAREは準備済みステートメントを定義する構文で、PREPARE {ステートメント名} FROM {SQLクエリ}のように定義しておくと、いつでもEXECUTE {ステートメント名}で登録されているクエリが実行できるようになる。疑似的に簡素なストアドプロシージャもしくはストアドファンクション的な動的クエリが実行できるのだ。なお、ステートメント名は大文字小文字を判別しない。
そして、クエリを実行するのがEXECUTEだ。実行すると、下記のように「ext1～ext128」のみのカラム値のみが取得できる。
| ext1 | ext2 | ext3 | ext4 | ext5 | ext6 | … | ext127 | ext128 |
+------+------+------+-------+------+------+---+--------+--------+
| {"gender":"male"} | {"birthday":"1999-04-01"} | {"zipcode":"123-4567"} | {"address":""} | {"email":"sample@sample.com"} | {"telephone":"012-345-6789"} | … | NULL | NULL |
PREPAREで準備済みにしたクエリは、EXECUTEでいつでも何度でも実行可能となる。しかし、MySQLのコンソールからログアウトすると登録した変数も準備したクエリも削除されてしまうので、次回のログイン時にも使いたい場合などは、ストアドプロシージャとして保存しておく必要がある。
最後にDEALLOCATE PREPAREは、これは準備済みにしたクエリを破棄する構文だ。これを実行すると、再度PREPAREしない限りEXECUTEできなくなる。ただし、SETで登録した変数はログアウトするまで使えるので、SELECT @cols;等で利用することはできる。
以上、簡易的なストアドプロシージャとも云えるこの動的SQLのやり方を知っていると、データベースを保守する時に色々と高度な検索が手早くできるようになるのだ。
基本的に、動的SQLについては圧倒的にパフォーマンスが見込めないので、これをアプリケーション側から操作するようなシステム設計はナンセンスだが、障害時のデータベースのデータ調査等、保守シーンでは強力な武器になると思う。