INSERT ... ON DUPLICATE KEY (do nothing)

Question

I have a table with a unique key for two columns:

CREATE  TABLE `xpo`.`user_permanent_gift` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`fb_user_id` INT UNSIGNED NOT NULL ,
`gift_id` INT UNSIGNED NOT NULL ,
`purchase_timestamp` TIMESTAMP NULL DEFAULT now() ,
PRIMARY KEY (`id`) ,
UNIQUE INDEX `user_gift_UNIQUE` (`fb_user_id` ASC, `gift_id` ASC) );

I want to insert a row into that table, but if the key exists, to do nothing! I don't want an error to be generated because the keys exist.

I know that there is the following syntax:

INSERT ... ON DUPLICATE KEY UPDATE ...

but is there something like:

INSERT ... ON DUPLICATE KEY DO NOTHING 

?

Burchette

Answer 1

Yes, use INSERT ... ON DUPLICATE KEY UPDATE id=id (it won't trigger row update even though id is assigned to itself).

If you don't care about errors (conversion errors, foreign key errors) and autoincrement field exhaustion (it's incremented even if the row is not inserted due to duplicate key), then use INSERT IGNORE like this:

INSERT IGNORE INTO <table_name> (...) VALUES (...)

Antoineantoinetta

just to add IGNORE after the INSERT, rest of syntax is the same ? – Burchette

@ufk: INSERT IGNORE without the ON DUPLICATE KEY part, e.g. INSERT IGNORE INTO xpo.user_permanent_gift (...) VALUES (...) – Madame

Note that INSERT IGNORE also ignores other errors such as data conversion failures. – Sabrinasabsay

so i'll use ON DUPLICATE KEY UPDATE id=id. i want only to ignore key duplicates not any other kind of error. – Burchette

One technique you could use would be to try inserting it first, and then if an error occurs, log the error and do a second INSERT IGNORE query. – Inseparable

Which one is faster, INSERT IGNORE or ON DUPLICATE KEY UPDATE id=id? – Elam

@Elam Likely INSERT IGNORE (as it requires no update to the row), but you'd want to benchmark. – Antoineantoinetta

It was a simple benchmarking but based on this source, ON DUPLICATE KEY UPDATE id=id is faster. – Perimorph

My problem (in perl land with DBH) is that UPDATE id=id looks like an affected row, so I can't tell the difference between a genuine insert and a safely avoided collision. Anyone got a notion on how to count only the new rows? – Outfight

@JoshuaEricTurcotte dev.mysql.com/doc/refman/5.7/en/mysql-affected-rows.html – Antoineantoinetta

id=id reports no rows affected (mysql 5.6), so it seem it is being handled intelligently – Farrar

beware INSERT IGNORE will increment the auto-increment column even if the row is not inserted – Nye

Can also confirm INSERT IGNORE in some situations will increment the AUTO_INCREMENT value even though the ID was never actually used due to the duplicate and the IGNORE. – Orography

Preferably don't use INSERT IGNORE, because (a) it still generates and logs the errors messages, simply downgrading them to warnings, and (b) it affects more than just unique/primary key clashes. It's semantically more like an error-suppression clause than what you actually want, which is "INSERT if no matching row already exists". Shame that there's no actual "ON DUPLICATE KEY DO NOTHING" syntax. – Fontana

@Fontana ON DUPLICATE KEY 1=1 works fine, I believe. – Antoineantoinetta

@Antoineantoinetta what you just wrote gives a syntax error: First, you left out the word UPDATE, and secondly it expects one or more assignments to table columns, and 1=1 isn't a valid assignment. But yes, nitpicking aside, what I was trying to say was "Preferably use the 2nd option from this answer rather than the first one". Despite the awkward and unintuitive syntax, the ON DUPLICATE KEY UPDATE column=column method is the only semantically correct way to achieve the desired result. – Fontana

INSERT IGNORE is OK for when you're entering data directly into the database yourself (on a command line or similar), or in other situations when rough-and-ready data insertion with minimum fuss is all you need. But as part of a DB-based application, it's very, very rarely what you really want: you should think carefully before using it instead of the alternative(s). (Note also the REPLACE INTO command, which has a slightly different but related functionality). – Fontana

If id is a string, and your collation is case-insensitive, I think this has potential of changing the case when there's a duplicate. – Earthaearthborn

@Earthaearthborn No, it doesn't work that way: The UPDATE part of the statement applies to the existing row only, so column=column sets column to its existing value, i.e. no change. If you wanted to update it to the new value (allowing e.g., a change of case for an id column), then you'd use either column=VALUES(column), or since MySQL v8.0.19 column=new.column where "new" is an alias you've given your to-be-inserted row(s). See dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html. – Fontana

This answer seems to imply that INSERT IGNORE causes autoincrement while ON DUPLICATE KEY does not. They both cause autoincrement. – Acidosis

I've been trying to figure out my auto-increment problem forever. Finally fixed it, thanks for the tip – Twit

Please keep in mind that ON DUPLICATE KEY UPDATE id=id will be appear in logical replication and will send the affected (but not modified) records to debezium, if installed. On the other hand, this is a simple trick to send records to debezium on purpose without modifying them. – Sail

Answer 2

HOW TO IMPLEMENT 'insert if not exist'?

1. REPLACE INTO

pros:

1. simple.

cons:

1. too slow.

2. auto-increment key will CHANGE(increase by 1) if there is entry matches unique key or primary key, because it deletes the old entry then insert new one.

2. INSERT IGNORE

pros:

1. simple.

cons:

1. auto-increment key will not change if there is entry matches unique key or primary key but auto-increment index will increase by 1

2. some other errors/warnings will be ignored such as data conversion error.

3. INSERT ... ON DUPLICATE KEY UPDATE

pros:

1. you can easily implement 'save or update' function with this

cons:

1. looks relatively complex if you just want to insert not update.

2. auto-increment key will not change if there is entry matches unique key or primary key but auto-increment index will increase by 1

4. Any way to stop auto-increment key increasing if there is entry matches unique key or primary key?

As mentioned in the comment below by @toien: "auto-increment column will be effected depends on innodb_autoinc_lock_mode config after version 5.1" if you are using innodb as your engine, but this also effects concurrency, so it needs to be well considered before used. So far I'm not seeing any better solution.

Electrotechnology

auto-increment column will be effected depends on innodb_autoinc_lock_mode config after version 5.1 – Nuisance

Answer 3

Use ON DUPLICATE KEY UPDATE ...,
Negative : because the UPDATE uses resources for the second action.

Use INSERT IGNORE ...,
Negative : MySQL will not show any errors if something goes wrong, so you cannot handle the errors. Use it only if you don’t care about the query.

Coomer

@abdul is there mysql support what you explained here ? – Seymour