MySQL Found_row () and row_count () examples

The MySQL Found_row () and Row_count () functions are used to calculate the number of rows affected by the previous statement, unlike the number of rows found_row used to get a select, and the number of rows Row_count used to get update or delete effects.

There are two functions in MySQL to calculate how many rows were affected by the previous statement, unlike Sqlserver/oracle, and do not cause functional problems because of this difference:

1, judge the number of rows obtained by select with the Found_rows () function.

2, determine the number of rows affected by update or delete with the Row_count () function to judge, it is important to note that if the value before and after the update, Row_count is 0, Unlike the @ @rowcount in SQL Server or rowcount in Oracle, the number of rows affected will be greater than 0 if the update is to a row, regardless of whether the value of the field before and after the update has changed.

Example Description:

Test on MySQL (database version: 5.1.30):

1. Create a database table:

Create Table  int varchar (+), Primary Key  =InnoDB;

2. Insert the test data:

Insert  into T  (id,name,address) Values   (1,'yubowei','weifang' ),  (2,'sam','Qingdao' );

3. Update the test

Update T   Set = ' Weifang ' where = 1   and = ' Yubowei ';

At this point, view the number of rows affected:

Select Row_count (); ==〉 execution result is 0;

4. Test again

Update T Set = ' Beijing ' where = 1  and = ' Yubowei ';

At this point, view the number of rows affected:
Select Row_count (); ==〉 execution result is 1;

From the above test it can be concluded that only when the record is really modified in MySQL, Row_count will record the number of rows affected, otherwise if the record exists but no actual modification
The update is not logged to Row_count.

This is not the same as SQL rowcount in Oracle

Tests on Oracle (Database version: 10G):

1. Create a database table:

Create Table  intvarchar2 (varchar2),  primarykey(Id,name));

2. Insert the test data:

Insert  into T (id,name,address) Values (1,'yubowei','weifang'), (  2,'sam','Qingdao');

3. Update the test

Update T   Set = ' Weifang ' where = 1   and = ' Yubowei ';

At this point, view the number of rows affected:
V_rowcount: = Sql%rowcount; ==〉 execution result is 1;

4. Test again

Update T   Set = ' Beijing ' where = 1   and = ' Yubowei ';

At this point, view the number of rows affected:
V_rowcount: = Sql%rowcount; ==〉 execution result is 1;

From the above test it can be concluded that in Oracle, as long as the updated records exist, the number of rows affected will be accumulated regardless of the actual data being modified.

Note: So far there are no parameters that can be set for Row_count (). If necessary, it can only be achieved by other means.

Original address: http://www.manongjc.com/article/693.html

Related reading:

MySQL solves Row_count () with mysql_affected_rows () to obtain a value of-1 after the square

MySQL row_count use instances in stored procedures

MySQL Row_count gets the number of rows affected by the previous SQL operation

MySQL Found_row () Usage Example-Found_row detailed

MySQL Found_row () and row_count () examples