MySql - ON DUPLICATE KEY INSERT

Question

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.

Answer 1

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;

Answer 2

Maybe this could be helpful:

$time = time();
$countme = 1;
while ($countme > 0) {
    $stmt = $mysqli->prepare("INSERT INTO loads (dte,filecode,id,acct,nmbr) VALUES (?,?,?,?,?) ON DUPLICATE KEY UPDATE dte = dte");
    $stmt->bind_param("sssss", $time, $filecode, $id, $acct, $number);
    $stmt->execute();
    $countme++;
    if ($stmt->affected_rows === 1) {
        $countme = 0; // all is good
    } else {
        $time = $time + 1;
    }
    if ($countme === 4) {
        exit;
    }
}