如何使用DBI从数据库中获取单个计数值？

The following code seems to be just too much, for getting a single count value. Is there a better, recommended way to fetch a single COUNT value using plain DBI?

以下代码似乎太多了，因为获得单个计数值。有没有更好的，推荐的方法来使用普通DBI获取单个COUNT值？

sub get_count {
   my $sth = $dbh->prepare("SELECT COUNT(*) FROM table WHERE...");
   $sth->execute( @params );
   my $($count) = $sth->fetchrow_array;
   $sth->finish;

   return $count;
}

This is shorter, but I still have two statements.

这个更短，但我还有两个陈述。

sub get_count_2 {
   my $ar = $dbh->selectall_arrayref("SELECT ...", undef, @params)
   return $ar->[0][0];
}

3 个解决方案

#1

Easy enough to do in one line with no extra variables:

很容易在一行中完成，没有额外的变量：

$count = $dbh->selectrow_array('SELECT count(*) FROM table WHERE...', undef, @params);

#2

I don't know Perl, but if it's syntax is logical I would think this would work based on your 2nd example:

我不知道Perl，但如果它的语法是合乎逻辑的，我会认为这可以基于你的第二个例子：

sub get_count {
   return $dbh->selectall_arrayref("SELECT ...", undef, @params)->[0][0];
}

#3

I probably wouldn't do this myself, but you could always make it a new top-level function of the DBH object you're using:

我可能不会自己这样做，但你总是可以使它成为你正在使用的DBH对象的一个新的顶层函数：

WARNING: untested code follows!

警告：未经测试的代码如下！

sub DBD::SQLite::db::count
{
   my($dbh, $table, $where) = @_;

   my($stmt) = "SELECT COUNT(*) FROM $table";
   $stmt .= " WHERE $where" if $where;

   my($count) = $dbh->selectrow_array($stmt);

   return $count;

}

and then call it like this:

然后像这样调用它：

my($cnt) = $dbh->count('Employee', 'year_hired < 2000');

Besides polluting a namespace that's not yours, you'd also have to write this for every DB driver you use, though I'm sure your could work something up that allows you to construct and eval some code to auto-configure this for a given DBH object.

除了污染不属于您的命名空间外，您还必须为您使用的每个数据库驱动程序编写此命令，但我确信您可以使用某些功能，允许您构建和评估某些代码以自动配置给定的DBH对象。

#1