REPLACE 语句
REPLACE 的工作方式与 INSERT 完全相同，只是如果表中的旧行与新行具有相同的主键或唯一索引值，则在插入新行之前将删除旧行。 REPLACE是SQL标准的MySQL扩展。它要么插入，要么删除并插入。 DELAYED 插入和替换在 MySQL 5.6 中被弃用了。在 MySQL 8.0 中，不支持 DELAYED。服务器识别但忽略 DELAYED 关键字，将替换作为非延迟替换处理，并生成 ER_WARN_LEGACY_SYNTAX_CONVERTED 警告。（“不再支持REPLACE DELAYED。语句已转换为REPLACE。“）在将来的版本中将删除 DELAYED 关键字。 注意 只有当表具有主键或唯一索引时，REPLACE 才有意义。否则，它就等价于INSERT，因为没有用于确定新行是否与另一行重复的索引。 所有列的值都取自 REPLACE 语句中指定的值。任何缺少的列都设置为其默认值，就像 INSERT 一样。不能引用当前行中的值并在新行中使用它们。如果使用诸如 SET col_name = col_name + 1 这样的赋值，那么右侧对列名的引用将被视为 DEFAULT(col_name)，因此该赋值相当于 SETcol_name = DEFAULT(col_name) + 1。 在 MySQL8.0.19 及更高版本中，可以使用 VALUES ROW() 指定要插入的列值。 要使用 REPLACE，必须同时具有表的 INSERT 和 DELETE 权限。 如果显式替换生成的列，则唯一允许的值是 DEFAULT。 使用 PARTITION 关键字和用逗号分隔的分区、子分区名称列表，REPLACE支持显式的分区选择。与 INSERT 一样，如果无法将新行插入这些分区或子分区中，REPLACE 语句将失败，并返回错误：Found a row not matching the given partition set。 REPLACE 语句返回一个计数，指示受影响的行数。这是删除和插入的行的总和。如果单行 REPLACE 的计数为 1，则插入一行，但未删除行。如果计数大于 1，则在插入新行之前删除了一个或多个旧行。如果表包含多个唯一索引，并且新行与不同唯一索引中不同旧行的值重复，则一行可以替换多个旧行。 通过受影响的行数可以很容易地确定 REPLACE 是只添加了一行，还是还替换了某些行：检查计数是1（添加）还是更大（替换）。 如果在使用 C API，则可以使用 mysql_affected_rows() 函数获取受影响的行数。 不能在一个表中替换并在子查询中从同一表的中进行选择。 MySQL 使用以下算法用于 REPLACE（和 LOAD DATA ... REPLACE）： 1. 尝试将新行插入表中 2. 由于主键或唯一索引发生重复键错误，插入失败时： a. 从表中删除具有重复键值的冲突行 b. 再次尝试将新行插入表中 在出现键重复错误的情况下，存储引擎可能执行 REPLACE 来更新记录，而不是先删除再插入，但语义是相同的。除了存储引擎增加Handler_xxx 状态变量的方式可能存在差异外，没有用户可见的影响。 由于 REPLACE ... SELECT 语句的结果取决于 SELECT 中的行的顺序，并且不能总是保证这种顺序，在源服务器和从服务器记录这些语句时，可能会出现分歧。因此，对于基于语句的复制，REPLACE ... SELECT 语句被标记为不安全。当使用基于语句的模式时，此类语句在错误日志中生成警告，在使用混合模式时，这些语句将使用基于行的格式写入二进制日志。 MySQL 8.0.19 及更高版本支持 TABLE 和 SELECT 与 REPLACE 一起使用，就像 INSERT 一样。 当修改未分区的现有表以适应分区时，或者修改已分区的分区表时，可以考虑更改表的主键。应该知道，如果这样做，REPLACE 语句的结果可能会受到影响，就像修改非分区表的主键一样。考虑由以下 CREATE TABLE 语句创建的表：
当我们创建这个表并运行 mysql 客户端中显示的语句时，结果如下：
现在我们创建了第二个表，与第一个表几乎相同，只是主键现在覆盖了2列，如下所示：
当我们在 test2 上运行与原始 test 表相同的两个 REPLACE 语句时，会得到不同的结果：
这是因为在 test2 上运行时，id 和 ts 列的值必须与要替换的行的值相匹配；否则，将插入一行。
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_listREPLACE [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 = valueassignment_list:assignment [, assignment] ...
REPLACE 的工作方式与 INSERT 完全相同，只是如果表中的旧行与新行具有相同的主键或唯一索引值，则在插入新行之前将删除旧行。 REPLACE是SQL标准的MySQL扩展。它要么插入，要么删除并插入。 DELAYED 插入和替换在 MySQL 5.6 中被弃用了。在 MySQL 8.0 中，不支持 DELAYED。服务器识别但忽略 DELAYED 关键字，将替换作为非延迟替换处理，并生成 ER_WARN_LEGACY_SYNTAX_CONVERTED 警告。（“不再支持REPLACE DELAYED。语句已转换为REPLACE。“）在将来的版本中将删除 DELAYED 关键字。 注意 只有当表具有主键或唯一索引时，REPLACE 才有意义。否则，它就等价于INSERT，因为没有用于确定新行是否与另一行重复的索引。 所有列的值都取自 REPLACE 语句中指定的值。任何缺少的列都设置为其默认值，就像 INSERT 一样。不能引用当前行中的值并在新行中使用它们。如果使用诸如 SET col_name = col_name + 1 这样的赋值，那么右侧对列名的引用将被视为 DEFAULT(col_name)，因此该赋值相当于 SETcol_name = DEFAULT(col_name) + 1。 在 MySQL8.0.19 及更高版本中，可以使用 VALUES ROW() 指定要插入的列值。 要使用 REPLACE，必须同时具有表的 INSERT 和 DELETE 权限。 如果显式替换生成的列，则唯一允许的值是 DEFAULT。 使用 PARTITION 关键字和用逗号分隔的分区、子分区名称列表，REPLACE支持显式的分区选择。与 INSERT 一样，如果无法将新行插入这些分区或子分区中，REPLACE 语句将失败，并返回错误：Found a row not matching the given partition set。 REPLACE 语句返回一个计数，指示受影响的行数。这是删除和插入的行的总和。如果单行 REPLACE 的计数为 1，则插入一行，但未删除行。如果计数大于 1，则在插入新行之前删除了一个或多个旧行。如果表包含多个唯一索引，并且新行与不同唯一索引中不同旧行的值重复，则一行可以替换多个旧行。 通过受影响的行数可以很容易地确定 REPLACE 是只添加了一行，还是还替换了某些行：检查计数是1（添加）还是更大（替换）。 如果在使用 C API，则可以使用 mysql_affected_rows() 函数获取受影响的行数。 不能在一个表中替换并在子查询中从同一表的中进行选择。 MySQL 使用以下算法用于 REPLACE（和 LOAD DATA ... REPLACE）： 1. 尝试将新行插入表中 2. 由于主键或唯一索引发生重复键错误，插入失败时： a. 从表中删除具有重复键值的冲突行 b. 再次尝试将新行插入表中 在出现键重复错误的情况下，存储引擎可能执行 REPLACE 来更新记录，而不是先删除再插入，但语义是相同的。除了存储引擎增加Handler_xxx 状态变量的方式可能存在差异外，没有用户可见的影响。 由于 REPLACE ... SELECT 语句的结果取决于 SELECT 中的行的顺序，并且不能总是保证这种顺序，在源服务器和从服务器记录这些语句时，可能会出现分歧。因此，对于基于语句的复制，REPLACE ... SELECT 语句被标记为不安全。当使用基于语句的模式时，此类语句在错误日志中生成警告，在使用混合模式时，这些语句将使用基于行的格式写入二进制日志。 MySQL 8.0.19 及更高版本支持 TABLE 和 SELECT 与 REPLACE 一起使用，就像 INSERT 一样。 当修改未分区的现有表以适应分区时，或者修改已分区的分区表时，可以考虑更改表的主键。应该知道，如果这样做，REPLACE 语句的结果可能会受到影响，就像修改非分区表的主键一样。考虑由以下 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)
现在我们创建了第二个表，与第一个表几乎相同，只是主键现在覆盖了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 列的值必须与要替换的行的值相匹配；否则，将插入一行。
