A small example of ROW_COUNT () in mysql

Note: in mysql, ROW_COUNT () can return the number of rows affected by the previous SQL UPDATE, DELETE, and INSERT operations.

MySQL test (database version: 5.1.22 ):

1. Create a database table:
Copy codeThe Code is as follows:
Create table t (
Id int,
Name varchar (50 ),
Address varchar (100 ),
Primary key (id, name)
) Engine = InnoDB;

2. Insert test data:
Copy codeThe Code is as follows:
Insert into t
(Id, name, address)
Values
(1, 'yubowei', 'weifang '),
(2, 'Sam ', 'qingdao ');

3. Update:
Copy codeThe Code is as follows:
Update t set address = 'weifang'
Where id = 1 and name = 'yubowei ';

Check the number of affected rows:
Select row_count (); => the execution result is 0;

4. Update again:
Copy codeThe Code is as follows:
Update t set address = 'beijing'
Where id = 1 and name = 'yubowei ';

Check the number of affected rows:
Select row_count (); => the execution result is 1;
From the test above, we can conclude that row_count records the number of affected rows only when the record is modified in MySQL, otherwise, if the record exists but is not modified, the update record will not be added to row_count.

Note:
After processing the UPDATE statement dynamically with PREPARE today, we found that the ROW_COUNT () function always returns-1. After checking that row_count () was originally placed behind the deallocate statement.
I made such a mistake.