The Found_rows () function in MySQL

Migrating SQL Server stored procedures to MySQL, encountered in SQL Server:

IF @ @ROWCOUNT < 1

The corresponding to MySQL can be replaced by using the Found_rows () function.

1. documentation for the Found_rows () function:

Http://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_found-rows

1)First use of Found_rows () ( with SQL_CALC_FOUND_ROWS,也带有 limit ):

A SELECT statement may include a clause to restrict the number of LIMIT rows the server returns to the client. In some cases, it's desirable to know how many rows the statement would has returned without LIMIT the, but without Runn ing the statement again. To obtain this row count, include a SQL_CALC_FOUND_ROWS option SELECT in the statement, and then invoke FOUND_ROWS() afterward:

tbl_name    WHERE id > 100 LIMIT 10;SELECT FOUND_ROWS();

The second SELECT returns a number indicating how many rows the first SELECT would has returned had it been written without The LIMIT clause.

The preceding SELECT statement with limit returns the number of rows returned by the preceding statement without the limit statement if it is SQL_CALC_FOUND_ROWS added SELECT FOUND_ROWS(); .

2)second/third use of Found_rows () ( not with SQL_CALC_FOUND_ROWS ):

In the absence of the sql_calc_found_rows option in the most recent successful select statement, found_rows () returns the number of ROWS in the result SE T returned by that statement. if the statement includes a limit clause, found_rows () returns the number of Rows up to the limit . For example, found_rows () Returns or, respectively, if the statement includes limit or limit .

The row count available through is transient and not intended to be available past the FOUND_ROWS() statement following SELECT SQL_CALC_FOUND_ROWS the Statement. If you need to refer to the value later, save it:

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

If you is using SELECT SQL_CALC_FOUND_ROWS , MySQL must calculate how many rows is in the full result set. However, this was faster than running the query again without LIMIT , because the result set need not being sent to the client.

1> Second use case ( not with SQL_CALC_FOUND_ROWS,也没有带 limit ):

If the previous SELECT statement does not have aSQL_CALC_FOUND_ROWS，也没有带 limit ，那么后面的 SELECT FOUND_ROWS(); 返回的结果就是前面的select返回的行数；

2> Third use case ( not with SQL_CALC_FOUND_ROWS,但是有带 limit ):

If the previous SELECT statement does not have aSQL_CALC_FOUND_ROWS，但是带有 limit，那么后面的 SELECT FOUND_ROWS(); 返回的结果就是limit语句到达的最大的行数，比如：select * from xxx limit 10; 到达的最大的行数为10，所以 found_rows() 返回10；比如 select * from xxx limit 50,10; 它要从第50行开始，再扫描10行，所以到达的最大的行数为60，所以found_rows() 返回60。

Here the first select Found_rows () returns 105, since he is from offset 100, then scans 5 rows, so returns 105, but the second scan results in null, and select Found_rows () returns 0! Instead of the userid=999999, because the where result is empty, so the trailing limit 100, 5 is not executed at all . so select Found_rows () returned 0.

Take another example to understand more deeply the case of Found_rows ():

The value of USER_PWD=XX in SQL above is the same. You can see that in this case the found_rows () is the middle result of the SELECT statement, and then the maximum number of rows that the limit is scanned at when limit is reached. It is not related to the number of rows of data in the original table . He is the limit of the intermediate result of select, then the final result set is obtained and then returned.

3) SQL_CALC_FOUND_ROWS FOUND_ROWS() and suitable for use in the scene

SQL_CALC_FOUND_ROWSFOUND_ROWS()and can be useful in situations if you want to restrict the number of rows that a query returns, but also determin E The number of rows in the full result set without running the query again. An example are a WEB script that presents a paged display containing links to the pages that show other sections of a Searc H result. Using enables determine how many and other pages is needed for the rest of the FOUND_ROWS() result.

1> Sql_calc_found_row + limit + found_rows () can be used on paging occasions.

2> Found_rows () without sql_calc_found_row can be used to determine whether the preceding select is empty in a stored procedure :

DELIMITER//DROP PROCEDURE IF EXISTSLoginandreg//CREATE PROCEDURELoginandreg (out UserIdBIGINT,    inchUser_pwdVARCHAR( +),    inchUser_mobilecodeVARCHAR( -),    inchUser_regipVARCHAR( -))BEGINIF EXISTS(SELECT *  fromUsers uWHEREU.user_mobilecode=User_mobilecode) Then    SELECTU.userid intoUserid fromUsers uWHEREU.user_mobilecode=User_mobilecode andU.user_pwd=user_pwd; IFFound_rows ()< 1  Then        SELECT -1  intouserId; END IF;ELSE     INSERT  intoUsers (user_pwd,user_mobilecode,user_visibility,user_level,user_regtime,user_regip,user_collecter,user_ Collected)VALUES(User_pwd,user_mobilecode,6,6, now (), USER_REGIP,0,0); SELECTLAST_INSERT_ID () intouserId;END IF;END //DELIMITER;

In the stored procedure above:

SELECT  into  from WHERE U.user_mobilecode= and u.user_pwd=user_pwd;         IF < 1  then        SELECT - 1  into userId;     END IF;

is a good example of how to use it.

In this stored procedure scenario, you can replace the IF @ @ROWCOUNT < 1 statements in the SQL Server stored procedure with MySQL's found_rows () .

The Found_rows () function in MySQL