Help us understand the problem. What is going on with this article?

プリペアドステートメント(パラメータクエリ)で In 句を使いたい

プリペアステートメント で 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||',%'
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした