pwas pwas - 9 months ago 57
SQL Question

Number of rows matched and changed not equal

I have a simple update query:

UPDATE mytable SET mycolumn = replace(mycolumn, "somestring", "otherstring")
WHERE mycolumn LIKE '%somestring%';

Sample output:

Query OK, 198 rows affected (24.18 sec)
Rows matched: 200 Changed: 198 Warnings: 0

How it is possible that rows changed count is less than matched? Each matched row should cointain
in column, so it should be replaced.

Answer Source

This was a bug which was reported and is now reverted in MySQL 5.1 onwards.

From the same:

Whether the number of rows updated is different from the number of rows found is engine specific. Some storage engines are able to read just a subset of the columns when doing an update (InnoDB is one such engine). So the server can't reliably check if a row will not be updated. And because of that it will always have number of rows updated equal to the number of rows found. The reason that this worked differently in 5.0 is the fact that in 5.1 the server passes information for the columns it needs to read to InnoDB and hence not all the columns are read anymore. In 5.0 such information was not passed down to the InnoDB storage engine, so it always returned all the columns and a check wether a rows is updated was possible.