如果您指定一个ON DUPLICATE KEY UPDATE 子句并且要插入的行将导致UNIQUE索引或中出现重复值，则会出现旧行PRIMARY KEY的 an 。UPDATE例如，如果 columna被声明为UNIQUE并且包含 value 1，则以下两个语句具有类似的效果：

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

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

效果并不完全相同：对于一个 包含自增列的InnoDB表，语句增加自增值但 不增加。 aINSERTUPDATE

如果 columnb也是唯一的， INSERT则等效于此 UPDATE语句：

UPDATE t1 SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

如果a=1 OR b=2匹配多行，则只 更新一行。通常，您应该尽量避免ON DUPLICATE KEY UPDATE 在具有多个唯一索引的表上使用子句。

对于ON DUPLICATE KEY UPDATE，如果该行作为新行插入，则每行的受影响行值为 1，如果更新现有行，则为 2，如果现有行设置为其当前值，则为 0。如果 在连接到mysqldCLIENT_FOUND_ROWS时将标志指定给 mysql_real_connect()C API 函数，并且将现有行设置为其当前值，则受影响的行值为 1（而不是 0）。

如果表包含一AUTO_INCREMENT列并INSERT ... ON DUPLICATE KEY UPDATE插入或更新一行，该LAST_INSERT_ID()函数将返回该AUTO_INCREMENT值。

该ON DUPLICATE KEY UPDATE子句可以包含多个列分配，以逗号分隔。

在子句中的赋值表达式中ON DUPLICATE KEY UPDATE，您可以使用 函数从语句部分 引用列值 。换句话说， 在子句中指的是将被插入的值，没有重复键冲突发生。此功能在多行插入中特别有用。该 函数仅在子句或 语句 中有意义，否则返回。例子： VALUES(col_name)INSERTINSERT ... ON DUPLICATE KEY UPDATEVALUES(col_name)ON DUPLICATE KEY UPDATEcol_nameVALUES()ON DUPLICATE KEY UPDATEINSERTNULL

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

该语句与以下两个语句相同：

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=3;
INSERT INTO t1 (a,b,c) VALUES (4,5,6)
  ON DUPLICATE KEY UPDATE c=9;

For INSERT ... SELECT statements, these rules apply regarding acceptable forms of SELECT query expressions that you can refer to in an ON DUPLICATE KEY UPDATE clause:

References to columns from a UNION do not work reliably. To work around this restriction, rewrite the UNION as a derived table so that its rows can be treated as a single-table result set. For example, this statement can produce incorrect results:

INSERT INTO t1 (a, b)
  SELECT c, d FROM t2
  UNION
  SELECT e, f FROM t3
ON DUPLICATE KEY UPDATE b = b + c;

Instead, use an equivalent statement that rewrites the UNION as a derived table:

INSERT INTO t1 (a, b)
SELECT * FROM
  (SELECT c, d FROM t2
   UNION
   SELECT e, f FROM t3) AS dt
ON DUPLICATE KEY UPDATE b = b + c;

The technique of rewriting a query as a derived table also enables references to columns from GROUP BY queries.

Because the results of INSERT ... SELECT statements depend on the ordering of rows from the SELECT and this order cannot always be guaranteed, it is possible when logging INSERT ... SELECT ON DUPLICATE KEY UPDATE statements for the source and the replica to diverge. Thus, INSERT ... SELECT ON DUPLICATE KEY UPDATE statements are flagged as unsafe for statement-based replication. Such statements produce a warning in the error log when using statement-based mode and are written to the binary log using the row-based format when using MIXED mode. An INSERT ... ON DUPLICATE KEY UPDATE statement against a table having more than one unique or primary key is also marked as unsafe. (Bug #11765650, Bug #58637)

另见第 16.2.1.1 节，“基于语句和基于行的复制的优点和缺点”。

使用表级锁INSERT ... ON DUPLICATE KEY UPDATE等存储引擎的分区表 会锁定更新分区键列的表的任何分区。MyISAM（这不会发生在使用存储引擎的表中，例如InnoDB使用行级锁定的表。）有关更多信息，请参阅 第 22.6.4 节，“分区和锁定”。