如何在SQL查询中使用集合类型输入变量替代IN/CASE子句固定值集合

首先你当前的写法不生效的核心原因是：你传入的:input_set是单个字符串类型的值，SQL解析时会把整个字符串当成IN子句的唯一匹配项，等价于key_1 = '1, 2, 3'，自然无法匹配到多个独立值的场景。

可以根据你使用的数据库类型，选择以下几种可行方案：

方案1：使用数据库原生支持的数组/集合参数（优先推荐） #

如果你的数据库支持数组/集合类型的参数绑定，直接用对应语法即可，性能最优且能正常使用索引：

• PostgreSQL 写法：

SELECT * FROM table_1
WHERE key_1 = ANY(:input_set)

参数绑定的时候直接传入字符串数组 ['1', '2', '3'] 即可。

• Oracle 写法：

SELECT * FROM table_1
WHERE key_1 IN (SELECT column_value FROM TABLE(:input_set))

参数绑定传入内置集合类型 SYS.ODCIVARCHAR2LIST('1','2','3') 即可。

方案2：字符串拆分转换（仅适用于不支持集合参数的场景） #

如果只能传入逗号分隔的字符串参数，可以用数据库的字符串拆分函数把单字符串拆成多行值再匹配：

• MySQL/MariaDB 可以用FIND_IN_SET函数，写法最简单：

SELECT * FROM table_1
WHERE FIND_IN_SET(key_1, :input_set) > 0

参数绑定时传入纯逗号分隔的字符串 '1,2,3'（不需要加引号、括号）即可。

• 其他数据库可以用对应拆分函数：SQL Server用STRING_SPLIT、PostgreSQL用STRING_TO_ARRAY+UNNEST、MySQL 8.0+也可以用JSON_TABLE解析JSON格式的参数。

注意：该方案因为用到了函数计算，无法命中key_1的索引，数据量大时性能会受影响。

方案3：动态生成占位符（通用安全方案） #

在业务代码层面根据传入的集合长度，动态生成对应数量的参数占位符，再分别绑定每个值。比如传入3个值时，生成的SQL为：

SELECT * FROM table_1
WHERE key_1 IN (:input1, :input2, :input3)

再分别绑定:input1='1'、:input2='2'、:input3='3'即可。

该方案兼容所有数据库，能正常使用索引，且不会有SQL注入风险，是没有数组参数支持时的最优选择。

内容的提问来源于stack exchange，提问作者Nani_Momo