MySQL replace语句

MySQL 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完全相同，不同的是，如果表中的旧行与PRIMARY KEY或UNIQUE索引中的新行具有相同的值，则在插入新行之前删除旧行。

REPLACE是MySQL对SQL标准的扩展。它要么插入，要么删除和插入。

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

只有当一个表有一个主键或唯一索引时，REPLACE才有意义。否则，它将等价于INSERT，因为没有索引可用于确定新行是否与另一行重复。

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

SET col_name = DEFAULT(col_name) + 1。

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

如果显式替换生成的列，则唯一允许的值是DEFAULT

REPLACE支持显式分区选择，使用partition关键字和分区、子分区或两者的相对名称列表。与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语句对于基于语句的复制来说被标记为不安全。当使用基于语句的模式时，这些语句会在错误日志中产生一个警告，而当使用MIXED模式时，这些语句会使用基于行的格式写入二进制日志。

当修改没有分区的现有表以适应分区时，或者在修改已分区表的分区时，可以考虑修改表的主键(参见22.6.1节，分区键、主键和唯一键)。您应该注意，如果这样做，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.03 sec)

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

mysql> SELECT * FROM test;
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
1 row in set (0.01 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上运行与在原始测试表上相同的两个REPLACE语句时，我们得到了不同的结果:

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

mysql> REPLACE INTO test2 VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 1 row affected (0.01 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.01 sec)

这是因为，当运行test2时，id和ts列值必须与现有行的值匹配，以便替换该行;否则，插入一行。

使用MyISAM等存储引擎(使用表级锁)的REPLACE语句影响分区表，只要不更新表分区列，则只锁那些包含匹配REPLACE语句WHERE子句的行的分区;否则锁定整个表。(对于像InnoDB这样使用行级锁的存储引擎，不会发生分区锁。)