如何计算mysql查询返回的行数?

How can I count the numbers of rows that a mysql query returned? using PHP..

如何计算mysql查询返回的行数?使用PHP . .

8 个解决方案

#1

Getting total rows in a query result...

You could just iterate the result and count them. You don't say what language or client library you are using, but the API does provide a mysql_num_rows function which can tell you the number of rows in a result.

您可以迭代结果并计算它们。您没有说明正在使用什么语言或客户端库，但是API确实提供了一个mysql_num_rows函数，它可以告诉您结果中的行数。

This is exposed in PHP, for example, as the mysqli_num_rows function. As you've edited the question to mention you're using PHP, here's a simple example using mysqli functions:

例如，它在PHP中作为mysqli_num_rows函数公开。正如您在编辑问题时提到您正在使用PHP一样，这里有一个使用mysqli函数的简单示例:

$link = mysqli_connect("localhost", "user", "password", "database");

$result = mysqli_query($link, "SELECT * FROM table1");
$num_rows = mysqli_num_rows($result);

echo "$num_rows Rows\n";

Getting a count of rows matching some criteria...

Just use COUNT(*) - see Counting Rows in the MySQL manual. For example:

只需使用COUNT(*)——参见MySQL手册中的计数行。例如:

SELECT COUNT(*) FROM foo WHERE bar= 'value';

Get total rows when LIMIT is used...

If you'd used a LIMIT clause but want to know how many rows you'd get without it, use SQL_CALC_FOUND_ROWS in your query, followed by SELECT FOUND_ROWS();

如果您使用了一个LIMIT子句，但是想知道没有它会有多少行，请在查询中使用SQL_CALC_FOUND_ROWS，然后选择FOUND_ROWS();

SELECT SQL_CALC_FOUND_ROWS * FROM foo
   WHERE bar="value" 
   LIMIT 10;

SELECT FOUND_ROWS();

For very large tables, this isn't going to be particularly efficient, and you're better off running a simpler query to obtain a count and caching it before running your queries to get pages of data.

对于非常大的表，这并不是特别有效，您最好运行一个更简单的查询来获取一个计数并在运行查询以获取数据页之前缓存它。

#2

In the event you have to solve the problem with simple SQL you might use a subquery.

如果您必须使用简单的SQL来解决问题，那么您可以使用子查询。

select count(*) from (select * from foo) as x;

#3

If your SQL query has a LIMIT clause and you want to know how many results total are in that data set you can use SQL_CALC_FOUND_ROWS followed by SELECT FOUND_ROWS(); This returns the number of rows A LOT more efficiently that using COUNT(*)
Example (straight from MySQL docs):

如果SQL查询有一个LIMIT子句，您想知道数据集中有多少结果，可以使用SQL_CALC_FOUND_ROWS，然后是SELECT FOUND_ROWS();这比使用COUNT(*)示例(直接从MySQL文档返回)更有效地返回行数:

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
    -> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

#4

If you want the result plus the number of rows returned do something like this. Using PHP.

如果想要结果加上返回的行数，可以这样做。使用PHP。

$query = "SELECT * FROM Employee";
$result = mysql_query($query);
echo "There are ".mysql_num_rows($result)." Employee(s).";

#5

Assuming you're using the mysql_ or mysqli_ functions, your question should already have been answered by others.

假设您正在使用mysql_或mysqli_函数，那么您的问题应该已经得到了其他人的回答。

However if you're using PDO, there is no easy function to return the number of rows retrieved by a select statement, unfortunately. You have to use count() on the resultset (after assigning it to a local variable, usually).

但是，如果您使用PDO，那么不幸的是，没有简单的函数返回select语句检索的行数。您必须在resultset上使用count()(通常是在分配给本地变量之后)。

Or if you're only interested in the number and not the data, PDOStatement::fetchColumn() on your SELECT COUNT(1)... result.

或者，如果您只对数字感兴趣，而不是对数据感兴趣，那么您的SELECT COUNT(1)中的PDOStatement::::fetchColumn()……结果。

#6

