プリペアドステートメント（パラメータクエリ）で In 句を使いたい #MySQL

プリペアステートメントで IN 句の内容が可変である場合、それでもやっぱりプレイスホルダは一つにしたいものです。

PostgreSQL

PostgreSQL なら string_to_array() か regexp_split_to_table() を使う以下の方法が簡単です。

string_to_array().before

SELECT * FROM theTable
WHERE theColumn = ANY(string_to_array('foo,bar', ','))

↓

string_to_array().after

SELECT * FROM theTable
WHERE theColumn = ANY(string_to_array($1, ','))

または

regexp_split_to_table().before

SELECT * FROM theTable
WHERE theColumn IN 
    (SELECT foo FROM regexp_split_to_table('foo,bar', E',') AS foo)

↓

regexp_split_to_table().after

SELECT * FROM theTable
WHERE theColumn IN 
    (SELECT foo FROM regexp_split_to_table($1, E',') AS foo)

Oracle

Oracle でも同じようなことができます。
http://blog.tanelpoder.com/2007/06/20/my-version-of-sql-string-to-table-tokenizer/

forOracle

SELECT * FROM theTable
WHERE theColumn IN 
    (
    SELECT
        REGEXP_REPLACE(
            REGEXP_SUBSTR( :mystr||:myseparator, '(.*?)'||:myseparator, 1, LEVEL )
            , :myseparator||'$'
            , ''
        ) TOKEN
    FROM
        DUAL
    CONNECT BY
        REGEXP_INSTR( :mystr||:myseparator, '(.*?)'||:myseparator, 1, LEVEL ) > 0
    )

※2016/05/23 追記。↓プレイスホルダを少なくしました。

oracle

WITH KEYWORD_SOURCE (MYKEYWORDS, MYSEP) AS (
    SELECT :mykeywords, :myseparator FROM DUAL
),   KEYWORDS_LIST (TOKEN) AS (
    SELECT
        REGEXP_REPLACE(
            REGEXP_SUBSTR( kws.MYKEYWORDS||kws.MYSEP, '(.*?)'||kws.MYSEP, 1, LEVEL )
            , kws.MYSEP||'$'
            , ''
        ) TOKEN
    FROM
        DUAL, KEYWORD_SOURCE  kws
    CONNECT BY
        REGEXP_INSTR( kws.MYKEYWORDS||kws.MYSEP, '(.*?)'||kws.MYSEP, 1, LEVEL ) > 0
)
SELECT * FROM theTable
WHERE theColumn IN (SELECT TOKEN FROM KEYWORDS_LIST)

SQLServer

SQLServer だと普通に Split が使えるんでしょうか。環境がないので試せていませんが。
http://stackoverflow.com/questions/5511097/query-for-matching-comma-seperated-values-from-two-tables
一旦XMLに変換する方法も...。
http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1333906103

※2020/05/12 追記：STRING_SPLIT を使うようです。下記参照。
SQLSeverのIN句でバインド変数を使う - Qiita

MySQL

MySQL は ngyuki 様がコメント欄にて FIND_IN_SET を使う方法を紹介してくださいました。詳しくはコメント欄を参照ください。

あるいは数が決まっているならこんな荒業も...？

MySQL


SELECT *
FROM TheTable
WHERE TheColumn IN (
        SELECT DISTINCT
            SUBSTRING_INDEX(SUBSTRING_INDEX(Param.v, ',', Split.pos), ',', -1) AS user_id
        FROM (
                SELECT   '123,456' AS v, 1 AS dmy -- <--パラメータ
            ) Param
            JOIN (
                SELECT 1 AS dmy, 1 AS pos
                UNION SELECT 1 AS dmy, 2 AS pos
                UNION SELECT 1 AS dmy, 3 AS pos
                UNION SELECT 1 AS dmy, 4 AS pos
                UNION SELECT 1 AS dmy, 5 AS pos

                -- ...省略

            ) Split ON Split.dmy = Param.dmy
    )

MySQL 8.0 以降なら再起CTEが使えてもっとスマートに実現できるはずですが、環境が無いので未着手です。

その他の方法

可変部分を文字列にして LIKE 句で比較するという以下のような方法もありますが、
はたして有用かどうかはケースバイケースだと思います。

実行速度は倍以上になります。
・実行回数が少なく、...
・アプリの性能劣化が許容できるレベルで、...
・コードのメンテナンスが格段に楽になるなら ...
...使った方が良いでしょう。

before-1

SELECT * FROM theTable
WHERE theColumn IN ('foo', 'bar')

↓

before-2

SELECT * FROM theTable
WHERE ',foo,bar,' LIKE '%,'||theColumn||',%'

↓

after

SELECT * FROM theTable
WHERE ? LIKE '%,'||theColumn||',%'