If you specify an ON DUPLICATE KEY UPDATE clause and a row to be inserted would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row occurs. 如果指定ON DUPLICATE KEY UPDATE子句，并且要插入的行将导致唯一索引或主键中的值重复，则会更新旧行。For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have similar effect:例如，如果列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;

The effects are not quite identical: For an InnoDB table where a is an auto-increment column, the INSERT statement increases the auto-increment value but the UPDATE does not.其效果并不完全相同：对于一个InnoDB表，其中a是一个自动增量列，INSERT语句会增加自动增量值，但UPDATE不会。

If column b is also unique, the INSERT is equivalent to this UPDATE statement instead:如果b列也是唯一的，则INSERT相当于此UPDATE语句：

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

If a=1 OR b=2 matches several rows, only one row is updated. 如果a=1 OR b=2匹配多行，则只更新一行。In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.通常，应该尽量避免在具有多个唯一索引的表上使用ON DUPLICATE KEY UPDATE子句。

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. 使用ON DUPLICATE KEY UPDATE，如果行作为新行插入，则每行受影响的行值为1；如果更新现有行，则每行受影响的行值为2；如果将现有行设置为其当前值，则每行受影响的行值为0。If you specify the CLIENT_FOUND_ROWS flag to the mysql_real_connect() C API function when connecting to mysqld, the affected-rows value is 1 (not 0) if an existing row is set to its current values.如果在连接mysqld时为C API函数mysql_real_connect()指定CLIENT_FOUND_ROWS标志，则如果现有行设置为其当前值，则受影响的行值为1（而不是0）。

If a table contains an AUTO_INCREMENT column and INSERT ... ON DUPLICATE KEY UPDATE inserts or updates a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value.如果表包含AUTO_INCREMENT列，并且INSERT ... ON DUPLICATE KEY UPDATE插入或更新了一行，则LAST_INSERT_ID()函数返回AUTO_INCREMENT值。

The ON DUPLICATE KEY UPDATE clause can contain multiple column assignments, separated by commas.ON DUPLICATE KEY UPDATE子句可以包含多个列赋值，用逗号分隔。

In assignment value expressions in the ON DUPLICATE KEY UPDATE clause, you can use the VALUES(col_name) function to refer to column values from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE statement. 在ON DUPLICATE KEY UPDATE子句中的赋值表达式中，可以使用VALUES(col_name)函数引用INSERT ... ON DUPLICATE KEY UPDATE语句中INSERT部分的列值。In other words, VALUES(col_name) in the ON DUPLICATE KEY UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred. 换句话说，ON DUPLICATE KEY UPDATE子句中的VALUES(col_name)指的是在没有出现重复键冲突的情况下将插入的col_name的值。This function is especially useful in multiple-row inserts. 此函数在多行插入中特别有用。The VALUES() function is meaningful only in the ON DUPLICATE KEY UPDATE clause or INSERT statements and returns NULL otherwise. VALUES()函数仅在ON DUPLICATE KEY UPDATE子句或INSERT语句中有意义，否则返回NULL。Example:例如：

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

That statement is identical to the following two statements:该语句与以下两个语句相同：

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;

Beginning with MySQL 8.0.19, it is possible to use an alias for the row, with, optionally, one or more of its columns to be inserted, following the VALUES or SET clause, and preceded by the AS keyword. 从MySQL 8.0.19开始，可以为行使用别名，可以选择插入一个或多个列，后跟VALUES或SET子句，前面加AS关键字。Using the row alias new, the statement shown previously using VALUES() to access the new column values can be written in the form shown here:使用行别名new，可以将前面显示的使用VALUES()访问新列值的语句写成如下所示的形式：

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

If, in addition, you use the column aliases m, n, and p, you can omit the row alias in the assignment clause and write the same statement like this:此外，如果使用列别名m、n和p，则可以省略赋值子句中的行别名，并编写如下语句：

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

When using column aliases in this fashion, you must still use a row alias following the VALUES clause, even if you do not make direct use of it in the assignment clause.以这种方式使用列别名时，必须在VALUES子句后面使用行别名，即使在赋值子句中没有直接使用它。

Beginning with MySQL 8.0.20, an INSERT ... SELECT ... ON DUPLICATE KEY UPDATE statement that uses VALUES() in the UPDATE clause, like this one, throws a warning:从MySQL8.0.20开始，在UPDATE子句中使用VALUES()的INSERT ... SELECT ... ON DUPLICATE KEY UPDATE语句，如下所示，会抛出警告：

INSERT INTO t1
  SELECT c, c+d FROM t2
  ON DUPLICATE KEY UPDATE b = VALUES(b);

You can eliminate such warnings by using a subquery instead, like this:您可以使用子查询来消除此类警告，如下所示：

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

You can also use row and column aliases with a SET clause, as mentioned previously. 如前所述，还可以在SET子句中使用行别名和列别名。Employing SET instead of VALUES in the two INSERT ... ON DUPLICATE KEY UPDATE statements just shown can be done as shown here:刚才所示的在两个INSERT ... ON DUPLICATE KEY UPDATE语句中使用SET而不是VALUES可以用如下所示的方式实现：

INSERT INTO t1 SET a=1,b=2,c=3 AS new
  ON DUPLICATE KEY UPDATE c = new.a+new.b;

INSERT INTO t1 SET a=1,b=2,c=3 AS new(m,n,p)
  ON DUPLICATE KEY UPDATE c = m+n;

The row alias must not be the same as the name of the table. 行别名不能与表的名称相同。If column aliases are not used, or if they are the same as the column names, they must be distinguished using the row alias in the ON DUPLICATE KEY UPDATE clause. 如果不使用列别名，或者列别名与列名相同，则必须使用ON DUPLICATE KEY UPDATE子句中的行别名来区分它们。Column aliases must be unique with regard to the row alias to which they apply (that is, no column aliases referring to columns of the same row may be the same).列别名对于其应用的行别名必须是唯一的（即，引用同一行的列的列别名不能相同）。

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:对于INSERT ... SELECT语句，这些规则适用于可在ON DUPLICATE KEY UPDATE子句中引用的SELECT查询表达式的可接受形式：

References to columns from a UNION are not supported. 不支持从UNION引用列。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. 要解决此限制，请将UNION重写为派生表，以便将其行视为单个表结果集。For example, this statement produces an error:例如，此语句产生错误：

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:相反，请使用等效语句将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;

The technique of rewriting a query as a derived table also enables references to columns from GROUP BY queries.将查询重写为派生表的技术还允许从GROUP BY查询中引用列。

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. 因为INSERT ... SELECT语句的结果依赖于SELECT中的行的顺序，并且这种顺序不能总是得到保证，所以在记录INSERT ... SELECT ON DUPLICATE KEY UPDATE语句的日志时，源和副本的日志可能有分歧。Thus, INSERT ... SELECT ON DUPLICATE KEY UPDATE statements are flagged as unsafe for statement-based replication. 因此，INSERT ... SELECT ON DUPLICATE KEY UPDATE语句对于基于语句的复制被标记为不安全。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. 当使用基于语句的模式时，此类语句在错误日志中生成警告，当使用MIXED模式时，将使用基于行的格式写入二进制日志。An INSERT ... ON DUPLICATE KEY UPDATE statement against a table having more than one unique or primary key is also marked as unsafe. 针对具有多个唯一键或主键的表的INSERT ... ON DUPLICATE KEY UPDATE语句也被标记为不安全。(Bug #11765650, Bug #58637)

See also Section 17.2.1.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.另请参见第17.2.1.1节，“基于语句和基于行的复制的优缺点”。