Java Web development MySQL database record browsing page

MySQL supports the limit keyword, which allows MySQL to return only a specified record, for example:

Select * From usermsgbox limit 3

This SQL statement allows MySQL to return the first three records in the usermsgbox table, as well:

Select * From usermsgbox limit 1, 3

This means: return the three records starting from the second record in the usermsgbox table, that is, 2, 3, and 4. Be careful: (1) the first parameter of limit starts from which record. Note that the first record is numbered 0 in limit, therefore, in the above example, limit starts from 1 and actually starts from the second record. (2) The second parameter 3 refers to three records in total, instead of 1-3 Records, remember.

With this tool, it is much easier to implement paging. However, after reading the MySQL manual, we found that MySQL provides more convenient keywords Based on limit:

Code: select all

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

Here we mainly use SQL _calc_found_rows and found_rows (). The first is an option of select, and the second is a function. What are the advantages of these two products? If this is not needed, we need to retrieve all records first, then get the total number of records, and then use limit to retrieve the records we need, which is annoying, especially the first query, all result sets need to be returned to the client, so the performance is poor. Therefore, MySQL provides the SQL _calc_found_rows option. After using this option, the records specified by limit are returned in the first SQL statement, however, the total number of records that can be obtained by this SQL statement without limit is calculated. In the second SQL statement, we can use the found_rows () function to retrieve the total number of records. This avoids manual writing of an SQL statement without limit, which improves performance and facilitates coding. Remember, in the first SQL statement, SQL _calc_found_rows calculates the total number of records that can be selected by SQL statements without the limit clause.

This function is supported from MySQL 4. For more details, see the original English text below:

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 to handle rows the statement wowould 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 wowould 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 shoshould return the row count that wocould 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.