How do I find the duplicate ID in a table when doing INSERT IGNORE?

Question

I know that when doing a INSERT IGNORE duplicate entries are ignored and errors become warning but here is what I need.

Scenario: We have a table with 2 columns (id, name) where BOTH are UNIQUE and id is PRIMARY KEY.

When you do an INSERT IGNORE on column name it creates the next AUTO_INCREMENT for id (even though it later doesnt exist in your DB, its just skipped) and when you call LAST_INSERT_ID it gives you the that next id. However I need to find the id that caused the query to be ignore. In other words the id of the name that was a duplicate.

Any MySQL/PHP trick is welcomed. :)

Thanks.

Answer 1

I agree with Jacco, either make the primary key a composite or drop the [id]. If [name] is unique what does the [id] auto_increment column add to your design?

Answer 2

I don't think you can extract this information with INSERT IGNORE. Even if the warning text would contain the key value, there are only so many warnings buffered, so you couldn't rely on that.

I would probably try to rewrite the query so that it avoids inserting a duplicate. You can always detect the keys before hand by doing a

SELECT id FROM tab WHERE name in ('...value1...',...,'...valueN...')

Depending on the format of the data you want to insert, and how you are doing the inserts (bulk, or one row at a time) you can do several things to avoid inserting known duplicates.

If on the other hand you had the idea of extracting the IDs of the would-be duplicates to perform an update for those rows instead, then you should use the REPLACE syntax (http://dev.mysql.com/doc/refman/5.1/en/replace.html) or the ON DUPLICATE KEY UPDATE construct (http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html)