Perl DBI 和占位符

Question

我有以下查询语句：select * from table where ID in (1,2,3,5...)

如何使用DBI和占位符构建这个查询语句？

例如：

my @list = (1, 2, 3, 4, 5);
my $sql = "select * from table where ID in (?)";

$sth->prepare($sql);
$sth->execute();

我应该发送什么参数执行？它是由,分隔的列表还是字符串，还是其他东西？

- smith

Answer 1

这将根据您的数组中的项目数量动态构建查询。

my @list =(1,2,3,4,5);
my $sql ="select * from table where ID in (@{[join',', ('?') x @list]})";

- ccheneson

Answer 2

以那种方式是不可能的。你需要为数组中的每个项指定一个占位符:

my @list = (1,2,3,4,5);
my $sql = "select * from table where ID in (?,?,?,?,?)";

$sth->prepare($sql);
$sth->execute(@list);

如果你的@list不是固定大小的，你需要使用正确数量的占位符构建$sql。

- Miguel Prz

Answer 3

引用 DBI 文档：

Also, placeholders can only represent single scalar values. For example, the following statement won't work as expected for more than one value:

     SELECT name, age FROM people WHERE name IN (?)    # wrong
     SELECT name, age FROM people WHERE name IN (?,?)  # two names

Rewrite to:

my $sql = 'select * from table where ID in ( ?, ?, ?, ?, ? )';
$sth->prepare($sql);
$sth->execute(@list);

- Alan Haggai Alavi

Answer 4

如果你正在使用DBI来访问PostgreSQL数据库，使用DBD::Pg驱动程序，可以使用以下代码：

my @list = (1, 2, 3, 4, 5);
my $sql = "select * from table where ID = ANY(?::INT[]);";

$sth->prepare ($sql);
$sth->execute (\@list);

- Tim Landscheidt

---

我认为你甚至不需要 ::INT[]。只要 id = ANY(?) 对我来说就可以了。 - Smylers

Answer 5

除非你知道元素的确切数量，否则你不能使用占位符。尝试这个：

my @list = (1, 2, 3, 4, 5);  # any number of elements
my $in = join(',', map { $dbh->quote($_) } @list);
my $sql = "select * from table where someid IN ($in)";

- pizzamonster

---

正如您可以从其他答案中看到的那样，使用占位符是可行的，因为即使来自动态源，也很容易找出数组的大小... - user3112922

如果您准备一次性编写语句，然后使用完全相同数量的参数多次执行它，则使用列表占位符是有意义的；在这种情况下，它只会增加开销并增加超过最大查询大小的可能性。 - pizzamonster

Answer 6

如果你切换到DBIx::Simple，你只需要这样说：

$db->query('INSERT INTO foo VALUES (??)', $foo, $bar, $baz);

?? 的意思是“需要多少就用多少”。

编辑：

实际上，我有点过于乐观了：“如果查询中存在字符串 (??)，它将被替换为与 @values 一样多的问号列表。”

所以这似乎行不通：

$db->query( "SELECT * FROM foo WHERE id IN (??) AND stuff=?", @ids, $stuff )

虽然有点老，但仍然有用。

对于好奇的人，模块中的代码如下：

# Replace (??) with (?, ?, ?, ...)
sub _replace_omniholder {
  my ($self, $query, $binds) = @_;
  return if $$query !~ /\(\?\?\)/;
  my $omniholders = 0;
  my $q = $self->{dbd} =~ /mysql/ ? $quoted_mysql : $quoted;
  $$query =~ s[($q|\(\?\?\))] {
    $1 eq '(??)'
    ? do {
        Carp::croak('There can be only one omniholder')
            if $omniholders++;
        '(' . join(', ', ('?') x @$binds) . ')'
    }
    : $1
  }eg;
}

- Øyvind Skaar

Answer 7

我找到了一种确保这个方法能够总结以上所有建议的方法。我的生产查询（我在这里发布了一个简化版本）使用IN <>，其中代码和它们的数量都是未知的。它可以是单个代码（例如FIN），也可以是一系列代码（FITLC FITLD FITLU FITSC FITSD FITSU MDYLC MDYLD MDYLU）。某些函数将其作为列表返回。

使这一切发生的代码是

            @codes =  get_muni_evcode( $category );
            my $in = join( ', ', ('?') x @codes );
            print "\n\nProcessing Category: $category --> Codes: @codes   .. in: $in\n";

            my $sql = "select distinct cusip9 
            from material_event 
            where event_date between (trunc(sysdate) - 1) + 2/3 and trunc(sysdate) + 2/3 
            and event_code in ($in)";
            my $sth2 = $dbh->prepare($sql);
            $sth2->execute( @codes );

            while (my $s2 = $sth2->fetchrow_hashref('NAME_lc'))
            {
                    my $cusip9 = $s2->{cusip9};
                    print "$cusip9\t";
                   .................. further processing ..............

            }

结果样例：

处理类别：RatingChange --> 代码：FITLC FITLD FITLU FITSC FITSD FITSU MDYLC MDYLD MDYLU MDYSC MDYSD MDYSU SPLD SPLPR SPLU SPSD SPSPR SPSU .. 在: ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? 359496HQ2 359496GB6 359496GH3 359496GL4 359496HU3 359496HS8 359496HA7 359496HF6 359496GM2 359496HM1 359496HR0 359496HT6 359496GY6 359496GJ9 359496HL3 359496GU4 359496HK5 359496HN9 359496HP4 359496GW0 359496GZ3 359496HC3 359496GC4 359496GK6 359496GP5 359496GV2 359496GX8 359496GN0

我非常感激在这里发布他们的想法的所有人，最终让我找到了正确的方法来解决这个问题。我认为这应该是一个相当普遍的问题。

- Leon Alekseyev