Explanation and usage of found_row of and row_count of in MySQL

Reference: mysqlpub com

There are two functions in MySQL to calculate how many lines are affected by the previous statement, different from SqlServer/Oracle. Do not cause functional problems because of this difference:

1. Judge the number of lines obtained by Select using the found_rows() function.

row_count() function is used to judge the number of lines affected by Update or Delete. It should be noted here that if the value before and after Update is 1, row_count is 0, unlike @@rowcount in SqlServer or rowcount in Oracle, as long as update reaches the line, the number of lines affected will be greater than 0, regardless of whether the value of the field before and after update has changed.

Examples:

Testing on MySQL (database version: 5.1.30) :

1. Create database tables:

create table t(
id int,
name varchar(50),
address varchar(100),
primary key(id,name)
)engine =InnoDB;

2. Insert test data:

insert into t
  (id,name,address)
values
  (1,'yubowei','weifang'),
  (2,'sam','qingdao');

3. Update the test

update t
  set address = 'weifang'
where id = 1
  and name = 'yubowei';

View the number of affected rows at this point:
select row_count (); == > execution result is 0;

4. Test again

update t
set address = 'beijing'
where id = 1
and name = 'yubowei';

View the number of affected rows at this point:
select row_count (); == > execution result is 1;
From the above test, we can see that in MySQL, row_count will only record the number of affected rows if the record is actually modified. Otherwise, if the record exists but is not actually modified, row_count will only record the number of affected rows
This update will not be recorded to row_count.

This is different from SQL ROWCOUNT in oracle

Tests on ORACLE (database version: 10G) :

1. Create database tables:

create table t(
id int,
name varchar2(50),
address varchar2(100),
primary key(id,name)
);

2. Insert test data:

insert into t
(id,name,address)
values
(1,'yubowei','weifang'),
(2,'sam','qingdao');

3. Update the test

update t
  set address = 'weifang'
where id = 1
  and name = 'yubowei';

View the number of affected rows at this point:
v_RowCount: = SQL % ROWCOUNT; == > execution result is 1;

4. Test again

update t
  set address = 'beijing'
where id = 1
  and name = 'yubowei';

View the number of affected rows at this point:
v_RowCount: = SQL % ROWCOUNT; == > execution result is 1;

From the above test, it can be concluded that in ORACLE, as long as the updated record exists, the number of affected rows will be accumulated regardless of whether the data is actually modified or not.

Note: so far no parameters can be set for row_count(). If necessary, it can only be done by other means.