MYSQLのストアドでの動的SQLについて

質問

MYSQL初心者ですが、よろしくお願いします。

MYSQL(ver5.5)でストアドプロシージャを作成しようと思います。
WEBの検索画面に入力された条件に応じて、WHERE句を動的に作成したいのです。

例えば、WEBの検索画面の検索条件に、
(1)氏名
(2)住所
(3)電話番号
があり、(1)と(3)に入力された場合は、
あるテーブルの検索条件を
WHERE　氏名=入力氏名　AND　電話番号=入力電話番号
のように動的にSQLを作成したいです。
（条件に応じて、静的なSQLを使い分けるやりかたはしたくない）
このような動的なSQLはストアドプロシージャで作成できのでしょうか？
よろしくお願いします。

回答No.2

mpro-gram · ベストアンサー

ストアドプロシージャ内で、テーブル名やカラム名など識別子を動的に変更することはできません。
create 時に、構文チェックが行われ、テーブル名やカラム名が確定していないとエラーになります。変数を使えるのは、識別子以外のデータ値のみ。
静的select文で、あるデータがnull や空文字列ならそのカラムは条件に使わないようにするなら、where句は次の様に書くとよい

where (case when :x is null or :x ='' then 1 else `a` <=> :x end)
and (case when null <=> :y or :y ='' then 1 else `b` <=> :y end)
and (case when isnull(:z) or :z ='' then 1 else `c` like concat('%', :z, '%') end)

※ null 判定法は、上記の様にMySQLでは３通りの式が使える。
a と b は完全一致、cカラムは、like検索用
ストアドにするまでもないとおもうけど、プリペアード文にして、:x :y :z を代入してやればよい

お礼

回答ありがとうございます。
大変参考になりました。
識別子は動的に変更できないのですね。
よくわかりました。
初心者でプリペアード文がなにものかよくわからなかったので、
調べてみますね。有難うございました。

回答No.3

yambejp

#2さんのところでロジックはでていますが、
仕様がきめきれていないところがあるので確認すると
氏名、住所、電話番号のどれも入力されていない場合は結果は
全部なのか、ヒットさせないのかがわかりません。
かりに後者だとして、ストアドを使う前提で、サンプルです。

//準備
CREATE TABLE HOGE (ID INT NOT NULL PRIMARY KEY,SIMEI VARCHAR(30),JUSHO VARCHAR(200),DENWA VARCHAR(20));
INSERT INTO HOGE VALUES(1,'sato','tokyo aaa','03-aaa'),
(2,'suzuki','tokyo bbb','03-bbb'),
(3,'takahashi','osaka ccc','06-ccc'),
(4,'tanaka','osaka ddd','06-ddd'),
(5,'ito','aichi eee','052-eee');

//プロシージャ作成
DROP PROCEDURE IF EXISTS OUTPUT_PROCEDURE;
DELIMITER //
CREATE PROCEDURE OUTPUT_PROCEDURE(
IN name VARCHAR(30),
IN address VARCHAR(200),
IN tel VARCHAR(20)
)
BEGIN
SELECT * FROM HOGE WHERE 1
AND (`SIMEI`=name OR name='')
AND (`JUSHO` LIKE CONCAT('%',address,'%') OR address='')
AND (`TEL`=tel OR tel='')
AND NOT (name='' AND address='' AND tel='');
END
//
DELIMITER ;

//コール
CALL OUTPUT_PROCEDURE('','',''); →戻りなし
CALL OUTPUT_PROCEDURE('a','','');→戻りなし
CALL OUTPUT_PROCEDURE('sato','','');→satoさんのデータ
CALL OUTPUT_PROCEDURE('','osaka','');→takahashi,tanakaさんのデータ

お礼

回答有難うございます。
すっきりしました。
親切に解説してもらえて、びっくりしています。
とても勉強になります。
また質問したいと思いますので、よろしくお願いします。

回答No.1

yambejp

where句を大きなくくりで考えればできるのでは
特にand検索なら

where 1
and 条件1
and 条件2
and 条件3 ・・・・

でつないでいけます。

もとのクエリにはwhere 1までを記しておき
あとはプログラムでつないでいけばよいでしょう

お礼

回答有難うございます。
参考にさせて頂きます。
今回は、プログラムではなくストアドプロシージャ内で、
動的にSQLを作成したいのです。
引数(1)(2)(3)をそれぞれブランクかどうかチェックして、
ブランクでなければ、yambejpさんの言われるように、
and条件をつなげていくイメージです。
この認識でOKでしょうか？