INSERT ... ON DUPLICATE KEY(什么都不做)
我有一个包含两列唯一键的表:
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.
我知道有以下语法:
INSERT ... ON DUPLICATE KEY UPDATE ...
但是有没有类似的东西:
but is there something like:
INSERT ... ON DUPLICATE KEY DO NOTHING
?
推荐答案
Yes, use INSERT ... ON DUPLICATE KEY UPDATE id=id (它不会触发行更新即使 id 分配给自己).
Yes, use INSERT ... ON DUPLICATE KEY UPDATE id=id (it won't trigger row update even though id is assigned to itself).
如果你不关心错误(转换错误、外键错误)和自动递增字段耗尽(即使由于重复键没有插入行也会递增)，那么使用INSERT IGNORE像这样:
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 (...)
相关文章