MySQL :: MySQL 9.0 参考手册 :: 15.2.12 REPLACE 语句

版本 9.0

8.4 当前
8.0
5.7

8.0 日语

MySQL 9.0 参考手册 / ... / REPLACE 语句

15.2.12 REPLACE 语句

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 语句”。

REPLACE 是 MySQL 对 SQL 标准的扩展。它要么插入，要么删除然后插入。有关 MySQL 对标准 SQL 的另一个扩展（要么插入，要么更新）的信息，请参见第 15.2.7.2 节，“INSERT ... ON DUPLICATE KEY UPDATE 语句”。

DELAYED 插入和替换在 MySQL 5.6 中已弃用。在 MySQL 9.0 中，不支持 DELAYED。服务器会识别但忽略 DELAYED 关键字，将替换处理为非延迟替换，并生成一个 ER_WARN_LEGACY_SYNTAX_CONVERTED 警告：不再支持 REPLACE DELAYED。该语句已转换为 REPLACE。DELAYED 关键字计划在将来的版本中删除。发行版本。

注意

REPLACE 只有在表具有 PRIMARY KEY 或 UNIQUE 索引时才有意义。否则，它等效于 INSERT，因为没有可用于确定新行是否重复的索引。

所有列的值都取自 REPLACE 语句中指定的值。任何缺失的列都将设置为其默认值，就像 INSERT 一样。您不能引用当前行中的值并在新行中使用它们。如果您使用赋值语句，例如 SET col_name = col_name + 1，则右侧对列名的引用将被视为 DEFAULT(col_name)，因此赋值等效于 SET col_name = DEFAULT(col_name) + 1。

您可以使用 VALUES ROW() 指定 REPLACE 尝试插入的列值。

要使用 REPLACE，您必须对表具有 INSERT 和 DELETE 权限。

如果显式替换生成列，则唯一允许的值为 DEFAULT。有关生成列的信息，请参见第 15.1.20.8 节，“CREATE TABLE 和生成列”。

REPLACE 支持使用包含分区、子分区或两者的逗号分隔名称列表的 PARTITION 子句显式选择分区。与 INSERT 一样，如果无法将新行插入到这些分区或子分区中的任何一个，则 REPLACE 语句将失败，并出现错误找到与给定分区集不匹配的行。有关更多信息和示例，请参见第 26.5 节，“分区选择”。

REPLACE 语句返回一个计数以指示受影响的行数。这是删除和插入的行数之和。如果对于单行 REPLACE，计数为 1，则插入了一行，并且没有删除任何行。如果计数大于 1，则在新行插入之前删除了一行或多行旧行。如果表包含多个唯一索引，并且新行在不同的唯一索引中重复了不同旧行的值，则单行可能会替换多行旧行。

受影响的行数使您能够轻松确定 REPLACE 只添加了一行还是还替换了任何行：检查计数是否为 1（已添加）还是大于 1（已替换）。

如果您使用的是 C API，则可以使用 mysql_affected_rows() 函数获取受影响的行数。

您不能将数据替换到表中并在子查询中从同一表中选择数据。

MySQL 对 REPLACE（以及 LOAD DATA ... REPLACE）使用以下算法

尝试将新行插入到表中
当由于主键或唯一索引发生重复键错误导致插入失败时
1. 从表中删除具有重复键值的冲突行
2. 再次尝试将新行插入到表中

在发生重复键错误的情况下，存储引擎可能会将 REPLACE 作为更新而不是删除加插入来执行，但语义是相同的。除了存储引擎如何递增 Handler_xxx 状态变量之外，没有用户可见的效果。

由于 REPLACE ... SELECT 语句的结果取决于从 SELECT 中获取的行顺序，而此顺序并非始终可以保证，因此在为源和副本记录这些语句时，它们可能会出现偏差。因此，REPLACE ... SELECT 语句被标记为对于基于语句的复制来说是不安全的。当使用基于语句的模式时，此类语句会在错误日志中产生警告，并且当使用 MIXED 模式时，它们会使用基于行的格式写入二进制日志。另请参见第 19.2.1.1 节，“基于语句和基于行的复制的优缺点”。

MySQL 9.0 支持 TABLE 以及 SELECT 与 REPLACE 结合使用，就像它与 INSERT 结合使用一样。有关更多信息和示例，请参见第 15.2.7.1 节，“INSERT ... SELECT 语句”。

当修改现有的未分区表以适应分区时，或者当修改已分区表的划分时，您可能需要考虑更改表的 primary key（请参见第 26.6.1 节，“分区键、主键和唯一键”）。您应该注意，如果您执行此操作，REPLACE 语句的结果可能会受到影响，就像您修改了未分区表的 primary key 一样。请考虑以下 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)
);

当我们创建此表并在 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)

现在我们创建一个第二个表，它与第一个表几乎相同，只是 primary key 现在涵盖了 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)
);

当我们在 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)

这是因为当在 test2 上运行时，id 和 ts 列值都必须与现有行的值匹配，才能替换该行；否则，将插入一行。

PREV HOME UP NEXT