How to know if when using "on duplicate key update" a row was inserted or updated?

Question

We have a database that gets updated everyday at midnight with a cronjob, we get new data from an external XML.

What we do is that we insert all the new content and in case there is a duplicated key we update that field.

INSERT INTO table (id, col1, col2, col3) values (id_value, val1, val2, val3), (id_value, val1, val2, val3), (id_value, val1, val2, val3), (id_value, val1, val2, val3), ON DUPLICATE KEY UPDATE  col1 = VALUES (col1),  col2 = VALUES (col2),  col3 = VALUES (col3); 

What we want to know is which rows have actually been inserted, meaning we want to have a list of the new items. is there any query that might return the new inserts? Basically we will need to get all the new ID's and not the number of new insertions.

Thanks

Answer 1

You can get this information at the time of the insert/update by examining the number of affected rows in the result set.

MySQL documentation states:

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row and 2 if an existing row is updated.

You'll need to combine ROW_COUNT with LAST_INSERT_ID to get your answer and insert one row at a time.

Answer 2

Add an update_count INT NOT NULL DEFAULT 1 column and change your query:

INSERT INTO    table (id, col1, col2, col3) VALUES (id_value, val1, val2, val3), (id_value, val1, val2, val3,), (id_value, val1, val2, val3), (id_value, val1, val2, val3), ON DUPLICATE KEY UPDATE          col1 = VALUES (col1),          col2 = VALUES (col2),          col3 = VALUES (col3),         update_count = update_count + 1; 

You can also increment it in a BEFORE UPDATE trigger which will allow you to keep the query as is.