MySQL COUNT查询始终为1

Question

I'm wondering why my MySQL COUNT(*) query always results in ->num_rows to be equal 1.

$result = $db->query("SELECT COUNT( * ) FROM u11_users");
print $result->num_rows; // prints 1

Whereas fetching "real data" from the database works fine.

$result = $db->query("SELECT * FROM u11_users");
print $result->num_rows; // prints the correct number of elements in the table

What could be the reason for this?

Answer 1

Count() is an aggregate function which means it returns just one row that contains the actual answer. You'd see the same type of thing if you used a function like max(id); if the maximum value in a column was 142, then you wouldn't expect to see 142 records but rather a single record with the value 142. Likewise, if the number of rows is 400 and you ask for the count(*), you will not get 400 rows but rather a single row with the answer: 400.

So, to get the count, you'd run your first query, and just access the value in the first (and only) row.

By the way, you should go with this count(*) approach rather than querying for all the data and taking $result->num_rows; because querying for all rows will take far longer since you're pulling back a bunch of data you do not need.