我的解决方案是:

create or replace type split_tbl as table of varchar(32767);
/

create or replace function split
(
  p_list varchar2,
  p_del varchar2 := ','
) return split_tbl pipelined
is
  l_idx    pls_integer;
  l_list    varchar2(32767) := p_list;
  l_value    varchar2(32767);
begin
  loop
    l_idx := instr(l_list,p_del);
    if l_idx > 0 then
      pipe row(substr(l_list,1,l_idx-1));
      l_list := substr(l_list,l_idx+length(p_del));
    else
      pipe row(l_list);
      exit;
    end if;
  end loop;
  return;
end split;
/

现在你可以使用一个变量来获取表中的一些值:

select * from table(split('one,two,three'))
  one
  two
  three

select * from TABLE1 where COL1 in (select * from table(split('value1,value2')))
  value1 AAA
  value2 BBB

因此，预处理语句可以是:

  "select * from TABLE where COL in (select * from table(split(?)))"

问候,

哈维尔·伊瓦内斯

我只是为此制定了一个特定于postgresql的选项。它有点像黑客，有自己的优缺点和局限性，但它似乎是有效的，而且不局限于特定的开发语言、平台或PG驱动程序。

当然，诀窍是找到一种方法，将任意长度的值集合作为单个参数传递，并让db将其识别为多个值。我的解决方案是从集合中的值构造一个带分隔符的字符串，将该字符串作为单个参数传递，并使用string_to_array()与PostgreSQL正确使用它所需的强制转换。

因此，如果你想搜索“foo”，“blah”和“abc”，你可以将它们连接成一个字符串，如:'foo,blah,abc'。下面是直接的SQL语句:

select column from table
where search_column = any (string_to_array('foo,blah,abc', ',')::text[]);

显然，您可以将显式强制转换更改为您想要的结果值数组——int、text、uuid等。因为函数接受一个字符串值(或者两个，如果你也想自定义分隔符)，你可以在准备好的语句中作为参数传递它:

select column from table
where search_column = any (string_to_array($1, ',')::text[]);

这甚至足够灵活，可以支持like比较:

select column from table
where search_column like any (string_to_array('foo%,blah%,abc%', ',')::text[]);

Again, no question it's a hack, but it works and allows you to still use pre-compiled prepared statements that take *ahem* discrete parameters, with the accompanying security and (maybe) performance benefits. Is it advisable and actually performant? Naturally, it depends, as you've got string parsing and possibly casting going on before your query even runs. If you're expecting to send three, five, a few dozen values, sure, it's probably fine. A few thousand? Yeah, maybe not so much. YMMV, limitations and exclusions apply, no warranty express or implied.

但它确实有效。

没有简单的办法。 如果目标是保持较高的语句缓存比(即不是每个参数都创建一条语句)，您可以执行以下操作:

创建带有几个参数(例如10个)的语句: ．.． 一个在 (?,?,?,?,?,?,?,?,?,?) ... 绑定所有实际参数 setString(1、“foo”); setString(2,“酒吧”); 其余的绑定为NULL Types.VARCHAR setNull (3) .．. Types.VARCHAR setNull (10)

NULL从不匹配任何东西，因此它会被SQL计划构建器优化。

当你将List传递给DAO函数时，逻辑很容易自动化:

while( i < param.size() ) {
  ps.setString(i+1,param.get(i));
  i++;
}

while( i < MAX_PARAMS ) {
  ps.setNull(i+1,Types.VARCHAR);
  i++;
}

PostgreSQL的解决方案:

final PreparedStatement statement = connection.prepareStatement(
        "SELECT my_column FROM my_table where search_column = ANY (?)"
);
final String[] values = getValues();
statement.setArray(1, connection.createArrayOf("text", values));

try (ResultSet rs = statement.executeQuery()) {
    while(rs.next()) {
        // do some...
    }
}

or

final PreparedStatement statement = connection.prepareStatement(
        "SELECT my_column FROM my_table " + 
        "where search_column IN (SELECT * FROM unnest(?))"
);
final String[] values = getValues();
statement.setArray(1, connection.createArrayOf("text", values));

try (ResultSet rs = statement.executeQuery()) {
    while(rs.next()) {
        // do some...
    }
}

我遇到了一些与准备好的语句相关的限制:

The prepared statements are cached only inside the same session (Postgres), so it will really work only with connection pooling A lot of different prepared statements as proposed by @BalusC may cause the cache to overfill and previously cached statements will be dropped The query has to be optimized and use indices. Sounds obvious, however e.g. the ANY(ARRAY...) statement proposed by @Boris in one of the top answers cannot use indices and query will be slow despite caching The prepared statement caches the query plan as well and the actual values of any parameters specified in the statement are unavailable.

在建议的解决方案中，我会选择一个不会降低查询性能并且查询次数较少的解决方案。这将是#4(批处理少数查询)从@Don链接或指定NULL值为不需要的'?@Vladimir Dyuzhev提出的标记

使用嵌套查询是一种令人不快的变通方法，但肯定是可行的。创建一个包含列的临时表MYVALUES。将值列表插入到MYVALUES表中。然后执行

select my_column from my_table where search_column in ( SELECT value FROM MYVALUES )

很丑，但如果你的价值列表非常大，这是一个可行的选择。

如果您的数据库没有缓存准备好的语句，这种技术还有一个额外的好处，那就是可能会从优化器获得更好的查询计划(检查一个页面是否有多个值，表只能检查一次，而不是每个值检查一次，等等)，这样可以节省开销。您的“insert”将需要批处理，并且可能需要调整MYVALUES表以使锁定或其他高开销保护最小化。