13.2.5.2 INSERT ... ON DUPLICATE KEY UPDATE 语句

如果指定ON DUPLICATE KEY UPDATE子句，而要插入的行将导致UNIQUE索引或PRIMARY KEY中的值重复，则会出现旧行的UPDATE。例如，如果列a声明为UNIQUE并包含值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;

(对于InnoDBtable(其中a是自动递增列)，效果并不相同。对于自动递增列，INSERT语句会增加自动递增值，但UPDATE不会。)

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

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

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

使用ON DUPLICATE KEY UPDATE，如果将行作为新行插入，则每行的受影响行值为 1；如果更新了现有行，则为 2；如果将现有行设置为其当前值，则为 0.如果在连接到mysqld时为mysql_real_connect() C API 函数指定CLIENT_FOUND_ROWS标志，则在将现有行设置为其当前值的情况下，受影响的行值为 1(而不是 0)。

如果 table 包含AUTO_INCREMENT列并且插入...在重复的密钥更新上插入或更新了行，则LAST_INSERT_ID()函数将返回AUTO_INCREMENT值。

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

在ON DUPLICATE KEY UPDATE子句中的赋值 table 达式中，可以使用VALUES(col_name)函数从插入...在重复的密钥更新上语句的INSERT部分引用列值。换句话说，ON DUPLICATE KEY UPDATE子句中的VALUES(col_name)指的是* col_name *的值，如果没有重复键冲突发生，它将被插入。此功能在多行插入中特别有用。 VALUES()函数仅在ON DUPLICATE KEY UPDATE子句或INSERT语句中有意义，否则返回NULL。例：

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;

对于插入...选择语句，这些规则适用于ON DUPLICATE KEY UPDATE子句中可以引用的SELECT查询 table 达式的可接受形式：

• 对单个 table(可能是派生 table)上查询的列的引用。

• 对多个 table 上的联接的查询中的列的引用。

• 对DISTINCT个查询中的列的引用。

• 只要SELECT不使用GROUP BY，就引用其他 table 中的列。副作用是您必须限定对非唯一列名称的引用。

对UNION列的引用无法可靠运行。要变通解决此限制，请将UNION重写为派生 table，以便可以将其行视为单 table 结果集。例如，以下语句可能产生错误的结果：

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

而是使用等效的语句将UNION重写为派生 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;

将查询重写为派生 table 的技术还可以引用GROUP BY查询中的列。

因为插入...选择语句的结果取决于SELECT中的行的 Sequences，并且不能始终保证该 Sequences，所以在记录插入...选择重复的密钥更新语句时可能使主服务器和从服务器发散。因此，对于基于语句的复制，插入...选择重复的密钥更新语句被标记为不安全。当使用基于语句的模式时，此类语句在错误日志中产生警告，而在使用MIXED模式时，则使用基于行的格式将其写入二进制日志。针对具有多个唯一或主键的 table 的插入...在重复的密钥更新上语句也被标记为不安全。 (缺陷#11765650，错误#58637)

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

使用诸如 tableMyISAM之类的存储引擎的分区 table 上的INSERT ... ON DUPLICATE KEY UPDATE使用 table 级锁来锁定其中分区键列已更新的 table 的任何分区。 (对于使用诸如InnoDB之类的存储引擎并采用行级锁定的 table，不会发生这种情况。)有关更多信息，请参见第 22.6.4 节“分区和锁定”。