JAVA Web開發MySQL資料庫記錄瀏覽分頁

首先，MySQL支援limit關鍵字，這個關鍵字可以讓MySQL只返回我們指定的一段記錄，比如： 

select * from usermsgbox limit 3 

這條SQL語句就可以讓MySQL返回usermsgbox表中的前三條記錄，也可以這樣： 

select * from usermsgbox limit 1,3 

這個意思是這樣的：返回usermsgbox表中第二條記錄開始往後的三條，也就是2，3，4這三條記錄。這裡要千萬小心了：（1）limit的第一個參數是從哪條記錄開始，注意，這裡第一條記錄在limit中編號是0，所以上述例子中limit從1開始，其實是從第二條記錄開始；（2）第二個參數3指的是共取3條記錄出來，不是取第1-3條記錄，千萬記住了。 

有了這個工具，實現分頁就方便多了，不過在查看了MySQL的手冊後，發現MySQL在limit的基礎上提供了更方便的關鍵字： 

CODE: SELECT ALL

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

這裡主要是SQL_CALC_FOUND_ROWS和FOUND_ROWS()這兩個東西，第一個是SELECT的一個option，第二個是一個函數。有這兩個有什麼好處呢？如果不用這個，我們做分頁，需要首先把所有記錄取出來，然後得到記錄總條數，然後再用limit取出我們需要的那些記錄，很煩，尤其是第一次查詢，需要把所有的結果集都返回到用戶端，所以效能很差。為此，MySQL提供了SQL_CALC_FOUND_ROWS這個option，用上這個option之後，第一條SQL中雖然返回的還是limit指定的那些記錄，但是不加limit的這條SQL語句一共能取到多少條記錄，就被計算出來了。然後在第二條SQL中，我們用FOUND_ROWS()這個函數就可以把這個總記錄條數取出來了。這樣就避免了我們手動寫一個不帶limit的SQL語句，效能提升了，我們coding也方便了。記住哦，第一條SQL中，SQL_CALC_FOUND_ROWS計算的是不帶limit從句的SQL語句能select到的記錄總數哦。 

該項功能從MySQL 4開始支援。更詳細的看下面的英文原文： 

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 get this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, then invoke FOUND_ROWS() afterward: 

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

The second SELECT will return a number indicating how many rows the first SELECT would have returned had it been written without the LIMIT clause. (If the preceding SELECT statement does not include the SQL_CALC_FOUND_ROWS option, then FOUND_ROWS() may return a different result when LIMIT is used than when it is not.) Note that 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. 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 UNION queries 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 the UNION 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_ROWS with UNION are: 

1. The SQL_CALC_FOUND_ROWS keyword must appear in the first SELECT of the UNION. 
2. 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. 
3. 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. 

SQL_CALC_FOUND_ROWS and FOUND_ROWS() are available starting at MySQL 4.0.0.