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.