MySQL中SQL_CALC_FOUND_ROWS的用法

標籤：post   most   union   because   完整   row   may   query   some   

1. SQL_CALC_FOUND_ROWS簡述

在很多分頁的程式中都這樣寫:

#查出合格記錄總數SELECT COUNT(*) from [table] WHERE  ......;  #查詢當頁要顯示的資料SELECT * FROM [table]  WHERE ...... limit M,N; 

但是從Mysql4.0.0開始，我們可以選擇使用另外一個方式：

SELECT SQL_CALC_FOUND_ROWS * FROM [table] WHERE ......  limit M, N;SELECT FOUND_ROWS();#SQL_CALC_FOUND_ROWS 告訴MySQL將sql所處理的行數記錄下來#FOUND_ROWS() 則取到了這個紀錄。

雖然也是兩個語句，但是只執行了一次主查詢，所以效率比原來要高很多。

2.SQL_CALC_FOUND_ROWS FOUND_ROWS()文檔中英對照

FOUND_ROWS()A SELECT statement may include a LIMIT clause to restrict the number of rows the server returnsto 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 returnedhad 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 fullresult 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的查詢要快多了，因為結果集不需要返回用戶端。SQL_CALC_FOUND_ROWS and FOUND_ROWS() can be useful in situations when you want to restrict thenumber of rows that a query returns, but also determine the number of rows in the full resultset without running the query again.當你想要限制查詢的返回行數的同時又想得到查詢的完整結果集合的行數,但又不想重複執行一次查詢,那麼SQL_CALC_FOUND_ROWS and FOUND_ROWS() 是非常有用的！

3.UNION 語句下的用法3.1 SQL_CALC_FOUND_ROWS只能出現在UNION的第一個SELECT中,否則編譯報錯！

mysql> (select  SQL_CALC_FOUND_ROWS  * from  actor  limit 1)  union    (select SQL_CALC_FOUND_ROWS  * from  actor limit 1);ERROR 1234 (42000): Incorrect usage/placement of 'SQL_CALC_FOUND_ROWS'

3.2 limit中 select 中的情況

#union all mysql> (select  SQL_CALC_FOUND_ROWS  * from  actor  limit 1)  union all   (select * from  actor limit 1);+----------+------------+-----------+---------------------+| actor_id | first_name | last_name | last_update         |+----------+------------+-----------+---------------------+|        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33 ||        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33 |+----------+------------+-----------+---------------------+2 rows in set (0.00 sec)mysql> select FOUND_ROWS();+--------------+| FOUND_ROWS() |+--------------+|            2 |+--------------+1 row in set (0.00 sec)

#union
mysql> (select SQL_CALC_FOUND_ROWS * from actor limit 1) union (select * from actor limit 1);
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)

mysql> select FOUND_ROWS();+--------------+| FOUND_ROWS() |+--------------+|            1 |+--------------+1 row in set (0.00 sec)

可以看到,limit中 select 中時,FOUND_ROWS()返回的時顯示的行數！

3.2 limit中 UNION 外的情況

mysql> (select  SQL_CALC_FOUND_ROWS  * from  actor  )  union    (select   * from  actor ) order by actor_id limit 2  ;+----------+------------+-----------+---------------------+| actor_id | first_name | last_name | last_update         |+----------+------------+-----------+---------------------+|        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33 ||        2 | NICK       | WAHLBERG  | 2006-02-15 04:34:33 |+----------+------------+-----------+---------------------+2 rows in set (0.00 sec)mysql> select FOUND_ROWS();+--------------+| FOUND_ROWS() |+--------------+|          200 |+--------------+1 row in set (0.00 sec)mysql> (select  SQL_CALC_FOUND_ROWS  * from  actor  )  union all   (select   * from  actor ) order by actor_id limit 2  ;+----------+------------+-----------+---------------------+| actor_id | first_name | last_name | last_update         |+----------+------------+-----------+---------------------+|        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33 ||        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33 |+----------+------------+-----------+---------------------+2 rows in set (0.01 sec)mysql> select FOUND_ROWS();+--------------+| FOUND_ROWS() |+--------------+|          400 |+--------------+1 row in set (0.00 sec)

可以看到,limit中 UNION 外時,FOUND_ROWS()返回的所有的行數！ UNION ALL 會返回2倍的行數

MySQL中SQL_CALC_FOUND_ROWS的用法