How to get affected rows in previous MySQL operation?

Question

mysql_affected_rows is to get number of affected rows in previous MySQL operation, but I want to get affected rows in previous MySQL operation. For example:

update mytable set status=2 where column3="a_variable";

Before this operation, status of some rows is already 2, and I want to get affected rows in previous MySQL operation, you can not get it by issuing a query of

select * from mytable where status=2

So how to do this work?

Answer 1

It can be efficiently and simply achieved with the following:

 select * from mytable where column3="a_variable" and status != 2;
 update mytable set status=2 where column3="a_variable";

The result of the first query are the rows that are going to change, and the second query actually changes them.

If this is a high performance system, you may need to take special care with transactions to prevent a new row slipping in between those 2 queries.