“插入忽略”vs“重复密钥更新时插入…”

作者

在执行包含多行的INSERT语句时，我希望跳过可能导致失败的重复条目。经过一些研究，我的选择似乎是使用：

  • 重复密钥更新，这意味着以一定代价进行不必要的更新，或
  • INSERT IGNORE意味着邀请其他类型的失败在未通知的情况下溜进

我的这些假设正确吗？跳过可能导致重复的行并继续到其他行的最佳方法是什么

我建议在重复密钥更新时使用INSERT…

如果使用INSERT IGNORE，则如果该行产生重复的键，则实际上不会插入该行。但该语句不会生成错误。它会生成一个警告。这些个案包括：

  • 在具有主键或唯一约束的列中插入重复键
  • 在具有非NULL约束的列中插入NULL
  • 向分区表插入行，但插入的值不映射到分区

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

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

更正：重复密钥更新时的REPLACE和INSERT…都是MySQL特有的非标准专利发明。ANSI SQL 2003定义了一个MERGE语句，可以解决相同的需求（以及更多），但MySQL不支持MERGE语句

用户试图编辑此帖子（编辑被版主拒绝）。编辑试图添加一个声明，INSERT…ON DUPLICATE KEY UPDATE导致分配一个新的自动增量id。的确，新id是生成的，但它没有在更改的行中使用

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

mysql&gt；创建表foo（id序列主键，u int，唯一键（u））；
mysql&gt；插入foo（u）值（10）；
mysql&gt；从foo中选择*；
+----+------+
|id|u|
+----+------+
|  1 |   10 |
+----+------+
mysql&gt；显示创建表foo\G
创建表“foo”(
`id`bigint（20）无符号非空自动增量，
`u`int（11）默认为空，
主键（`id`），
唯一键“u”（“u”）
)ENGINE=InnoDB AUTO_INCREMENT=2默认字符集=1
mysql&gt；在重复密钥更新u=20时将值（10）插入到foo（u）中；
mysql&gt；从foo中选择*；
+----+------+
|id|u|
+----+------+
|  1 |   20 |
+----+------+
mysql&gt；显示创建表foo\G
创建表“foo”(
`id`bigint（20）无符号非空自动增量，
`u`int（11）默认为空，
主键（`id`），
唯一键“u”（“u”）
)ENGINE=InnoDB AUTO_INCREMENT=3默认字符集=1

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

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

mysql&gt；从foo中选择*；
+----+------+
|id|u|
+----+------+
|  1 |   20 |
+----+------+
mysql&gt；替换为foo（u）值（20）；
mysql&gt；从foo中选择*；
+----+------+
|id|u|
+----+------+
|  3 |   20 |
+----+------+

相关文章：

  1. 我应该在MySQL中使用datetime或timestamp数据类型吗？
  2. 如何在MySQL中使用命令行导入SQL文件？
  3. 内部联接、左联接、右联接和完全联接之间有什么区别？[副本]
  4. SQL仅选择列上具有最大值的行[重复]

发表评论

© 2026 开发百科-程序代码问答平台 • Built with