REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    { {VALUES | VALUE} (value_list) [, (value_list)] ...
      |
      VALUES row_constructor_list
    }

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    SET assignment_list

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    {SELECT ... | TABLE table_name}
value:
    {expr | DEFAULT}
value_list:
value [, value] ...
row_constructor_list:
    ROW(value_list)[, ROW(value_list)][, ...]
assignment:
col_name = value
assignment_list:
assignment [, assignment] ...

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. REPLACE的工作方式与INSERT完全相同，只是如果表中的旧行与主键或唯一索引的新行具有相同的值，则在插入新行之前会删除旧行。See Section 13.2.6, “INSERT Statement”.见第13.2.6节，“插入声明”。

REPLACE is a MySQL extension to the SQL standard. 是SQL标准的MySQL扩展。It either inserts, or deletes and inserts. 它要么插入，要么删除并插入。For another MySQL extension to standard SQL—that either inserts or updates—see Section 13.2.6.2, “INSERT ... ON DUPLICATE KEY UPDATE Statement”.有关插入或更新标准SQL的另一个MySQL扩展，请参阅第13.2.6.2节，“INSERT ... ON DUPLICATE KEY UPDATE语句”。

DELAYED inserts and replaces were deprecated in MySQL 5.6. DELAYED插入和替换在MySQL5.6中被弃用。In MySQL 8.0, DELAYED is not supported. 在MySQL 8.0中，不支持DELAYED。The server recognizes but ignores the DELAYED keyword, handles the replace as a nondelayed replace, and generates an ER_WARN_LEGACY_SYNTAX_CONVERTED warning: REPLACE DELAYED is no longer supported. The statement was converted to REPLACE. 服务器识别但忽略DELAYED关键字，将替换处理为非延迟替换，并生成ER_WARN_LEGACY_SYNTAX_CONVERTED警告：不再支持REPLACE DELAYED。语句已转换为REPLACE。The DELAYED keyword is scheduled for removal in a future release. release.DELAYED关键字计划在将来的版本中删除。释放。

Values for all columns are taken from the values specified in the REPLACE statement. 所有列的值都取自REPLACE语句中指定的值。Any missing columns are set to their default values, just as happens for INSERT. 任何缺少的列都被设置为它们的默认值，就像INSERT一样。You cannot refer to values from the current row and use them in the new row. 不能引用当前行中的值并在新行中使用它们。If you use an assignment such as SET col_name = col_name + 1, the reference to the column name on the right hand side is treated as DEFAULT(col_name), so the assignment is equivalent to SET col_name = DEFAULT(col_name) + 1.如果你使用一个赋值，譬如SET col_name = col_name + 1，在右侧对列名的引用会被视为DEFAULT(col_name)，所以赋值等于SET col_name = DEFAULT(col_name) + 1。

In MySQL 8.0.19 and later, you can specify the column values that REPLACE attempts to insert using VALUES ROW().在MySQL8.0.19及更高版本中，可以使用ROW()指定REPLACE插入尝试的列值。

To use REPLACE, you must have both the INSERT and DELETE privileges for the table.要使用REPLACE，您必须同时具有表的INSERT和DELETE权限。

If a generated column is replaced explicitly, the only permitted value is DEFAULT. 如果显式替换生成的列，则唯一允许的值是DEFAULT。For information about generated columns, see Section 13.1.20.8, “CREATE TABLE and Generated Columns”.有关生成列的信息，请参阅第13.1.20.8节，“创建表和生成列”。

REPLACE supports explicit partition selection using the PARTITION clause with a list of comma-separated names of partitions, subpartitions, or both. REPLACE支持使用PARTITION子句显式选择分区，该子句包含以逗号分隔的分区、子分区或两者的名称列表。As with INSERT, if it is not possible to insert the new row into any of these partitions or subpartitions, the REPLACE statement fails with the error Found a row not matching the given partition set. 与INSERT一样，如果无法将新行插入这些分区或子分区中的任何一个，则REPLACE语句将失败，错误为“发现与给定分区集不匹配的行”。For more information and examples, see Section 24.5, “Partition Selection”.有关更多信息和示例，请参阅第24.5节，“分区选择”。

The REPLACE statement returns a count to indicate the number of rows affected. REPLACE语句返回一个计数，以指示受影响的行数。This is the sum of the rows deleted and inserted. 这是删除和插入的行的总和。If the count is 1 for a single-row REPLACE, a row was inserted and no rows were deleted. 如果单行REPLACE的计数为1，则插入了一行而未删除任何行。If the count is greater than 1, one or more old rows were deleted before the new row was inserted. 如果计数大于1，则在插入新行之前删除了一个或多个旧行。It is possible for a single row to replace more than one old row if the table contains multiple unique indexes and the new row duplicates values for different old rows in different unique indexes.如果表包含多个唯一索引，并且新行在不同的唯一索引中重复了不同旧行的值，则单行可以替换多个旧行。

