[Mysql] “INSERT IGNORE”与“INSERT ... ON DUPLICATE KEY UPDATE”


Answers

如果你想看看这一切意味着什么,这里是一切的一击:

CREATE TABLE `users_partners` (
  `uid` int(11) NOT NULL DEFAULT '0',
  `pid` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`uid`,`pid`),
  KEY `partner_user` (`pid`,`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

主键基于此快速参考表的两列。 主键需要唯一值。

让我们开始:

INSERT INTO users_partners (uid,pid) VALUES (1,1);
...1 row(s) affected

INSERT INTO users_partners (uid,pid) VALUES (1,1);
...Error Code : 1062
...Duplicate entry '1-1' for key 'PRIMARY'

INSERT IGNORE INTO users_partners (uid,pid) VALUES (1,1);
...0 row(s) affected

INSERT INTO users_partners (uid,pid) VALUES (1,1) ON DUPLICATE KEY UPDATE uid=uid
...0 row(s) affected

注意,上面通过将列设置为等于自己来节省了太多的额外工作,实际上不需要更新

REPLACE INTO users_partners (uid,pid) VALUES (1,1)
...2 row(s) affected

现在有些多行测试:

INSERT INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...Error Code : 1062
...Duplicate entry '1-1' for key 'PRIMARY'

INSERT IGNORE INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...3 row(s) affected

在控制台中没有生成其他消息,并且它现在在表格数据中具有这4个值。 我删除了除(1,1)之外的所有内容,所以我可以从同一个比赛场地进行测试

INSERT INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4) ON DUPLICATE KEY UPDATE uid=uid
...3 row(s) affected

REPLACE INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...5 row(s) affected

所以你有它。 由于这一切都是在一张几乎没有数据而没有制作的新表上进行的,所以执行的时间是微观的和不相关的。 任何拥有真实世界数据的人都将不胜感激。

Question

在执行有多行的INSERT语句时,我想跳过会导致失败的重复条目。 经过一番研究,我的选择似乎是使用以下两种方法之一:

  • ON DUPLICATE KEY UPDATE ,这意味着一些代价不必要的更新,或者
  • INSERT IGNORE暗示其他类型的失败的邀请未经宣布。

我在这些假设中是否正确? 简单地跳过可能导致重复的行并继续到其他行的最佳方法是什么?




我知道这是旧的,但我会添加此笔记,以防其他人(如我)在试图在INSERT..IGNORE上查找信息时到达此页面。

如上所述,如果使用INSERT..IGNORE,则执行INSERT语句时发生的错误将被视为警告。

没有明确提到的一件事是INSERT..IGNORE将导致插入时将无效值调整为最接近的值(而如果不使用IGNORE关键字,无效值将导致查询中止)。




如果要在表中插入主键或唯一索引的冲突,它将更新有冲突的行,而不是插入该行。

句法 :

插入到table1中设置column1 = a,column2 = b on dulplicate update column2 = c;

现在在这里,这个插入语句可能与您以前看到的不一样。 这个插入语句试图在table1中插入一行,分别将a和b的值插入到column1和column2列中。

让我们深入理解这个说法:

例如: - 这里column1被定义为table1中的主键。

现在,如果在table1中没有列1中具有值“a”的行。 所以这个语句将在table1中插入一行。

现在,如果在table1中有一列在第2列中具有值“a”。 因此,此语句将使用“c”更新行的column2值,其中column1值为“a”。

所以,如果你想插入一个新的行,否则更新该行的主键或唯一索引的冲突。 阅读更多关于此链接




在DUPLICATE KEY UPDATE中并不是真正的标准。 这与REPLACE的标准一致。 请参阅SQL MERGE

基本上这两个命令都是标准命令的替代语法版本。




INSERT IGNORE的潜在危险。 如果您尝试插入VARCHAR值的时间过长,则使用 - 定义列,则该值将被截断并插入,即使已启用严格模式。