MySQL中实现“不存在则插入”的方法

技术背景

在MySQL数据库操作中，当需要向已有大量数据（如拥有约1400万条记录的表）的表中添加新数据时，为避免数据重复，需要实现“不存在则插入”的功能。传统的使用一对查询（一个查询检查，一个查询插入）的方式效率较低，因此需要探索更高效的方法。

实现步骤

1. 使用INSERT IGNORE INTO

这种方法会忽略插入过程中出现的错误，若插入的记录已存在（违反唯一约束），则会被默默跳过。
示例代码如下：

INSERT IGNORE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;

该方法的优点是简单直接，缺点是如果出现其他问题（如非唯一约束相关的错误），查询也不会中止，可能会掩盖其他问题。因此，建议先在不使用IGNORE关键字的情况下进行测试。

2. 使用REPLACE INTO

若记录已存在，它会先删除原记录，再插入新记录；若记录不存在，则直接插入。
示例代码如下：

REPLACE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;

不过，此方法效率不高，因为它涉及删除和插入操作，且可能会触发相关的删除触发器，导致其他约束对象被删除。

3. 使用INSERT … ON DUPLICATE KEY UPDATE

当插入的记录违反唯一约束时，执行UPDATE操作；若不违反，则正常插入。
示例代码如下：

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

该方法的优势在于只忽略重复键事件，遇到其他错误时仍会中止查询。

4. 使用SELECT结合WHERE NOT EXISTS

通过子查询判断要插入的数据是否已存在，若不存在则插入。
示例代码如下：

INSERT INTO `table` (`value1`, `value2`) 
SELECT 'stuff for value1', 'stuff for value2' FROM DUAL 
WHERE NOT EXISTS (SELECT * FROM `table` 
      WHERE `value1`='stuff for value1' AND `value2`='stuff for value2' LIMIT 1) 

这种方法适用于表中没有唯一键的情况。

核心代码

以下是使用INSERT … ON DUPLICATE KEY UPDATE实现“不存在则插入，存在则更新”的示例代码：

INSERT INTO products (product_name, price) VALUES ('iPhone 15', 999)
ON DUPLICATE KEY UPDATE price = 999;

若products表中存在product_name为iPhone 15的记录，则更新其price为999；若不存在，则插入该记录。

最佳实践

• 若表中有唯一键，优先考虑使用INSERT … ON DUPLICATE KEY UPDATE，它能在处理重复记录时避免不必要的删除和插入操作，同时能正确处理其他错误。
• 若仅需忽略重复记录，不关心其他错误，可使用INSERT IGNORE INTO。
• 若表中无唯一键，可使用SELECT结合WHERE NOT EXISTS的方法。

常见问题

1. INSERT IGNORE INTO会导致主键出现间隙吗？

会。INSERT IGNORE INTO无论插入是否成功，都会像普通INSERT一样增加主键值，可能导致主键出现间隙。若应用程序依赖完美递增的主键，这可能会带来问题。可考虑设置innodb_autoinc_lock_mode = 0（会有轻微性能损失），或先使用SELECT检查再插入（会有性能损失和额外代码）。

2. REPLACE INTO有什么副作用？

REPLACE INTO会先删除原记录再插入新记录，可能会触发删除触发器，导致其他约束对象被删除。并且，根据MySQL手册，只有当表有主键或唯一索引时，REPLACE才有意义，否则它等同于INSERT。

3. INSERT … ON DUPLICATE KEY UPDATE会影响自增列吗？

当插入失败（因重复键）而执行UPDATE操作时，INSERT … ON DUPLICATE KEY UPDATE会使自增列递增，因为它并非真正的插入失败，而是进行了更新操作。