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_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 = value assignment_list: assignment [, assignment] ...
REPLACE
的工作方式与完全相同INSERT
,不同之处在于,如果表中的旧行与a PRIMARY KEY
或UNIQUE
索引的新行具有相同的值,则在插入新行之前删除该旧行。请参见“ INSERT语句”。
REPLACE
是对SQL标准的MySQL扩展。它要么插入,要么删除并插入。对于另一个MySQL扩展到标准SQL-,要么插入或更新-请参阅第13.2.6.2,“INSERT ... ON DUPLICATE KEY UPDATE语句”。
DELAYED
插入和替换在MySQL 5.6中已弃用。在MySQL 8.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
在MySQL 8.0.19及更高版本中,您可以使用REPLACE
尝试指定要插入的列值VALUES ROW()
。
要使用REPLACE
,您必须同时拥有表的INSERT
和DELETE
特权。
如果显式替换了生成的列,则唯一允许的值为DEFAULT
。有关生成的列的信息,请参见“创建表和生成的列”。
REPLACE
支持使用PARTITION
关键字进行显式分区选择,该关键字带有分区,子分区或两者的逗号分隔名称列表。与之相同INSERT
,如果无法将新行插入这些分区或子分区中的任何一个,则该REPLACE
语句将失败,并显示错误“找到与给定分区集不匹配的行”。有关更多信息和示例,请参见“分区选择”。
该REPLACE
语句返回一个计数,以指示受影响的行数。这是删除和插入的行的总和。如果单行的计数为1,则会REPLACE
插入一行,并且不会删除任何行。如果计数大于1,则在插入新行之前删除一个或多个旧行。如果表包含多个唯一索引并且新行复制了不同唯一索引中不同旧行的值,则单行可能会替换一个以上的旧行。
受影响的行数使您可以轻松确定是REPLACE
仅添加一行还是也替换了任何行:检查计数是1(添加)还是更大(已替换)。
如果使用的是C API,则可以使用mysql_affected_rows()
函数获取受影响的行数。
您不能替换为表并在子查询中从同一表中选择。
MySQL对REPLACE
(和LOAD DATA ... REPLACE
)使用以下算法:
- 尝试将新行插入表中
虽然插入失败是因为主键或唯一索引发生重复键错误:
- 从表中删除具有重复键值的冲突行
- 再试一次将新行插入表中
在重复密钥错误的情况下,存储引擎可能会执行REPLACE
更新操作而不是删除加插入操作,但是语义是相同的。除了存储引擎如何递增状态变量的可能差异之外,没有任何用户可见的效果。Handler_xxx
因为REPLACE ... SELECT
语句的结果取决于中的行的顺序,SELECT
并且不能始终保证此顺序,所以在记录这些语句时可能会使主服务器和从服务器发散。因此,REPLACE ... SELECT
对于基于语句的复制,语句被标记为不安全。使用基于语句的模式时,此类语句会在错误日志中产生警告,而使用模式时,此类语句将使用基于行的格式写入二进制日志中MIXED
。另请参见“复制格式”。
MySQL 8.0.19和更高版本支持TABLE
以及,SELECT
与REPLACE
一样INSERT
。有关更多信息和示例,请参见“ INSERT ... SELECT语句”。
修改未分区的现有表以容纳分区时,或者修改已分区表的分区时,可以考虑更改表的主键(请参见“分区键,主键和唯一键”)”)。您应该意识到,这样做REPLACE
会影响语句的结果,就像修改未分区表的主键一样。考虑以下CREATE TABLE
语句创建的表:
CREATE TABLE test ( id INTUNSIGNED NOT NULLAUTO_INCREMENT ,data VARCHAR(64)DEFAULT NULL, ts TIMESTAMP NOT NULLDEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,PRIMARY KEY (id) );
当我们创建该表并运行mysql客户端中显示的语句时,结果如下:
mysql>REPLACE INTO testVALUES (1, 'Old', '2014-08-20 18:47:00'); Query OK, 1 row affected (0.04 sec) mysql>REPLACE INTO testVALUES (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)
现在,我们创建与第二张表几乎相同的第二张表,除了主键现在覆盖两列,如下所示(强调的文本):
CREATE TABLE test2 ( id INTUNSIGNED NOT NULLAUTO_INCREMENT ,data VARCHAR(64)DEFAULT NULL, ts TIMESTAMP NOT NULLDEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,PRIMARY KEY (id, ts) );
当我们在原始表上test2
执行相同的两个REPLACE
语句时,将test
获得不同的结果:
mysql>REPLACE INTO test2VALUES (1, 'Old', '2014-08-20 18:47:00'); Query OK, 1 row affected (0.05 sec) mysql>REPLACE INTO test2VALUES (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
列值都必须与要替换的行的现有行的值匹配;否则,将插入一行。