Documentation Home
MySQL 8.0 参考手册  / 第 13 章 SQL 语句  / 13.2 数据操作语句  / 13.2.6 插入语句  /  13.2.5.2 INSERT ... ON DUPLICATE KEY UPDATE 语句

13.2.5.2 INSERT ... ON DUPLICATE KEY UPDATE 语句

如果您指定一个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;

DELAYED使用 时将忽略 该选项ON DUPLICATE KEY UPDATE

对于INSERT ... SELECT语句,这些规则适用于SELECT您可以在ON DUPLICATE KEY UPDATE子句中引用的可接受的查询表达式形式:

  • 对单个表查询中的列的引用,该表可能是派生表。

  • 对多个表的连接查询中的列的引用。

  • 对查询中的列的引用DISTINCT

  • 引用其他表中的列,只要 SELECT不使用 GROUP BY. 一个副作用是您必须限定对非唯一列名的引用。

对列的引用 UNION不能可靠地工作。要解决此限制,请将 重写 UNION为派生表,以便可以将其行视为单表结果集。例如,此语句可能会产生不正确的结果:

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

UNION相反,使用将 重写为派生表 的等效语句 :

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;

将查询重写为派生表的技术还支​​持从GROUP BY 查询中引用列。

因为 INSERT ... SELECT语句的结果取决于行的顺序,SELECT并且不能始终保证此顺序,所以在记录 INSERT ... SELECT ON DUPLICATE KEY UPDATE源和副本的语句时可能会出现分歧。因此, INSERT ... SELECT ON DUPLICATE KEY UPDATE语句被标记为对基于语句的复制不安全。使用基于语句的模式时,此类语句会在错误日志中产生警告,并在使用模式时使用基于行的格式写入二进制日志MIXEDINSERT ... ON DUPLICATE KEY UPDATE针对具有多个唯一键或主键的表的 语句也被标记为不安全。(缺陷 #11765650,缺陷 #58637)

另见第 17.1.2.1 节,“基于语句和基于行的复制的优点和缺点”

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