"INSERT IGNORE"vs"INSERT… ON重复密钥更新"

Replace进入似乎是一个选择。或者你可以检查

IF NOT EXISTS(QUERY) Then INSERT

这将插入或删除然后插入。我倾向于先进行IF NOT EXISTS检查。

我建议使用INSERT...ON DUPLICATE KEY UPDATE。

如果您使用INSERT IGNORE，那么如果它导致重复键，则实际上不会插入该行。但该语句不会生成错误。它会生成警告。这些情况包括：

• 在具有PRIMARY KEY或UNIQUE约束的列中插入重复键。
• 将NULL插入到具有NOT NULL约束的列中。
• 将行插入分区表，但插入的值不会映射到分区。

如果你使用REPLACE，MySQL实际上在内部执行DELETE后跟INSERT，这有一些意想不到的副作用：

• 分配一个新的自动增量ID。
• 可以删除具有外键的依赖行（如果您使用级联外键）或阻止REPLACE。
• 触发DELETE的触发器被不必要地执行。
• 副作用也会传播到副本。

更正：REPLACE和INSERT...ON DUPLICATE KEY UPDATE都是MySQL特有的非标准专有发明。ANSISQL2003定义了一个MERGE语句，可以解决相同的需求（甚至更多），但MySQL不支持MERGE语句。

一个用户试图编辑这篇文章（编辑被版主拒绝）。编辑试图添加一个声明INSERT...ON DUPLICATE KEY UPDATE会导致分配一个新的自动增量id。新id确实是产生，但它不会在更改的行中使用。

请参阅下面的演示，使用Percona Server 5.5.28进行了测试。配置变量innodb_autoinc_lock_mode=1（默认值）：

mysql> create table foo (id serial primary key, u int, unique key (u));mysql> insert into foo (u) values (10);mysql> select * from foo;+----+------+| id | u    |+----+------+|  1 |   10 |+----+------+
mysql> show create table foo\GCREATE TABLE `foo` (`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,`u` int(11) DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `u` (`u`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
mysql> insert into foo (u) values (10) on duplicate key update u = 20;mysql> select * from foo;+----+------+| id | u    |+----+------+|  1 |   20 |+----+------+
mysql> show create table foo\GCREATE TABLE `foo` (`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,`u` int(11) DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `u` (`u`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

上面演示了IODKU语句检测到重复，并调用更新来更改u的值。请注意，AUTO_INCREMENT=3表示生成了一个id，但未在行中使用。

而REPLACE删除原始行并插入新行，生成和存储新的自动增量id：

mysql> select * from foo;+----+------+| id | u    |+----+------+|  1 |   20 |+----+------+mysql> replace into foo (u) values (20);mysql> select * from foo;+----+------+| id | u    |+----+------+|  3 |   20 |+----+------+

我经常使用INSERT IGNORE，这听起来也正是你想要的那种行为。只要你知道不会插入会导致索引冲突的行，并且你相应地计划你的程序，它应该不会造成任何麻烦。

重复键更新不是标准中的真的。它与替换一样标准。请参阅SQL合并。

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

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

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

需要补充的重要内容：当使用INSERT IGNORE并且您确实存在密钥违规时，MySQL不会发出警告！

例如，如果您尝试一次插入100条记录，其中一条有错误，您将进入交互模式：

Query OK, 99 rows affected (0.04 sec)

Records: 100 Duplicates: 1 Warnings: 0

如您所见：没有警告！这种行为甚至在官方的MySQL文档中被错误地描述了。

如果你的脚本需要被告知，如果一些记录没有被添加（由于键违反），你必须调用mysql_info（）并解析它的“重复”值。

如果你想知道这一切意味着什么，这里有一个打击的一切：

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

所以你有它。由于这一切都是在一个几乎没有数据的新表上执行的，也没有在生产环境中执行，执行的时间是微观的和无关紧要的。任何有真实世界数据的人都非常欢迎贡献出来。

如果使用insert ignore，在查询集的末尾有SHOW WARNINGS;语句，则会显示一个包含所有警告的表，包括哪些ID是重复的。

插入忽略的潜在危险。如果您尝试插入更长的VARCHAR值，则使用-定义列，该值将被截断并插入，即使启用了严格模式。

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

语法：

insert into table1 set column1 = a, column2 = b on duplicate update column2 = c;

现在在这里，这个插入语句可能看起来与您之前看到的不同。这个插入语句试图将表1中值为a和b的行分别插入列列1和列2。

让我们深入理解这句话：

例如：这里的列1被定义为表1中的主键。

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

现在，如果在表1中有一个值为“a”的行在列2中。因此，此语句将用“c”更新行的列2值，其中列1值为“a”。

因此，如果您想插入新行，否则请在主键或唯一索引冲突时更新该行。
阅读更多关于此链接

首选INSERT...ON DUPLICATE KEY UPDATE以防止意外的异常管理。

当您只有**1个唯一约束**时，此解决方案有效

在我的例子中，我知道col1和col2是一个唯一的综合索引。

它跟踪错误，但不会在重复时抛出异常。关于性能，相同值的更新效率为MySQL注意到这一点并且不更新它

INSERT INTO table(col1, col2, col3, col4)VALUES(?, ?, ?, ?)ON DUPLICATE KEY UPDATEcol1 = VALUES(col1),col2 = VALUES(col2)

使用这种方法的想法来自phpdelusions.net/pdo的评论。

如果您在同一语句中使用两者INSERT IGNORE和ON DUPLICATE KEY UPDATE，如果插入找到重复键，更新仍会发生。换句话说，更新优先于忽略。但是，如果ON DUPLICATE KEY UPDATE子句本身导致重复键错误，则该错误将被忽略。

如果您有多个唯一键，或者您的更新试图违反外键约束，就会发生这种情况。

CREATE TABLE test(id BIGINT (20) UNSIGNED AUTO_INCREMENT,str VARCHAR(20),PRIMARY KEY(id),UNIQUE(str));
INSERT INTO test (str) VALUES('A'),('B');
/* duplicate key error caused not by the insert,but by the update: */INSERT INTO test (str) VALUES('B')ON DUPLICATE KEY UPDATE str='A';
/* duplicate key error is suppressed */INSERT IGNORE INTO test (str) VALUES('B')ON DUPLICATE KEY UPDATE str='A';