是否有数组的 SQL 参数绑定? [英] Is there SQL parameter binding for arrays?
问题描述
在 SQL 查询中是否有绑定数组(标量)的标准方法?我想绑定到 IN
子句中,如下所示:
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->quote 而不是占位符.
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::Class 或 Rose::DB::Object.
2) Use an ORM to do this sort of low level stuff for you. DBIx::Class or Rose::DB::Object, for example.
这篇关于是否有数组的 SQL 参数绑定?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!