是否存在SQL参数数组绑定？ [英] Is there SQL parameter binding for arrays?

问题描述

有没有在SQL查询绑定（标量的）阵列的标准方式？我要绑定到在子句，像这样：

Is there a standard way to bind arrays (of scalars) in a SQL query? I want to bind into an IN clause, like so:

SELECT * FROM junk WHERE junk.id IN (?);

我碰巧使用到的Perl :: DBI 这要挟参数标量，所以我结束了无用的查询，如：

I happen to be using Perl::DBI which coerces parameters to scalars, so I end up with useless queries like:

SELECT * FROM junk WHERE junk.id IN ('ARRAY(0xdeadbeef)');

澄清：我把自己的的.sql 文件查询，所以已经形成的字符串。凡提答案动态创建的查询字符串我可能会做一个搜索，而是进行更换。

Clarification: I put the query in its own .sql file, so the string is already formed. Where the answers mention creating the query string dynamically I'd probably do a search and replace instead.

编辑：这个问题是怎么样的一个重复的一个参数化SQL IN子句？。我本来以为，这应该关闭正因为如此，但似乎它积累了一些很好的具体Perl的信息。

This question is kind of a duplicate of Parameterizing a SQL IN clause?. I originally thought that it should be closed as such, but it seems like it's accumulating some good Perl-specific info.

推荐答案

您指定这是有一个参数的查询的SQL - 当你想多参数将无法工作。它的处理，当然是一种痛苦。另外两个变化，以什么建议已经：

You specify "this is the SQL for a query with one parameter" -- that won't work when you want many parameters. It's a pain to deal with, of course. Two other variations to what was suggested already:

1）使用DBI->报价，而不是占位符。

1) Use DBI->quote instead of place holders.

my $sql = "select foo from bar where baz in ("
           . join(",", map { $dbh->quote($_) } @bazs)
           . ")";
my $data = $dbh->selectall_arrayref($sql);

2）使用ORM做这种低层次的东西给你。 DBIx ::类或玫瑰:: DB ::对象，例如。

2) Use an ORM to do this sort of low level stuff for you. DBIx::Class or Rose::DB::Object, for example.

这篇关于是否存在SQL参数数组绑定？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！