使用limit查询的同时取得总的记录数：SQL_CALC_FOUND_ROWS和FOUND_ROWS()

时间：2022-07-15 20:20:36

通常我们都用如下的sql来进行列表

SELECT COUNT(*) FROM users WHERE name LIKE ‘a%’;
SELECT name, email FROM users WHERE name LIKE ‘a%’ LIMIT 10;

    但是从Mysql4.0.0开始，我们可以选择使用另外一个方式：

SELECT SQL_CALC_FOUND_ROWS name, email FROM users WHERE name LIKE ‘a%’ LIMIT 10;
SELECT FOUND_ROWS();

    其中SQL_CALC_FOUND_ROWS 告诉MySQL将sql所处理的行数记录下来，FOUND_ROWS() 则取到了这个纪录。

    虽然也是两个语句，但是只执行了一次主查询，所以效率比原来要高很多。

    详细信息见这里 http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

－－－－以下为个人翻译－－－－

    FOUND_ROWS()

    A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To obtain this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward:

    SELECT语句中经常可能用LIMIT限制返回行数。有时候可能想要知道如果没有LIMIT会返回多少行，但又不想再执行一次相同语句。那么，在SELECT查询中包含SQL_CALC_FOUND_ROWS选项，然后执行FOUND_ROWS()就可以了：

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

    The second SELECT returns a number indicating how many rows the first SELECT would have returned had it been written without the LIMIT clause.

    第二个SELECT将返回第一条SELECT如果没有LIMIT时返回的行数。

    In the absence of the SQL_CALC_FOUND_ROWS option in the most recent SELECT statement,FOUND_ROWS() returns the number of rows in the result set returned by that statement.

    如果在前一条语句中没有使用SQL_CALC_FOUND_ROWS选项，FOUND_ROWS()将返回前一条语句实际返回的行数。

    The row count available through FOUND_ROWS() is transient and not intended to be available past the statement following the SELECT SQL_CALC_FOUND_ROWS statement. If you need to refer to the value later, save it:

    FOUND_ROWS()得到的数字是临时的，执行下一条语句就会失效。如果想要这个数字，就要将它保存下来：

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM ... ;mysql> SET @rows = FOUND_ROWS();

    If you are using SELECT SQL_CALC_FOUND_ROWS, mysql must calculate how many rows are in the full result set. However, this is faster than running the query again without LIMIT, because the result set need not be sent to the client.

    如果使用 SELECT SQL_CALC_FOUND_ROWS，MySQL必须计算所有结果集的行数。尽管这样，总比再执行一次不使用LIMIT的查询要快多了吧，因为那样结果集要返回客户端滴。（阿冬注：应该不单是没有将结果集返回的原因，还有原因可能是比如LIKE之类比较费劲的SQL不需要再去劳累一次。）（突然想起来MYSQL5.1是有中文文档的，在12.9.3节。）

    SQL_CALC_FOUND_ROWS and FOUND_ROWS() can be useful in situations when you want to restrict the number of rows that a query returns, but also determine the number of rows in the full result set without running the query again. An example is a Web script that presents a paged display containing links to the pages that show other sections of a search result. Using FOUND_ROWS() allows you to determine how many other pages are needed for the rest of the result.

    The use of SQL_CALC_FOUND_ROWS and FOUND_ROWS() is more complex for UNIONstatements than for simple SELECT statements, because LIMIT may occur at multiple places in a UNION. It may be applied to individual SELECT statements in the UNION, or global to theUNION result as a whole.

    The intent of SQL_CALC_FOUND_ROWS for UNION is that it should return the row count that would be returned without a global LIMIT. The conditions for use of SQL_CALC_FOUND_ROWSwith UNION are:

  • The SQL_CALC_FOUND_ROWS keyword must appear in the first SELECT of the UNION.
  • The value of FOUND_ROWS() is exact only if UNION ALL is used. If UNION without ALL is used, duplicate removal occurs and the value of FOUND_ROWS() is only approximate.
  • If no LIMIT is present in the UNION, SQL_CALC_FOUND_ROWS is ignored and returns the number of rows in the temporary table that is created to process the UNION.
标签：

相关文章