How can I check if the values were changed after an update?

Question

Assuming I do something like the following:

my $rows = $dbh->do("UPDATE MYTABLE SET amount=1200 WHERE id =123"); 

The $rows returns 1 even with the amount is already 1200. So it is considered an updated row.
My question is: Is there a way to check if an update actually changed the values in a row besides doing a query before the update?

Answer 1

Change the SQL query to:

UPDATE MYTABLE SET amount=1200 WHERE id = 123 AND amount <> 1200

The table will be identical, but it returns the number of rows that actually changed.

Answer 2

By default, DBD::mysql returns the number of rows matched in an UPDATE, not the number of rows physically changed. You can change this behavior by disabling mysql_client_found_rows in your call to connect:

my $dsn = "DBI:mysql:;mysql_client_found_rows=0";
my $dbh = DBI->connect($dsn, $user, $password);