MySQL multiple insert when duplicates not allowed.

Question

I have a table with 3 columns. Each has a unique index.

I'd like to do multiple inserts at once (300 records a pop). When a duplicate entry occurs it cancels the insert in its entirety. This means if 1 out of the 300 is a duplicate, none of them will be inserted.

Is there a way around this?

score 8 · Accepted Answer · answered Feb 13 '12 at 18:13

8

Try changing your query from INSERT INTO ... to INSERT IGNORE INTO .... This will cause any errors to become warnings, and your other records should be inserted.

answered Feb 13 '12 at 18:13

kitti

14,663
31
49

score 1 · Answer 2 · answered Feb 13 '12 at 18:11

If your inserts are idempotent, update or replace would help you out.

Given they are likely not, there isn't any super-efficient way to do this without something falling back to inserting individual rows as a fallback - to isolate the problem row.

If you are batching inserts to reduce latency from client to server, consider using a stored procedure to take the rows, and does insert on the server side taking all the data in a shot; that can have a fallback that does appropriate error handling on a row-by-row basis.

That assumes, of course, that there is some meaningful error handling that can be done on the server side without needing synchronous communication to the client.

score 0 · Answer 3 · answered Feb 13 '12 at 18:08

0

Use a transaction: http://dev.mysql.com/doc/refman/5.0/en/commit.html

START TRANSACTION // DO QUERIES COMMIT

If anything goes wrong in DO QUERIES, none of the records will be committed.

If you want to ignore errors. use --force from your mysql command line

answered Feb 13 '12 at 18:08

Levi Hackwith

9,232
18
64
115

I think the OP wants the opposite.... he's doing a multiple insert and the entire insert statement is dying due to a bad record. – kitti Feb 13 '12 at 18:10

score -1 · Answer 4 · answered Feb 13 '12 at 18:12

-1

You can use something like this:

  INSERT INTO table (username, id)
          VALUES
          ('john', 1),
          ('jim', 2),
          ('mary', 3),
          ('jack', 4),
          ('helen', 4) ON DUPLICATE KEY IGNORE

If you want the entry to be replaced , use UPDATE instead of IGNORE.

answered Feb 13 '12 at 18:12

Giannis

813
7
13

1

MySQL doesn't have an `ON DUPLICATE KEY IGNORE`. It only supports `ON DUPLICATE KEY UPDATE`. – kitti Feb 13 '12 at 18:19
There is `INSERT IGNORE` and `INSERT ... ON DUPLICATE KEY UPDATE` see https://stackoverflow.com/questions/2366813/on-duplicate-key-ignore and https://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update for more info – Onema Jan 25 '19 at 23:05

MySQL multiple insert when duplicates not allowed.

4 Answers4