MySQL中INSERT IGNORE与INSERT … ON DUPLICATE KEY UPDATE的对比

技术背景

在执行包含多行数据的INSERT语句时，常常会遇到重复记录的问题，这些重复记录可能会导致插入操作失败。为了避免这种情况，MySQL提供了INSERT IGNORE和INSERT ... ON DUPLICATE KEY UPDATE两种解决方案。

实现步骤

INSERT IGNORE

当使用INSERT IGNORE时，如果插入的数据会导致唯一键冲突（如主键或唯一索引），MySQL不会抛出错误，而是将其作为警告处理，该记录将不会被插入。示例代码如下：

-- 创建测试表
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 IGNORE INTO users_partners (uid,pid) VALUES (1,1);

INSERT … ON DUPLICATE KEY UPDATE

当使用INSERT ... ON DUPLICATE KEY UPDATE时，如果插入的数据导致唯一键冲突，MySQL会执行更新操作。示例代码如下：

-- 插入数据，若冲突则更新
INSERT INTO users_partners (uid,pid) VALUES (1,1) ON DUPLICATE KEY UPDATE uid=uid;

核心代码

INSERT IGNORE

INSERT IGNORE INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

INSERT … ON DUPLICATE KEY UPDATE

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2, ...;

最佳实践

• 使用INSERT … ON DUPLICATE KEY UPDATE：如果需要在遇到重复记录时更新已有记录，或者需要知道哪些记录发生了冲突，建议使用INSERT ... ON DUPLICATE KEY UPDATE。它虽然可能会有一些额外的开销，但可以避免INSERT IGNORE带来的一些潜在问题，如自动递增ID的浪费。
• 使用INSERT IGNORE：如果只关心插入操作的整体成功，不关心个别记录是否因为冲突而未插入，并且不需要处理冲突记录，可以使用INSERT IGNORE。

常见问题

INSERT IGNORE的潜在风险

• 自动递增ID问题：即使插入失败，INSERT IGNORE也可能会导致自动递增ID的值增加。
• 数据截断问题：如果插入的VARCHAR值超过了列定义的长度，即使启用了严格模式，该值也会被截断并插入。
• 无警告提示：当发生键冲突时，MySQL不会抛出警告，需要通过mysql_info()函数解析“Duplicates”值来获取相关信息。

INSERT … ON DUPLICATE KEY UPDATE的问题

• 非标准语法：INSERT ... ON DUPLICATE KEY UPDATE不是标准的SQL语法，是MySQL特有的。
• 自动递增ID生成：虽然新的自动递增ID会被生成，但不会在变更的行中使用。
• 死锁问题：在并发环境下，多个事务以不同顺序更新记录时可能会导致死锁。