Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySql - ON DUPLICATE KEY INSERT

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.

like image 887
matt Avatar asked Aug 13 '12 18:08

matt


People also ask

What is insert on duplicate key update?

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.

Is insert on duplicate key update Atomic?

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.

How do I duplicate a key in MySQL?

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.

How do I avoid insert duplicate records in MySQL?

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.


1 Answers

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:

  1. Using a stored procedure as outlined in the accepted answer to this question: MySQL ON DUPLICATE KEY insert into an audit or log table

  2. 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;
like image 156
newfurniturey Avatar answered Oct 07 '22 01:10

newfurniturey