在MySQL中，ON DUPLICATE KEY UPDATE或INSERT IGNORE可以是可行的解决方案。

一个基于mysql.com的ON DUPLICATE KEY UPDATE更新示例:

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

UPDATE table SET c=c+1 WHERE a=1;

一个基于mysql.com的INSERT IGNORE示例

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Or:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Or:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

值得注意的是，INSERT IGNORE仍然会增加主键，不管语句是否成功，就像普通的INSERT一样。

这将导致主键之间的间隙，可能会使程序员精神不稳定。或者如果您的应用程序设计得很差，并且依赖于完美的增量主键，这可能会成为一个令人头痛的问题。

查看innodb_autoinc_lock_mode = 0(服务器设置，会有轻微的性能损失)，或者先使用SELECT以确保查询不会失败(这也会有性能损失和额外的代码)。

有几个答案，涵盖如何解决这个问题，如果你有一个唯一的索引，你可以检查与ON DUPLICATE KEY或插入忽略。但情况并非总是如此，由于UNIQUE有长度限制(1000字节)，您可能无法更改它。例如，我必须在WordPress中使用元数据(wp_postmeta)。

最后我用两个问题解决了这个问题:

UPDATE wp_postmeta SET meta_value = ? WHERE meta_key = ? AND post_id = ?;
INSERT INTO wp_postmeta (post_id, meta_key, meta_value) SELECT DISTINCT ?, ?, ? FROM wp_postmeta WHERE NOT EXISTS(SELECT * FROM wp_postmeta WHERE meta_key = ? AND post_id = ?);

查询1是一个常规的UPDATE查询，当相关数据集不存在时没有任何影响。查询2是一个依赖于NOT EXISTS的INSERT，即只有当数据集不存在时才执行INSERT。

解决方案:

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) 

解释:

最里面的查询

SELECT * FROM `table` 
      WHERE `value1`='stuff for value1' AND `value2`='stuff for value2' LIMIT 1

用作WHERE NOT exists -条件检测是否已经存在要插入数据的行。在找到这样的一行之后，查询可能会停止，因此LIMIT 1(微优化，可以省略)。

中间查询

SELECT 'stuff for value1', 'stuff for value2' FROM DUAL

表示要插入的值。DUAL指的是一个特殊的单行一列表，默认存在于所有Oracle数据库中(参见https://en.wikipedia.org/wiki/DUAL_table)。在MySQL-Server 5.7.26版本中，当省略FROM DUAL时，我得到了一个有效的查询，但旧版本(如5.5.60)似乎需要FROM信息。通过使用WHERE NOT EXISTS，如果最里面的查询找到匹配的数据，中间查询将返回一个空结果集。

外部查询

INSERT INTO `table` (`value1`, `value2`) 

插入中间查询返回的数据(如果有)。

使用INSERT IGNORE INTO表。

还有INSERT…ON DUPLICATE KEY UPDATE语法，你可以在13.2.6.2 INSERT…打开重复的键更新语句。

根据谷歌的webcache，帖子来自bogdan.org.ua:

18th October 2007 To start: as of the latest MySQL, syntax presented in the title is not possible. But there are several very easy ways to accomplish what is expected using existing functionality. There are 3 possible solutions: using INSERT IGNORE, REPLACE, or INSERT … ON DUPLICATE KEY UPDATE. Imagine we have a table: CREATE TABLE `transcripts` ( `ensembl_transcript_id` varchar(20) NOT NULL, `transcript_chrom_start` int(10) unsigned NOT NULL, `transcript_chrom_end` int(10) unsigned NOT NULL, PRIMARY KEY (`ensembl_transcript_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Now imagine that we have an automatic pipeline importing transcripts meta-data from Ensembl, and that due to various reasons the pipeline might be broken at any step of execution. Thus, we need to ensure two things:

反复执行的管道不会破坏我们的 >数据库

repeated executions will not die due to ‘duplicate > primary key’ errors. Method 1: using REPLACE It’s very simple: REPLACE INTO `transcripts` SET `ensembl_transcript_id` = 'ENSORGT00000000001', `transcript_chrom_start` = 12345, `transcript_chrom_end` = 12678; If the record exists, it will be overwritten; if it does not yet exist, it will be created. However, using this method isn’t efficient for our case: we do not need to overwrite existing records, it’s fine just to skip them. Method 2: using INSERT IGNORE Also very simple: INSERT IGNORE INTO `transcripts` SET `ensembl_transcript_id` = 'ENSORGT00000000001', `transcript_chrom_start` = 12345, `transcript_chrom_end` = 12678; Here, if the ‘ensembl_transcript_id’ is already present in the database, it will be silently skipped (ignored). (To be more precise, here’s a quote from MySQL reference manual: “If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted.”.) If the record doesn’t yet exist, it will be created. This second method has several potential weaknesses, including non-abortion of the query in case any other problem occurs (see the manual). Thus it should be used if previously tested without the IGNORE keyword. Method 3: using INSERT … ON DUPLICATE KEY UPDATE: Third option is to use INSERT … ON DUPLICATE KEY UPDATE syntax, and in the UPDATE part just do nothing do some meaningless (empty) operation, like calculating 0+0 (Geoffray suggests doing the id=id assignment for the MySQL optimization engine to ignore this operation). Advantage of this method is that it only ignores duplicate key events, and still aborts on other errors. As a final notice: this post was inspired by Xaprb. I’d also advise to consult his other post on writing flexible SQL queries.

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

如果该记录存在，它将被覆盖;如果它还不存在，将创建它。