SELECT SQL_CALC_FOUND_ROWS *
FROM   table1
WHERE  ...;

SELECT FOUND_ROWS();

FOUND_ROWS() must be called immediately after the query.

必须在查询之后立即调用FOUND_ROWS()。

#7

If you're fetching data using Wordpress, then you can access the number of rows returned using $wpdb->num_rows:

如果您正在使用Wordpress获取数据，那么您可以使用$wpdb->num_rows访问返回的行数:

$wpdb->get_results( $wpdb->prepare('select * from mytable where foo = %s', $searchstring));
echo $wpdb->num_rows;

If you want a specific count based on a mysql count query then you do this:

如果您想要基于mysql计数查询的特定计数，那么您可以这样做:

$numrows = $wpdb->get_var($wpdb->prepare('SELECT COUNT(*) FROM mytable where foo = %s', $searchstring );
echo $numrows;

If you're running updates or deletes then the count of rows affected is returned directly from the function call:

如果您正在运行更新或删除，那么受影响的行数将直接从函数调用返回:

$numrowsaffected = $wpdb->query($wpdb->prepare(
   'update mytable set val=%s where myid = %d', $valuetoupdate, $myid));

This applies also to $wpdb->update and $wpdb->delete.

这也适用于$wpdb->更新和$wpdb->删除。

#8

As it is 2015, and deprecation of mysql_* functionality, this is a PDO-only visualization.

由于是2015年，而且不支持mysql_*功能，所以这是一个只支持pdo的可视化。

<?php
    // Begin Vault (this is in a vault, not actually hard-coded)
    $host="hostname";
    $username="GuySmiley";
    $password="thePassword";
    $dbname="dbname";
    // End Vault

    $b='</br>';
    try {
        $theCategory="fruit";   // value from user, hard-coded here to get one in

        $dbh = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
        $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        // prepared statement with named placeholders
        $stmt = $dbh->prepare("select id,foodName from foods where category=:theCat and perishable=1");
        $stmt->bindParam(':theCat', $theCategory, PDO::PARAM_STR,20);
        $stmt->execute();
        echo "rowCount() returns: ".$stmt->rowCount().$b;   // See comments below from the Manual, varies from driver to driver

        $stmt = $dbh->prepare("select count(*) as theCount from foods where category=:theCat and perishable=1");
        $stmt->bindParam(':theCat', $theCategory, PDO::PARAM_STR,20);
        $stmt->execute();
        $row=$stmt->fetch();    // fetches just one row, which is all we expect
        echo "count(*) returns: ".$row['theCount'].$b;

        $stmt = null;
        // PDO closes connection at end of script
    } catch (PDOException $e) {
        echo 'PDO Exception: ' . $e->getMessage();
        exit();
    }
?>

Schema for testing

create table foods
(   id int auto_increment primary key,
    foodName varchar(100) not null,
    category varchar(20) not null,
    perishable int not null
);
insert foods (foodName,category,perishable) values 
('kiwi','fruit',1),('ground hamburger','meat',1),
('canned pears','fruit',0),('concord grapes','fruit',1);

For my implementation, I get the output of 2 for both echos above. The purpose of the above 2 strategies is to determine if your driver implementation emits the rowCount, and if not, to seek a fall-back strategy.

对于我的实现，我得到上面两个echos的输出为2。上述两种策略的目的是确定驱动程序实现是否发出行计数，如果不是，则寻找后退策略。

From the Manual on PDOStatement::rowCount:

来自PDOStatement手册::rowCount:

PDOStatement::rowCount() returns the number of rows affected by a DELETE, INSERT, or UPDATE statement.

PDOStatement::rowCount()返回受删除、插入或更新语句影响的行数。

For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement. Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of rows that will be returned. Your application can then perform the correct action.

对于大多数数据库，PDOStatement::rowCount()不返回SELECT语句影响的行数。相反，使用PDO::query()发出SELECT COUNT(*)语句，该语句的谓词与预期的SELECT语句相同，然后使用PDOStatement:::fetchColumn()检索将返回的行数。然后应用程序可以执行正确的操作。