I understand that there exists INSERT IGNORE
and INSERT ... ON DUPLICATE KEY UPDATE
. However, when there is a duplicate key, I'd like to do a INSERT
to a temporary table to keep a record of the unique key that has been violated, so that I can output it to the user.
Is there any way I can do a ON DUPLICATE INSERT
? If it helps, I'm trying to do a bulk insert.
INSERT ... ON DUPLICATE KEY UPDATE is a MariaDB/MySQL extension to the INSERT statement that, if it finds a duplicate unique or primary key, will instead perform an UPDATE. The row/s affected value is reported as 1 if a row is inserted, and 2 if a row is updated, unless the API's CLIENT_FOUND_ROWS flag is set.
By definition, atomicity requires that each transaction is an all or nothing. So yes it is atomic in the sense that if the data that you are trying to insert will cause a duplicate in the primary key or in the unique index, the statement will instead perform an update and not error out.
ON DUPLICATE KEY UPDATE inserts or updates a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. The ON DUPLICATE KEY UPDATE clause can contain multiple column assignments, separated by commas. The use of VALUES() to refer to the new row and columns is deprecated beginning with MySQL 8.0.
Note − Use the INSERT IGNORE command rather than the INSERT command. If a record doesn't duplicate an existing record, then MySQL inserts it as usual. If the record is a duplicate, then the IGNORE keyword tells MySQL to discard it silently without generating an error.
With ON DUPLICATE KEY UPDATE
, you cannot insert into another table - nor is there an alternative function available.
Two custom/alternative ways you can accomplish this:
Using a stored procedure
as outlined in the accepted answer to this question: MySQL ON DUPLICATE KEY insert into an audit or log table
Creating a trigger
that logged every INSERT
for your table into another table and querying the 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 the duplicates in the table, you could us:
SELECT id FROM insert_logs HAVING COUNT(id) > 1 GROUP BY id;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With