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 的工作原理与 INSERT但如果表中的旧行与新行值相同，PRIMARY KEY 或 UNIQUE“索引，旧行会在插入新行前被删除。参见 第 15.2.7 节，“INSERT Statement”。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. See Section 15.2.7, “INSERT Statement”.

REPLACE 是 SQL 标准的 MySQL 扩展。它要么插入，要么 deletes 和 insert。关于另一个标准 SQL 的 MySQL 扩展——可以插入或updates——参见 第 15.2.7.2 节，“插入 ...ON DUPLICATE KEY UPDATE Statement”。REPLACE is a MySQL extension to the SQL standard. It either inserts, or deletes and inserts. For another MySQL extension to standard SQL—that either inserts or updates—see Section 15.2.7.2, “INSERT ... ON DUPLICATE KEY UPDATE Statement”.

DELAYED 插入和替换在 MySQL 5.6 中已被弃用。在 MySQL 8.4 中，DELAYED 不被支持。服务器识别但忽略 DELAYED 关键字，将替换视为非延迟替换，并生成 ER_WARN_LEGACY_SYNTAX_CONVERTED警告：REPLACE DELAYED 不再支持。该语句被转换为替换。DELAYED 关键字计划在未来的版本中被移除。发布。DELAYED inserts and replaces were deprecated in MySQL 5.6. In MySQL 8.4, DELAYED is not supported. 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. The DELAYED keyword is scheduled for removal in a future release. release.

所有列的值都取自语句中指定的值。REPLACE任何缺失的列都将设置为其默认值，就像在 中发生的情况一样。INSERT您不能引用当前行的值并在新行中使用它们。如果您使用诸如SET col_name = col_name + 1这样的赋值，右侧的列名引用将被视为DEFAULT(col_name)，因此赋值等同于SET col_name = DEFAULT(col_name) + 1.Values for all columns are taken from the values specified in the REPLACE statement. Any missing columns are set to their default values, just as happens for 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.

你可以指定 REPLACE 尝试插入的列值，使用 VALUES ROW()。You can specify the column values that REPLACE attempts to insert using VALUES ROW().

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

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

REPLACE 支持使用 PARTITION 子句显式选择分区，通过逗号分隔的分区名、子分区名或两者的列表进行指定。与 INSERT 类似，如果无法将新行插入到这些分区或子分区中的任意一个，REPLACE 语句将失败并报错 Found a row not matching the given partition set。更多信息和示例，请参阅 第 26.5 节 “分区选择”。REPLACE supports explicit partition selection using the PARTITION clause with a list of comma-separated names of partitions, subpartitions, or both. 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. For more information and examples, see Section 26.5, “Partition Selection”.

REPLACE 语句返回一个计数，用以指示受影响的行数。这是被删除行数和插入行数的总和。如果对于单行 REPLACE 计数为 1，则表示插入了一行，没有删除任何行。如果计数大于 1，则表示在插入新行之前删除了一个或多个旧行。如果表包含多个唯一索引，并且新行在不同的唯一索引中与旧行重复值，则单行可能替换多行旧行。The REPLACE statement returns a count to indicate the number of rows affected. 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. If the count is greater than 1, one or more old rows were deleted before the new row was inserted. 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.

受影响行数使得判断 REPLACE 操作仅添加行或同时替换了行变得容易：检查计数是否为 1（添加）或大于 1（替换）。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).

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

不能在子查询中对一个表执行替换操作的同时从同一个表中选择数据。You cannot replace into a table and select from the same table in a subquery.

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

在出现重复键错误的情况下，存储引擎可能会将 REPLACE 操作作为更新而非删除加插入来执行，但其语义是相同的。用户可见的效果仅可能在存储引擎如何增加 Handler_xxx 状态变量上存在差异。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. There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables.

由于 REPLACE ... SELECT取决于从 SELECT 的顺序，因此在为源和副本记录这些语句时，可能会出现分歧。因此，REPLACE ... SELECT 语句在基于语句的复制中被标记为不安全。当使用基于语句的模式时，这些语句会在错误日志中产生警告;在使用 MIXED。另见 第 19.2.1.1 节，“基于语句和基于行复制的优缺点”。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. For this reason, REPLACE ... SELECT 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. See also Section 19.2.1.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.

MySQL 8. 4 支持 TABLE“，以及 SELECT with REPLACE就像它支持 INSERT。参见 第 15.2.7.1 节，“INSERT ...SELECT 语句“，了解更多信息和示例。MySQL 8.4 supports TABLE as well as SELECT with REPLACE, just as it does with INSERT. See Section 15.2.7.1, “INSERT ... SELECT Statement”, for more information and examples.

在修改未分区的现有表以适应分区，或修改已分区表的分区时，可以考虑更改表的主键（参见 第 26.6.1 节，”分区键、主键和唯一键“）。你应注意，这样做时，REPLACE 语句的结果可能会受到影响，就像修改未分区表的主键一样。考虑由以下表格创建的表格 CREATE TABLE 语句：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 26.6.1, “Partitioning Keys, Primary Keys, and 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. Consider the table created by the following CREATE TABLE statement:

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)
);

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

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):

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)
);

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

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)

这是因为在 test2时，id 和 ts 列值必须与现有行一致，才能被替换该行;否则，插入一行。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.