MySQL选择行数作为额外的列? [英] MySql selecting count of rows as extra column?

问题描述

我需要一个MySql语句，该语句将选择所有行以及总共有多少行.

I need a MySql statement that will select all the rows, as well as how many total rows there are.

我正在使用

mysql_query("SELECT * FROM posts LIMIT 0, 5");

...试图添加计数:

...trying to add the count:

mysql_query("SELECT *, COUNT(*) AS total FROM posts LIMIT 0, 5");

...但是只返回一行.

...but that only returns a single row.

此外，如果有一种比向每行添加额外的列更好的方法来获取总数，那么我想这样做.谢谢！

Also, if there is a better way to get the total than to add an extra column to each row, then I would like that instead. Thank you!

推荐答案

-请参阅下面的@Ittai注释.提议的解决方案依赖于已弃用的功能 - https://dev.mysql.com/worklog/task/?id= 12615

-- Please see @Ittai 's comment below. The proposed solution relies on a feature that has been deprecated -- https://dev.mysql.com/worklog/task/?id=12615

我需要一个MySql语句，该语句将选择所有行以及总行数.

I need a MySql statement that will select all the rows, as well as how many total rows there are.

从字面上看，这是不可能的. SQL查询的结果是一个(虚拟)表.结果表中每行中的列提供的值仅与该行关联，并且各行实际上彼此独立.

When taken literally, this is not possible. The result of a SQL query is a (virtual) table; the column in each row in that result table provides a value that is associated with that row only, and the rows are in effect independent of each other.

有很多方法可以获取整个结果的行数，但是它可以用两个语句完成，也可以使用与您在概念上有所不同的查询来完成. (下面的解决方案)

There are many ways to grab the rowcount of the entire result, but it's either done in two statements or with a query that is conceptually different from what you have. (Solutions below)

您的原始问题中有一个方面可以用多种方式解释:

There is one aspect in your original question that could be interpreted in multiple ways:

以及总行数

as well as how many total rows there are

这可能意味着:

1. 计算结果中返回的每一行.
2. 如果不是LIMIT子句，则计算返回的每一行(在这种情况下，结果将被截断为5行)

(我将在下面为两个问题提供答案)

(I'll come up with answers for both below)

但是只返回一行.我猜这是因为COUNT(*)对于每一行都是相同的，并且由于某种原因，MySql只返回具有唯一值的行吗?我不知道.

but that only returns a single row. I'm guessing it's because COUNT(*) is going to be the same for each row, and for some reason, MySql is only returning rows with unique values for it? I have no clue.

COUNT是一个聚合函数.通过使用聚合函数，您要求数据库将行的行聚集在一起，并将其某些方面投影到单行中.令人困惑的是，mysql还会在同一SELECT列表中混合非聚合表达式和聚合表达式.大多数其他数据库不允许这样做，并且会为此提供一个错误，但是MySQL不允许这样做.

COUNT is an aggregate function. By using an aggregate function, you're asking the database to bunch up groups of rows and project some aspects of it into a single row. What is confusing is that mysql also you to mix non-aggregate and aggregate expressions in the same SELECT list. Most other databases don't allow this and give you an error for this, but alas MySQL does not.

但是COUNT(*)仍然将所有行聚合为一行，表示整个行组.

But COUNT(*) does still however aggregate all rows into a single row, that represents the entire group of rows.

此外，如果有一种比向每行添加额外的列更好的方法来获取总数，那么我想这样做.谢谢！

Also, if there is a better way to get the total than to add an extra column to each row, then I would like that instead. Thank you!

是的，有几种方法.

如果您想获取返回给PHP的行数，那么，在MySQL应用了它的limit子句后，我建议您简单地调用php函数mysql_num_rows(

If you want to get the number of rows returned to PHP, so, after MySQL applied its limit clause, I suggest simply calling the php function mysql_num_rows (http://www.php.net/manual/en/function.mysql-num-rows.php) after doing the mysql_query call.

如果要获取在没有LIMIT子句的情况下返回的行数，我建议分两个步骤进行操作:首先，对原始查询执行稍作修改的版本，然后立即执行之后，调用MySQL的FOUND_ROWS函数. (请参见 http://dev.mysql. com/doc/refman/5.5/en/information-functions.html#function_found-rows )

If you want to get the number of rows that would have been returned in absence of the LIMIT clause, I suggest doing it in 2 steps: first, execute a slighly modified version of your original query, and then, immmediately after that, call MySQL's FOUND_ROWS function. (see http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_found-rows)

它看起来像这样:

$result = mysql_query('SELECT SQL_CALC_FOUND_ROWS * FROM posts LIMIT 0, 5');
//do stuff with the result, but don't do any other queries

//get the total number of rows (disregarding the LIMIT clause) 
$result = mysql_query('SELECT FOUND_ROWS()');

SQL_CALC_FOUND_ROWS修饰符告诉MySQL在应用LIMIT子句之前要跟踪总行数，而FOUND_ROWS()返回该数字.请记住两点:

The SQL_CALC_FOUND_ROWS modifier tells MySQL to keep track of the total number of rows before applying the LIMIT clause, and FOUND_ROWS() returns that number. Keep 2 things in mind:

1. 这两个mysql_query调用均应在同一连接上执行
2. 在对mysql_query
的这些调用之间不执行其他查询

1. both these mysql_query calls should be executed over the same connection
2. Don't execute another query inbetween these calls to mysql_query

哦，最后一点:使用LIMIT时，通常希望以特定的方式对结果进行排序.通常人们使用LIMIT进行分页.如果不对行进行排序，则顺序是不确定的，即使LIMIT偏移量不同，后续查询也可能返回先前语句已经返回的行.您可以使用ORDER BY子句明确地对结果进行排序. ( http://dev.mysql.com/doc/refman/5.5/en/select.html )

Oh, final note: when using LIMIT, you typically want the results to be ordered in a particular way. Usually people use LIMIT for pagination. If you don't order the rows, the order is indeterminate and subsequent queries may return rows already returned by previous statements, even if the LIMIT offset is different. You can explicitly order the result by using an ORDER BY clause. (http://dev.mysql.com/doc/refman/5.5/en/select.html)

这篇关于MySQL选择行数作为额外的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！