MySQL のストアドプロシージャで動的なテーブル名を指定する
MySQL のストアドプロシージャを作っていて、テーブル名を引数で取って動的に SQL を組み、結果を返したいという事がやりたかった。
イメージはこんな感じ。
DELIMITER \\ DROP PROCEDURE IF EXISTS `foo`\\ CREATE PROCEDURE `foo`( IN `tbl_bar` INT(10), IN `colmun_baz` VARCHAR(1000), IN `value_hoge` VARCHAR(1000), OUT `_result` INT(10) ) BEGIN SELECT COUNT(*) INTO _result FROM tbl_bar WHERE colmun_baz = value_hoge; END \\ DELIMITER ;
最初はストアドファンクションとしてやろうとしたけど、コンパイル時にエラーになったので、ストアドプロシージャで、引数に戻すように設定。
ERROR 1336 (0A000) at line 1: Dynamic SQL is not allowed in stored function or trigger
で、当然ながら実行時エラーが発生した。
動的な SQL を組み立てるにはプリペアドステートメントを使わないとダメみたい。
DELIMITER \\ DROP PROCEDURE IF EXISTS `foo`\\ CREATE PROCEDURE `foo`( IN `tbl_bar` INT(10), IN `colmun_baz` VARCHAR(1000), IN `value_hoge` VARCHAR(1000), OUT `_result` INT(10) ) BEGIN SET @s = 'SELECT COUNT(*) INTO @cnt FROM ? WHERE ? = ?'; PREPARE stmt from @s; SET @tbl = tbl_bar; SET @clm = colmun_baz; SET @val = value_hoge; EXECUTE stmt USING @tbl, @clm, @val; DEALLOCATE PREPARE stmt; SELECT @cnt INTO `_result`; END \\ DELIMITER ;
こんな感じで使えばいけると思ったが実行時エラーが発生。
どうやらMySQL では動的にテーブル名を指定できないっぽい…と思い、ググったら以下がヒット。
MySQL :: Re: Can I use a dynamic table name in stored procedure --> SOLVED
CONCAT() で文字列として連結すれば出来るよう。
参考にしつつ書いたのがこれ。
DELIMITER \\ DROP PROCEDURE IF EXISTS `foo`\\ CREATE PROCEDURE `foo`( IN `tbl_bar` INT(10), IN `colmun_baz` VARCHAR(1000), IN `value_hoge` VARCHAR(1000), OUT `_result` INT(10) ) BEGIN SET @s = CONCAT('SELECT COUNT(*) INTO @cnt FROM `', tbl_bar, '` WHERE `', colmun_baz, '` = ?'); PREPARE stmt from @s; SET @val = value_hoge; EXECUTE stmt USING @val; DEALLOCATE PREPARE stmt; SELECT @cnt INTO `_result`; END \\ DELIMITER ;
意図通りとれたが、本当にこのやり方で良いのかが分からない。
そして MySQL のストアドプロシージャの情報が少ない…。