INSERT ... ON DUPLICATE KEY (do nothing)
MysqlSqlUnique KeyMysql Problem Overview
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
?
Mysql Solutions
Solution 1 - Mysql
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 (...)
Solution 2 - Mysql
HOW TO IMPLEMENT 'insert if not exist'?
REPLACE INTO
1. pros:
- simple.
cons:
-
too slow.
-
auto-increment key will CHANGE(increase by 1) if there is entry matches
unique key
orprimary key
, because it deletes the old entry then insert new one.
INSERT IGNORE
2. pros:
- simple.
cons:
-
auto-increment key will not change if there is entry matches
unique key
orprimary key
but auto-increment index will increase by 1 -
some other errors/warnings will be ignored such as data conversion error.
INSERT ... ON DUPLICATE KEY UPDATE
3. pros:
- you can easily implement 'save or update' function with this
cons:
-
looks relatively complex if you just want to insert not update.
-
auto-increment key will not change if there is entry matches
unique key
orprimary key
but auto-increment index will increase by 1
unique key
or primary key
?
4. Any way to stop auto-increment key increasing if there is entry matches 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.
Solution 3 - Mysql
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.