Find out if REPLACE statement has replaced or just inserted in MySQL

Question

Is there a way in MySQL to find out if the last REPLACE statement actually replaced any row or just performed a regular insert? LAST_INSERT_ID() doesn't seem to help, since the autoincrement counter is updated in all cases.

Answer 1

You need to determine the affected rows count. REPLACE doesn't update rows; it inserts or deletes and then inserts.

From the MySQL manual:

The affected-rows count makes it easy to determine whether REPLACE only added a row or whether it also replaced any rows: Check whether the count is 1 (added) or greater (replaced).

Check out the manual page: MySQL REPLACE Syntax

Answer 2

REPLACE returns the count of affected rows. This can be retrieved in SQL by ROW_COUNT().