Information functions

FOUND_ROWS

Declaration

found_rows()

Description

A SELECT statement may contain a LIMIT clause to restrict the number of rows returned by the database server to the client. In some cases, you do not need to run the statement again to determine the number of rows returned by this statement with the absence of LIMIT. You can use SQL_CALC_FOUND_ROWS in the SELECT statement, and then call the FOUND_ROWS() function to determine the number of rows returned by this statement with the absence of LIMIT.

Examples:

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

The second SELECT statement returns a number, indicating the number of rows returned by the first SELECT statement with the absence of the LIMIT clause. If the preceding SELECT statement does not use the SQL_CALC_FOUND_ROWS option, the FOUND_ROWS() statement may return different results when the LIMIT clause is or is not used.

The number of valid rows returned by FOUND_ROWS() is instantaneous and cannot skip the statement after SELECT SQL_CALC_FOUND_ROWS. Save the value if you need to use it later.

Examples:

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

If you are using SQL_CALC_FOUND_ROWS, the system must calculate the total number of rows in all result sets. However, this is still faster than running the query again without using the LIMIT clause, because the result sets do not need to be sent to the client.

SQL_CALC_FOUND_ROWS and FOUND_ROWS() are very useful when you want to limit the number of rows returned by a query. You can also determine the number of rows in all the result sets without running the query again. Take a Web script that provides page display as an example. It contains links to pages that display other parts of the search results. You can use FOUND_ROWS() to determine the number of other pages that are needed to display the remaining results.

The application of SQL_CALC_FOUND_ROWS and FOUND_ROWS() for simple SELECT statements is more complex than the application for the UNION query. This is because the LIMIT clause may appear in multiple positions in UNION. It may apply to independent SELECT statements in UNION or the entire UNION result.

SQL_CALC_FOUND_ROWS expects UNION to return the number of rows that must be returned when no global LIMIT clause exists. Conditions for using SQL _CALC_FOUND_ROWS and UNION in conjunction:

• The SQL_CALC_FOUND_ROWS keyword must appear in the first SELECT statement of UNION.

• The value of FOUND_ROWS() is accurate only when UNION ALL is used. If you use a UNION statement without specifying the ALL option, the delete operation occurs twice, and the value of FOUND_ROWS() only needs to be approximate.

• If the UNION statement does not contain a LIMIT clause, SQL_CALC_FOUND_ROWS is ignored, and the number of rows created in the temporary table for UNION is returned.

LAST_INSERT_ID()

Declaration

last_insert_id()

Description

This function returns the value of the last auto-increment field inserted in the current session. If the recent INSERT operation inserted multiple records, LAST_INSERT_ID() returns the auto-increment field value of the first record.

Example

obclient> select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                5 |
+------------------+
1 row in set (0.00 sec)