是否有数组的 SQL 参数绑定? [英] Is there SQL parameter binding for arrays?

查看:15
本文介绍了是否有数组的 SQL 参数绑定?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

在 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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