MySQL multiple insertion when duplicates are not allowed.

Question

I have a table with three columns. Each of them has a unique index.

I would like to make several attachments at once (300 pop entries). When re-recording occurs, it completely cancels the insert. This means that if 1 out of 300 is a duplicate, none of them will be inserted.

Is there any way around this?

Answer 1

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

Answer 2

If your insertions are idempotent, update or replace will help you.

Given that they are most likely not, there is no super-efficient way to do this without returning to inserting individual lines as a backup - to isolate the problem line.

If you are inserting inserts to reduce the latency from the client to the server, consider using a stored procedure to take the rows and insert into the server side it takes all the data in the frame; which may have a backup that performs appropriate error handling line by line.

This assumes, of course, that there is significant error handling that can be performed on the server side without the need for synchronous communication with the client.

Answer 3

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

START TRANSACTION // DO QUERIES COMMIT

If something goes wrong in DO QUERIES, none of the entries will be committed.

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

Answer 4

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 record to be replaced, use UPDATE instead of IGNORE.