All Products
Search
Document Center

Information functions

Last Updated: Jan 07, 2020

FOUND_ROWS

Syntax

FOUND_ROWS()

Description

A SELECT statement may contain a LIMIT clause to limit the number of rows returned from the database server to the client. In some cases, you want to know how many rows the statement returns without running the statement again and without using the LIMIT clause. You can use SQL_CALC_FOUND_ROWS in the SELECT statement, and then call the FOUND_ROWS() function to get the number of rows returned by the statement without the LIMIT clause.

Example:

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

The second SELECT statement returns a number indicating how many rows were returned by the first SELECT statement without using the LIMIT clause. If the preceding SELECT statement does not use the SQL_CALC_FOUND_ROWS option, the results of FOUND_ROWS() may differ depending on whether the LIMIT clause is used.

The number of valid rows returned by the FOUND_ROWS() function is instantaneous and always appears before the statements that are after the SELECT SQL_CALC_FOUND_ROWS statement. You must save it if you want to use this value later.

For example:

  1. mysql> SELECT SQL_CALC_FOUND_ROWS * FROM ... ;
  2. mysql> SET @rows = FOUND_ROWS();

If you are using SQL_CALC_FOUND_ROWS, the system must calculate how many rows the entire result set contains. This is faster than running the query again without LIMIT because the result set does not need to be sent to the client.

SQL_CALC_FOUND_ROWS and FOUND_ROWS() are useful for limiting 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. For example, in the Web script which implements paged information presentation containing hyperlinks to pages that show other parts of the search results, you can use FOUND_ROWS() to determine how many additional pages are needed to display the remaining results.

The use of SQL_CALC_FOUND_ROWS and FOUND_ROWS() is more complex for UNION statements 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 applied to the entire UNION result.

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 using SQL_CALC_FOUND_ROWS with UNION are:

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

  • The value of FOUND_ROWS() is exact only when UNION ALL is used. If UNION without ALL is used, duplicate removal occurs and the value of FOUND_ROWS() is only approximate.

  • 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.

LAST_INSERT_ID()

Syntax

last_insert_id()

Description

Returns the integer that is automatically generated for an AUTO_INCREMENT column by the previous INSERT or UPDATE statement. If you insert multiple rows into the table using a single INSERT statement, the LAST_INSERT_ID() function returns the first automatically generated value only.

Example:

  1. mysql>select LAST_INSERT_ID();
  2. +------------------+
  3. | LAST_INSERT_ID() |
  4. +------------------+
  5. | 5 |
  6. +------------------+
  7. 1 row in set (0.00 sec)