MySQL(InnnoDB)でfind_in_setをインデックスの張れる記述方法に変えたい

###前提・実現したいこと

MySQL(InnnoDB)でfind_in_setを使っていますが、パフォーマンスが出ません。
find_in_setではインデックスが張れないので、find_in_setと同じ条件指定が出来て
インデックスが張れる記述方法などを探しています。
インデックスの張り方も含めてアイデアをいただけるとありがたいです。

###現状のSQL例

select id
from mytable
where
find_in_set(model, @models)

modelは文字列です。
@modelsは毎回異なる値が設定されるので、set @models = 'A, B, C, D, E'といった指定を
SQLの前段で記述するようにSQL生成プログラムを作成してあります。

行動規範の内容に同意します

回答2件

ベストアンサー

find_in_setなんて関数を使わずに、素直にINで書きましょう。

SQL
1select id 
2from mytable 
3where 
4     model IN ('a', 'b', 'c', 'd')

なお、可変個の文字列を正しくエスケープ/プリペアするのは少々面倒かもしれないので、そこにはご注意ください。

投稿2016/05/25 01:14

maisumakun

総合スコア145183

cnx

2016/05/25 01:42

早速のアドバイスありがとうございます。 model IN ('a', 'b', 'c', 'd') こちらの「'a', 'b', 'c', 'd'」を1つの変数として扱う表記は可能でしょうか。または1つの変数から展開して'a', 'b', 'c', 'd'に戻す方法がありますでしょうか。（もしも無ければコメント頂かなくても大丈夫です。その際は、SQLの吐き出しの際に直接Where句内に値を埋め込む仕様に変更して試してみます。）

KiyoshiMotoki

2016/05/25 04:39

横から失礼します。 > こちらの「'a', 'b', 'c', 'd'」を1つの変数として扱う表記は可能でしょうか。一応、可能ではあります。 MySQLのドキュメントに、以下のように説明があります。 https://dev.mysql.com/doc/refman/5.6/ja/user-variables.html > ユーザー変数は、データ値を提供するためのものです。これらは、テーブル名やデータベース名が想定されるコンテキストなどでの識別子または識別子の一部として、または SELECT などの予約語として、SQL ステートメントの中で直接使用することはできません。 > （中略） > 識別子を提供するためにユーザー変数を使用できないというこの原則の例外が、あとから実行する準備されたステートメントとして使用するために文字列を構築している場合です。例えば、以下のような感じです。　SET @s = CONCAT('select id from mytable where model in (', @models, ')'); 　PREPARE stmt FROM @s; 　EXECUTE stmt; もっとも、この方法はお勧めできません。 SQLインジェクションの原因となる可能性があるからです。 http://nippondanji.blogspot.jp/2010/01/sql.html SQLインジェクションの可能性、という点では、 > SQLの吐き出しの際に直接Where句内に値を埋め込む仕様も、同様です。お使いの環境が不明なので具体的な提案はできませんが、バインド変数を使用する方式に変更した上で、SQLを呼び出すアプリケーション側で、 IN句に指定する値の数だけパラメータマーカー("?"のこと）の数を動的に増やすよう、実装してやるのが良いと思います。 https://dev.mysql.com/doc/refman/5.6/ja/prepare.html 例えば、　String sql = 'select id from mytable where model in (%s)'; という文字列を用意しておき、"%s"部分を指定したい値の数だけ ?, ?, ?, ... と変化させるイメージです。

cnx

2016/05/25 05:52

丁寧な解説ありがとうございました。環境のご説明をさせて頂きますと、 Windows環境のバッチファイルで条件を入力 ↓ 条件に応じたSQLファイルをバッチ内で生成 ↓ WinSCP経由（証明書のみ受け入れる接続設定）でリモートのCentOSに接続（接続元IP制限も実施、2段階でその先の別のCentOSインスタンスに接続） ↓ そこでWinSCPスクリプト機能でMySQLに接続、生成されたSQLをアップロード＆実行 ↓ 結果をダウンロードして終了という流れになっております。 Webを介してPHPなどが動き、SQLを実行するような環境とは異なっております。頂いた情報を参考に修正と確認をしてみます。

行動規範の内容に同意します