MySQL ストアドプログラム内での動的SQL

MySQLで動的SQLを勉強した際、かなりてこずったのでまとめておきます。

動的SQLの必要性

静的SQL(通常のSQL)ではSQL文を書いたときに呼び出す内容が確定しているが、
動的SQLを使えばクエリの内容を可変にできる。

たとえば、t1というテーブルにおいて
変数 @var_in が1のとき var_1 のカラムを参照、
変数 @var_in が2のとき var_2 のカラムを参照、
：
：
変数 @var_in がNのとき var_N のカラムを参照、
というようなプログラムを書きたいとする。

Nが3であれば静的SQLだけでも下のように書くこともできる。

静的SQL

SET @var_in := 1;
SELECT
  (CASE 
   WHEN @var_in=1 THEN var_1
   WHEN @var_in=2 THEN var_2
   WHEN @var_in=3 THEN var_3
    ELSE  null 
    END)
    FROM t1;

しかしこのNが10、100、1000と増えていった時にすべて条件分岐を書き出すのはかなり大変になり、
動的SQLを使うメリットが大きくなる。

MySQLでの動的SQL

動的SQLは定義された文字列をクエリとして認識させることができ、
クエリ自体を可変にできる。
以下が先の課題をクリアできるプログラムとなる。

動的SQL

SET @q := CONCAT("SELECT var_",@var_in,"FROM t1");
-- @var_in = 1  ->  "SELECT var_1 FROM t1"
PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

1行目：クエリとしたい文字列を定義する。
2行目：stmtをEXECUTEしたときにこの文字列をクエリとして認識するよう命令する。
3行目：実際にEXECUTEして可変なクエリを実行する。
4行目：stmtを開放する。

ストアドプログラム内での動的SQL

ただし、ストアドプログラム内では、動的SQLに用いる変数について注意が必要である。
通常、

DECLARE var_name INT;

のように定義されている変数については、

SELECT COUNT(*) INTO var_name;

のようにして、変数に値を代入したり、

INSERT INTO t1 VALUES (var_name);

のようにテーブルに挿入することが可能だが、ストアドプログラム内の動的SQLでは不可能で、
クエリ内で扱える変数は頭に@を付けたユーザー定義変数のみである。

たとえば、先のプログラムから少し変更して、
変数 @var_in がNのとき var_N のカラムの平均値を、@v_out_Nに出力するプログラムを書きたいとする。
この時は以下のように書かなくてはならない。

SET @q := CONCAT("SELECT AVG(var_",@var_in,") INTO @var_out_",@var_in," FROM t1");
-- @var_in＝1  ->  "SELECT AVG(var_1) INTO @var_out_1 FROM t1"
PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

また、このとき@var_inや@var_out_NをDECLAREで定義する必要はない。

まとめ

私はwebサービスの開発などの経験はなく、例にあったような場面に遭遇したわけではないのですが、機械学習用のデータセットを作成する際にpythonのpandasなどでいうapply関数のような動作を動的SQLで実装しました。
同じような操作を行ごとに行うのはカーソルで十分ですが、列ごとに同じ操作を繰り返すには動的SQLを使う必要がありそうです。