FOUND_ROWS () and ROW_COUNT () functions in MySQL _ MySQL

IF @ ROWCOUNT1 corresponds to mysql in sqlserver, you can replace it with the FOUND_ROWS () function. 1. found_rows () function documentation address: dev. mysql. comdocrefman5...... migrate the stored procedure of SQL server to mysql, encountered in SQL server:

IF @ ROWCOUNT <1

You can use the FOUND_ROWS () function to replace it with mysql.

1. found_rows () function

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

1) The first usage of found_rows () (with SQL _CALC_FOUND_ROWS and limit ):

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 obtain this row count, include a SQL _CALC_FOUND_ROWS option in the SELECT statement, and 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 returns a number indicating how should rows the first SELECT wowould have returned had it been written without the LIMIT clause.

If the select statement with limit is added with SQL _CALC_FOUND_ROWS, the SELECT FOUND_ROWS () statement executed next will return the number of rows returned by the preceding statement without limit.

In this case, select found_rows () and select count (*) have a very small difference: if userId permits null, select found_rows () the result may be smaller than select count. Because the former is equivalent to: select count (userId) from Users; this statement does not include rows where userId is null. Count (*) is included.

2) usage in second/third of found_rows () (without 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 set returned by that statement. if the statement provided Des a LIMIT clause, FOUND_ROWS () returns the number of rows up to the limit. for example, FOUND_ROWS () returns 10 or 60, respectively, if the statement LIMIT des LIMIT 10 or LIMIT 50, 10.

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

Mysql> SELECT SQL _CALC_FOUND_ROWS * FROM ...;

Mysql> SET @ rows = FOUND_ROWS ();

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.

1> second usage (neither SQL _CALC_FOUND_ROWS nor limit ):

If the preceding select statement does not contain SQL _CALC_FOUND_ROWS or limit, SELECT FOUND_ROWS (); The Returned result is the number of rows returned by the preceding select statement;

2> usage in the third phase (without SQL _CALC_FOUND_ROWS, but with limit ):

If the preceding select statement does not contain SQL _CALC_FOUND_ROWS but limit, SELECT FOUND_ROWS (); returns the maximum number of rows that the limit statement has reached. for example: select * from xxx limit 10; the maximum number of lines reached is 10, so found_rows () returns 10; for example, select * from xxx limit 50, 10; it starts from 50th rows, scan 10 rows again, so the maximum number of rows reached is 60, so found_rows () returns 60.

Here, the first select found_rows () returns 105, because it is from the offset of 100, and then scans 5 rows, so 105 is returned; but the result of the second scan is null, select found_rows () returns 0! Instead of 105, because the result of where userId = 999999 is null, the subsequent limit and 5 are not executed at all. So select found_rows () returns 0.

Let's look at another example to learn more about the found_rows ():

The values of user_Pwd = xx in the preceding SQL statement are the same. In this case, found_rows () is the intermediate result of the select statement, and the maximum number of rows scanned by the limit statement at this time when the limit statement is executed. It is irrelevant to the number of rows in the original table. It is the limit of the intermediate result of the select statement, and then the final result set is obtained and then returned.

3) applicable scenarios of SQL _CALC_FOUND_ROWS and FOUND_ROWS ()

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 () enables you to determine how many other pages are needed for the rest of the result.

1> SQL _CALC_FOUND_ROW + limit + found_rows () can be used in paging scenarios.

2> if found_rows () without SQL _CALC_FOUND_ROW is not included, you can use the following statement in the stored procedure to determine whether the previous select is null:

DELIMITER //DROP PROCEDURE IF EXISTS loginandreg //CREATE PROCEDURE loginandreg(    OUT userId     BIGINT,    IN user_Pwd                          VARCHAR(32),    IN user_MobileCode                   VARCHAR(16),    IN user_RegIP                        VARCHAR(16))BEGINIF EXISTS(SELECT * FROM Users u WHERE u.user_MobileCode=user_MobileCode) THEN    SELECT u.userId INTO userId FROM Users u WHERE u.user_MobileCode=user_MobileCode AND u.user_Pwd=user_Pwd;        IF FOUND_ROWS() < 1 THEN        SELECT -1 INTO userId;    END IF;ELSE     INSERT INTO Users(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);    SELECT LAST_INSERT_ID() INTO userId;END IF;END //DELIMITER ;

In the above stored procedure:

SELECT u. userId INTO userId FROM Users u WHERE u. user_MobileCode = user_MobileCode AND u. user_Pwd = user_Pwd;

IF FOUND_ROWS () <1 THEN

SELECT-1 INTO userId;

End if;

Is a good example.

In this scenario, you can use mysql's FOUND_ROWS () to replace the IF @ ROWCOUNT <1 statement in the SQL server stored procedure.

Bytes --------------------------------------------------------------------------------------------------------------------------

2. row-count () function

Document address: http://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_row-count

In one sentence, the row_count () function is generally used to return the number of rows actually modified by update, insert, and delete.

In MySQL 5.6, ROW_COUNT () returns a value as follows:

DDL statements: 0. This applies to statements such as create table or drop table.

DML statements other than SELECT: The number of affected rows. this applies to statements such as UPDATE, INSERT, or DELETE (as before), but now also to statements such as alter table and load data infile.

SELECT:-1 if the statement returns a result set, or the number of rows "affected" if it does not. for example, for SELECT * FROM t1, ROW_COUNT () returns-1. for SELECT * FROM t1 into outfile 'File _ name', ROW_COUNT () returns the number of rows written to the file.

SIGNAL statements: 0.

For UPDATE statements, the affected-rows value by default is the number of rows actually changed. if you specify the CLIENT_FOUND_ROWS flag to mysql_real_connect () when connecting to mysqld, the affected-rows value is the number of rows "found"; that is, matched by the WHERE clause.

That is to say, for the update statement, row_count () returns the number of actually modified rows by default. However, you can also return the number of rows (or the number of matched rows, affected rows) through parameter settings ), this setting is compatible with SQL % rowcount in Oracle ps/SQL and @ RowCount in SQL server.

However, the row_count () result is inconsistent with the default behavior of the mysql JDBC driver, and the Connection in mysql jdbc. the getUpdateCount () function returns the number of rows found, rather than the number of actually modified rows. if you want to return the actually modified rows, you need to use the stored procedure. link description:

Http://stackoverflow.com/questions/17544782/how-to-tell-number-of-rows-changed-from-jdbc-execution

Http://mybatis-user.963551.n3.nabble.com/Return-number-of-changed-rows-td3888464.html#a3903155

Http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html (which contains all mysql jdbc link configurable parameters)

UseAffectedRows

Don't set the CLIENT_FOUND_ROWS flag when connecting to the server (not JDBC-compliant, will break most applications that rely on "found" rows. "affected rows" for DML statements), but does cause "correct" update counts from "INSERT... on duplicate key update "statements to be returned by the server.

Default: false

Since version: 5.1.7

Impact on mysql replication:

Important

FOUND_ROWS () is not replicated reliably using statement-based replication. This function is automatically replicated using row-based replication.

Important

ROW_COUNT () is not replicated reliably using statement-based replication. This function is automatically replicated using row-based replication.

Note: found_rows () and row_count () are unreliable in statement-based replication environments. they automatically use row-based replication.