Working with Metadata - MySQL - page 337

10.1. Determining the Number of Rows Affected by a
Statement
Problem
You want to know how many rows an SQL statement changed.
Solution
Sometimes the row count is the return value of the function that executes the statement.
Other times the count is returned by a separate function that you call after executing
the statement.
Discussion
For statements that affect rows ( UPDATE , DELETE , INSERT , REPLACE ), each API provides
a way to determine the number of rows involved. For MySQL, the default meaning of
“affected by” is “changed by,” not “matched by.” That is, rows not changed by a statement
are not counted, even if they match the conditions specified in the statement. For ex‐
ample, the following UPDATE statement results in an “affected by” value of zero because
it changes no columns from their current values, no matter how many rows the WHERE
clause matches:
UPDATE profile SET cats = 0 WHERE cats = 0 ;
The MySQL server permits a client to set a connect-time flag to indicate that it wants
rows-matched counts, not rows-changed counts. In this case, the row count for the
preceding statement would be equal to the number of rows with an arms value of 0, even
though the statement results in no net change to the table. However, not all MySQL
APIs expose this flag. The following discussion indicates which APIs enable you to select
the type of count you want and which use the rows-matched count by default rather
than the rows-changed count.
Perl
In Perl DBI scripts, do() returns the row count for statements that modify rows:
my $count = $dbh -> do ( $stmt );
# report 0 rows if an error occurred
printf "Number of rows affected: %d\n" , ( defined ( $count ) ? $count : 0 );
If you prepare a statement first and then execute it, execute() returns the row count:
my $sth = $dbh -> prepare ( $stmt );
my $count = $sth -> execute ();
printf "Number of rows affected: %d\n" , ( defined ( $count ) ? $count : 0 );