13.2.8 REPLACE 声明

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_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 ...

value:
    {expr | DEFAULT}

value_list:
    value [, value] ...

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

REPLACE的工作方式与INSERT完全相同，不同之处在于，如果 table 中的旧行与PRIMARY KEY或UNIQUE索引的新行具有相同的值，则在插入新行之前删除该旧行。参见第 13.2.5 节“ INSERT 语句”。

REPLACE是 SQL 标准的 MySQL 扩展。它要么插入，要么“删除”并插入。有关标准 SQL 的另一个 MySQL 扩展(可以插入或* update *)，请参见第 13.2.5.2 节“在重复密钥更新语句上插入...”。

DELAYED插入和替换在 MySQL 5.6 中已弃用。在 MySQL 5.7 中，不支持DELAYED。服务器识别但忽略DELAYED关键字，将替换作为非延迟替换进行处理，并生成ER_WARN_LEGACY_SYNTAX_CONVERTED警告。 (“不再支持 REPLACE DELAYED.该语句已转换为 REPLACE.”)DELAYED关键字将在以后的版本中删除。

Note

仅当 table 具有PRIMARY KEY或UNIQUE索引时，REPLACE才有意义。否则，它等效于INSERT，因为没有索引可用于确定新行是否重复另一行。

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

要使用REPLACE，您必须同时拥有 table 的INSERT和DELETE特权。

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

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

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

“受影响的行数”使您可以轻松确定REPLACE是仅添加一行还是也替换了任何行：检查计数是 1(添加)还是更大(已替换)。

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

您不能替换为 table 并在子查询中从同一 table 中选择。

MySQL 对REPLACE(和加载数据...替换)使用以下算法：

  • 尝试将新行插入 table 中

  • 虽然插入失败是因为主键或唯一索引发生重复键错误：

  • 从 table 中删除具有重复键值的冲突行

    • 再试一次将新行插入 table 中

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

因为REPLACE ... SELECT语句的结果取决于来自SELECT的行的 Sequences，并且不能始终保证该 Sequences，所以在记录这些语句时可能使主服务器和从服务器发散。因此，对于基于语句的复制，REPLACE ... SELECT语句被标记为不安全。当使用基于语句的模式时，此类语句会在错误日志中产生警告，而在使用MIXED模式时，会使用基于行的格式将此类语句写入二进制日志。另请参见第 16.2.1.1 节，“基于语句的复制和基于行的复制的优缺点”。

在修改未分区的现有 table 以容纳分区时，或者在修改已分区 table 的分区时，您可以考虑更改 table 的主键(请参见第 22.6.1 节“分区键，主键和唯一键”)。您应该意识到，如果这样做，REPLACE语句的结果可能会受到影响，就像修改非分区 table 的主键时一样。考虑下面的CREATE TABLE语句创建的 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)
);

当我们创建该 table 并运行 mysqlClient 端中显示的语句时，结果如下：

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)

现在，我们创建与第二张 table 几乎相同的第二张 table，除了主键现在覆盖两列，如下所示(强调的文本)：

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上运行与在原始testtable 上相同的两个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列的值都必须与要替换的行的现有行的值匹配；否则，将插入一行。

使用 table 级锁的MyISAM之类的使用存储引擎影响分区 table 的REPLACE语句仅锁定那些包含与REPLACE语句WHERE子句匹配的行的分区，只要没有更新 table 分区列；否则整个 table 将被锁定。 (对于使用行级锁定的诸如InnoDB之类的存储引擎，不会发生分区锁定。)有关更多信息，请参见第 22.6.4 节“分区和锁定”。

Name MySql 中文文档
Version
Language
Badge
Last Updated 2021-07-03T12:24:35