The affected-rows count makes it easy to determine whether REPLACE only added a row or whether it also replaced any rows: Check whether the count is 1 (added) or greater (replaced).受影响的行计数可以很容易地确定REPLACE是仅添加了一行还是同时替换了任何行：检查计数是1（已添加）还是更大（已替换）。

If you are using the C API, the affected-rows count can be obtained using the mysql_affected_rows() function.如果您使用的是C API，那么可以使用mysql_impacted_rows()函数获取受影响的行数。

You cannot replace into a table and select from the same table in a subquery.不能将替换为表并从子查询中的同一表中进行选择。

MySQL uses the following algorithm for REPLACE (and LOAD DATA ... REPLACE):MySQL为REPLACE（和LOAD DATA ... REPLACE）使用以下算法：

It is possible that in the case of a duplicate-key error, a storage engine may perform the REPLACE as an update rather than a delete plus insert, but the semantics are the same. 在出现重复密钥错误的情况下，存储引擎可能会将REPLACE作为更新而不是删除加插入来执行，但语义是相同的。There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables.除了存储引擎如何增加Handler_xxx状态变量的可能差异之外，没有用户可见的效果。

Because the results of REPLACE ... SELECT statements depend on the ordering of rows from the SELECT and this order cannot always be guaranteed, it is possible when logging these statements for the source and the replica to diverge. 因为REPLACE ... SELECT语句的结果取决于SELECT中的行的顺序，并且这种顺序不能总是得到保证，在记录这些语句时，源和副本可能会出现分歧。For this reason, REPLACE ... SELECT statements are flagged as unsafe for statement-based replication. 因此，REPLACE ... SELECT语句对于基于语句的复制，被标记为不安全。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模式时，将使用基于行的格式写入二进制日志。See also Section 17.2.1.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.另请参见第17.2.1.1节，“基于语句和基于行的复制的优缺点”。

MySQL 8.0.19 and later supports TABLE as well as SELECT with REPLACE, just as it does with INSERT. MySQL 8.0.19及更高版本支持TABLE和带有REPLACE的SELECT，就像它对INSERT所做的那样。See Section 13.2.6.1, “INSERT ... SELECT Statement”, for more information and examples.有关更多信息和示例，请参阅第13.2.6.1节，“INSERT ... SELECT语句”。

When modifying an existing table that is not partitioned to accommodate partitioning, or, when modifying the partitioning of an already partitioned table, you may consider altering the table's primary key (see Section 24.6.1, “Partitioning Keys, Primary Keys, and Unique Keys”). 在修改未分区的现有表以适应分区时，或者，当修改已分区表的分区时，可以考虑更改表的主键（参见第24.61节、“分区键、主键和Unique Keys”）。You should be aware that, if you do this, the results of REPLACE statements may be affected, just as they would be if you modified the primary key of a nonpartitioned table. 您应该知道，如果这样做，REPLACE语句的结果可能会受到影响，就像修改非分区表的主键一样。Consider the table created by the following CREATE TABLE statement:考虑下面的CREATE TABLE语句创建的表：

CREATE TABLE test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data VARCHAR(64) DEFAULT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

When we create this table and run the statements shown in the mysql client, the result is as follows:当我们创建这个表并运行mysql客户端中显示的语句时，结果如下：

mysql> REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.04 sec)

mysql> REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 2 rows affected (0.04 sec)

mysql> SELECT * FROM test;
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
1 row in set (0.00 sec)

Now we create a second table almost identical to the first, except that the primary key now covers 2 columns, as shown here (emphasized text):现在，我们创建第二个表，与第一个表几乎相同，只是主键现在包含2列，如下所示（强调文本）：

CREATE TABLE test2 (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data VARCHAR(64) DEFAULT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id, ts)
);

When we run on test2 the same two REPLACE statements as we did on the original test table, we obtain a different result:当我们在test2上运行与在原始test表上相同的两个REPLACE语句时，我们得到了不同的结果：

mysql> REPLACE INTO test2 VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.05 sec)

mysql> REPLACE INTO test2 VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM test2;
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | Old  | 2014-08-20 18:47:00 |
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
2 rows in set (0.00 sec)

This is due to the fact that, when run on test2, both the id and ts column values must match those of an existing row for the row to be replaced; otherwise, a row is inserted.这是因为在test2上运行时，id和ts列值必须与要替换的行的现有行的值匹配；否则，将插入一行。