MySql - INCLUDE DUPLICATE KEY INSERTS

Question

I understand that there is INSERT IGNORE and INSERT ... ON DUPLICATE KEY UPDATE . However, when there is a duplicate key, I would like to do INSERT in a temporary table in order to keep a record of the unique key that was broken so that I can display it to the user.

Is there any way to do ON DUPLICATE INSERT ? If this helps, I try to do volume insertion.

Answer 1

With ON DUPLICATE KEY UPDATE you cannot insert another table - and there is no alternative function.

Two custom / alternative ways of doing this:

• Using the stored procedure , as indicated in the accepted answer to this question: Including MySQL ON DUPLICATE KEY in the audit or log table

• Create a trigger that logs each INSERT for your table in another table and queries a table full of "logs" for any duplicates.

Something similar to this should work:

 CREATE TABLE insert_logs ( id int not null ); delimiter | CREATE TRIGGER insert_logs_trigger BEFORE INSERT ON your_table FOR EACH ROW BEGIN INSERT INTO insert_logs SET id = NEW.id; END; | 

To get a list of duplicates in a table, you can:

 SELECT id FROM insert_logs HAVING COUNT(id) > 1 GROUP BY id;