MySQL Cookbook, 2nd Edition

Obtaining the Number of Rows Affected by a Statement

Problem

You want to know how many rows were changed by an SQL statement.

Solution

Sometimes the row count is the return value of the function that issues the statement. Other times the count is returned by a separate function that you call after issuing 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 that are not changed by a statement are not counted, even if they match the conditions specified in the statement. For example, the following UPDATE statement results in an “affected by” value of zero because it does not change any columns from their current values, no matter how many rows the WHERE clause matches:

UPDATE limbs SET arms = 0 WHERE arms = 0;

The MySQL server allows a client to set a flag when it connects 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, the row count for statements ...