• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • 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,不同之处在于,如果表中的旧行与a PRIMARY KEYUNIQUE索引的新行具有相同的值,则在插入新行之前删除该旧行。请参见“ 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 KEYUNIQUE索引时才有意义。否则,它等同于INSERT,因为没有索引可用于确定新行是否重复另一行。

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

    在MySQL 8.0.19及更高版本中,您可以使用REPLACE尝试指定要插入的列值VALUES ROW()

    要使用REPLACE,您必须同时拥有表的INSERTDELETE特权。

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

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

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

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

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

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

    MySQL对REPLACE(和LOAD DATA ... REPLACE)使用以下算法:

    1. 尝试将新行插入表中
    2. 虽然插入失败是因为主键或唯一索引发生重复键错误:

      1. 从表中删除具有重复键值的冲突行
      2. 再试一次将新行插入表中

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

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

    MySQL 8.0.19和更高版本支持TABLE以及,SELECTREPLACE一样INSERT。有关更多信息和示例,请参见“ INSERT ... SELECT语句”。

    修改未分区的现有表以容纳分区时,或者修改已分区表的分区时,可以考虑更改表的主键(请参见“分区键,主键和唯一键”)”)。您应该意识到,这样做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)
    

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

    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语句时,将test获得不同的结果:

    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)
    

    这是由于以下事实:在上运行时test2idts列值都必须与要替换的行的现有行的值匹配;否则,将插入一行。

    上篇:LOAD XML语句

    下篇:SELECT语句