Want to know whether an 'UPSERT' operation performed INSERT or UPDATE

I have a query where I try to insert a record and ready to update in case the record exists.

The question is that I would like to know, once the query is executed, if the operation has been an update or an insert.

Any idea?

According to MySQL documentation:


With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify the CLIENT_FOUND_ROWS flag to mysql_real_connect() when connecting to mysqld, the affected-rows value is 1 (not 0) if an existing row is set to its current values.

So, if your statement would affect just one row, if the number of affected rows is 1, then it is an INSERT; otherwise, an UPDATE. To retrieve the number of affected rows using PDO, use rowCount method.

StackExchange Database Administrators Q#161097, answer score: 6