MySQL ROW_COUNT() bigger than expected

von Torsten Ziegler

A lot of people try to use ROW_COUNT for SELECT statements, this is not what I am talking about here.

I debugged an application that was using Doctrine::DBAL via PHP::PDO to talk to a MySQL 5.7 Server.
The problem was, that the return value of an DBAL executeUpdate function that was inserting one (1) new dataset returned two (2) as the number of affected rows. I expected it to return 1.

After some hours I came to the point to view the obvious:
It was a joined update using the synthax:

UPDATE TableOne Join TableTwo ON TableOne.id = TableTwo.id Set TableOne.value = 1, TableTwo.value=2 WHERE TableOne.id = ?

So this affects zero to two rows, depending on the initial values.
If both values change ROW_COUNT() and the PDO::execute function will return 2, if just one value changes it will return 1 and if both values have been the same as set by the update it will report 0 affected rows.

While this seems obvious I could not find any information about this using the search engines.
Thats why I am noting it here, and hope it helps someone.

Zurück

Einen Kommentar schreiben

Bitte addieren Sie 9 und 4.