MySQL 我如何知道 Perl DBI 查询返回多少行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/440799/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I know how many rows a Perl DBI query returns?
提问by The.Anti.9
I'm trying to basically do a search through the database with Perl to tell if there is an item with a certain ID. This search can return no rows, but it can also return one.
我基本上是在尝试使用 Perl 在数据库中进行搜索，以判断是否存在具有特定 ID 的项目。此搜索不能返回任何行，但它也可以返回一个。
I have the following code:
我有以下代码：
my $th = $dbh->prepare(qq{SELECT bi_exim_id FROM bounce_info WHERE bi_exim_id = '$exid'});
$th->execute();
if ($th->fetch()->[0] != $exid) {
...
Basically, this tries to see if the ID was returned and if it's not, continue with the script. But it is throwing a Null array reference error on the $th->fetch()->[0]thing.
How can i just simply check to see if it returned rows or now?
基本上，这会尝试查看 ID 是否已返回，如果没有，则继续执行脚本。但它在$th->fetch()->[0]事物上抛出 Null 数组引用错误。我怎样才能简单地检查它是否返回了行或现在？
采纳答案by Paul Tomblin
my $th = $dbh->prepare(qq{SELECT bi_exim_id FROM bounce_info WHERE bi_exim_id = '$exid'});
$th->execute();
my $found = 0;
while ($th->fetch())
{
$found = 1;
}
Your query won't return anything if the row doesn't exist, so you can't de-reference the fetch.
如果该行不存在，您的查询将不会返回任何内容，因此您无法取消引用提取。
Update: you might want to re-write that as
更新：您可能想将其重写为
my $found = $th->fetch();
回答by brian d foy
The DBD::mysqldriver has a the rows()method that can return the count of the results:
该DBD :: mysql的驱动程序有一个的rows()，可以返回结果的计数方法：
$sth = $dbh->prepare( ... );
$sth->execute;
$rows = $sth->rows;
This is database-driver specific, so it might not work in other drivers, or it might work differently in other drivers.
这是特定于数据库驱动程序的，因此它可能无法在其他驱动程序中工作，或者在其他驱动程序中可能以不同的方式工作。
回答by runrig
Why don't you just "select count(*) ..."??
你为什么不只是“选择计数（*）......”？？
my ($got_id) = $dbh->selectrow_array("SELECT count(*) from FROM bounce_info WHERE bi_exim_id = '$exid'");
Or to thwart Little Bobby Tables:
或者阻止Little Bobby Tables：
my $q_exid = $dbh->quote($exid);
my ($got_id) = $dbh->selectrow_array("SELECT count(*) from FROM bounce_info WHERE bi_exim_id = $q_exid");
Or if you're going to execute this a lot:
或者，如果您要执行很多次：
my $sth = $dbh->prepare("SELECT count(*) from FROM bounce_info WHERE bi_exim_id = ?");
....save $sth (or use prepare_cached()) and then later
my ($got_id) = $dbh->selectrow_array($sth, undef, $exid);
回答by runrig
Change select to always return something? This should work in Sybase, dunno about other DBs.
更改选择以始终返回某些内容？这应该适用于 Sybase，不知道其他数据库。
my $th = $dbh->prepare(qq{SELECT count(*) FROM bounce_info WHERE bi_exim_id = '$exid'});
$th->execute();
if ($th->fetch()->[0]) {
....
}
回答by jrockway
In general, I'm not sure why people are so afraid of exceptions. You catch them and move on.
总的来说，我不确定人们为什么如此害怕例外。你抓住他们并继续前进。
my $sth = prepare ...
$sth->execute;
my $result = eval { $sth->fetchrow_arrayref->[1] };
if($result){ say "OH HAI. YOU HAVE A RESULT." }
else { say "0 row(s) returned." }
In this case, though, Paul's answer is best.
不过，在这种情况下，保罗的回答是最好的。
Also, $sth->rowsdoesn't usually work until you have fetched every row. If you want to know how many rows match, then you have to actually ask the database engine the question you want to know the answer to; namely select count(1) from foo where bar='baz'.
此外，$sth->rows在您获取每一行之前通常不会工作。如果您想知道有多少行匹配，那么您必须实际向数据库引擎询问您想知道答案的问题；即select count(1) from foo where bar='baz'。
回答by Dave Sherohman
The only reliable way to find out how many rows are returned by a SELECT query is to fetch them all and count them. As stated in the DBI documentation:
找出 SELECT 查询返回多少行的唯一可靠方法是获取所有行并对其进行计数。如DBI 文档中所述：
Generally, you can only rely on a row count after a non-SELECT execute (for some specific operations like UPDATE and DELETE), or after fetching all the rows of a SELECT statement.
For SELECT statements, it is generally not possible to know how many rows will be returned except by fetching them all. Some drivers will return the number of rows the application has fetched so far, but others may return -1 until all rows have been fetched. So use of the rows method or $DBI::rows with SELECT statements is not recommended.
通常，您只能在非 SELECT 执行之后（对于某些特定操作，如 UPDATE 和 DELETE）或在获取 SELECT 语句的所有行之后依赖行计数。
对于 SELECT 语句，通常不可能知道将返回多少行，除非将它们全部取出。一些驱动程序将返回应用程序到目前为止已提取的行数，但其他驱动程序可能会返回 -1，直到所有行都已提取。因此，不建议在 SELECT 语句中使用 rows 方法或 $DBI::rows 。
However, when you get down to it, you almost never need to know that in advance anyhow. Just loop on while ($sth->fetch)to process each row or, for the special case of a query that will only return zero or one rows,
然而，当你深入了解它时，你几乎不需要提前知道。只需循环while ($sth->fetch)处理每一行，或者对于只返回零或一行的查询的特殊情况，
if ($sth->fetch) {
# do stuff for one row returned
} else {
# do stuff for no rows returned
}
回答by LeeGee
This article may users of MySQL prior to version 8:
本文可能是 MySQL 8 之前版本的用户：
http://www.arraystudio.com/as-workshop/mysql-get-total-number-of-rows-when-using-limit.html
http://www.arraystudio.com/as-workshop/mysql-get-total-number-of-rows-when-using-limit.html
Luckily since MySQL 4.0.0 you can use SQL_CALC_FOUND_ROWS option in your query which will tell MySQL to count total number of rows disregarding LIMIT clause. You still need to execute a second query in order to retrieve row count, but it's a simple query and not as complex as your query which retrieved the data.
Usage is pretty simple. In you main query you need to add SQL_CALC_FOUND_ROWS option just after SELECT and in second query you need to use FOUND_ROWS() function to get total number of rows. Queries would look like this:
SELECT SQL_CALC_FOUND_ROWS name, email FROM users WHERE name LIKE 'a%' LIMIT 10;
SELECT FOUND_ROWS();
The only limitation is that you must call second query immediately after the first one because SQL_CALC_FOUND_ROWS does not save number of rows anywhere.
Although this solution also requires two queries it's much more faster, as you execute the main query only once.
You can read more about SQL_CALC_FOUND_ROWS and FOUND_ROWS() in MySQL docs.
幸运的是，从 MySQL 4.0.0 开始，您可以在查询中使用 SQL_CALC_FOUND_ROWS 选项，它会告诉 MySQL 计算总行数，而不管 LIMIT 子句。您仍然需要执行第二个查询来检索行数，但它是一个简单的查询，不像检索数据的查询那么复杂。
用法很简单。在主查询中，您需要在 SELECT 之后添加 SQL_CALC_FOUND_ROWS 选项，在第二个查询中，您需要使用 FOUND_ROWS() 函数来获取总行数。查询将如下所示：
SELECT SQL_CALC_FOUND_ROWS name, email FROM users WHERE name LIKE 'a%' LIMIT 10;
选择 FOUND_ROWS();
唯一的限制是您必须在第一个查询之后立即调用第二个查询，因为 SQL_CALC_FOUND_ROWS 不会在任何地方保存行数。
尽管此解决方案也需要两次查询，但速度要快得多，因为您只执行一次主查询。
您可以在 MySQL 文档中阅读有关 SQL_CALC_FOUND_ROWS 和 FOUND_ROWS() 的更多信息。
Sadly, as of 8.0.17:
遗憾的是，从 8.0.17 开始：
The SQL_CALC_FOUND_ROWS query modifier and accompanying FOUND_ROWS() function are deprecated as of MySQL 8.0.17 ...
从 MySQL 8.0.17 开始，不推荐使用 SQL_CALC_FOUND_ROWS 查询修饰符和随附的 FOUND_ROWS() 函数...
Details here.
详情请看这里。
