REPLACE INTO

功能描述

replace是MySQL的一个扩展语法，它的工作原理与insert几乎相同，不同点在于表中存在唯一性约束时，replace into先删除冲突行，再插入新行。

注意事项

• 该功能仅在数据库兼容模式为MySQL时能够使用（即创建DB时DBCOMPATIBILITY='B'），在其他数据库兼容模式下不能使用该特性。

• 在使用replace语句时，用户必须对操作的表（客体）具有insert和delete权限。

• 不能将要做replace操作的表通过子查询语句的形式，作为replace操作的目标值，例如以下写法是错误的：

  REPLACE INTO test VALUES (1, 'Old', select aTime from test where id =2；
  

语法格式

REPLACE  [INTO]  table_name  VALUES   …
REPLACE  [INTO]  table_name  VALUE  {expr  |  DEFAULT}   …
REPLACE  [INTO]  table_name  SET  col_name  =  value  [,  col_name  =  value  ] …
REPLACE  [INTO]  table_name  SELECT …

参数说明

• table_name：

  表名。必须是已存在的表。

• col_name

  列名。

• expr

  赋予对应column的一个有效表达式或值：

• DEFAULT

  对应字段名的缺省值。如果没有缺省值，则为NULL。

示例

1、创建兼容MySQL的库db_mysql，并进入。

CREATE DATABASE db_mysql DBCOMPATIBILITY='B';
\c db_mysql

2、创建一张表，并指定主键。

CREATE TABLE test (
id int,
id2 int,
name varchar(50) DEFAULT NULL,
PRIMARY KEY(id)
);

3、插入测试数据。

replace test  values(1,1000,'test1');
replace test  values(2,2000,'test2');
replace test  values(3,3000,'test3');
replace test  values(4,4000,'test4');

4、查询测试表数据。

select * from test;

结果返回如下：

 id | id2  | name
----+------+-------
  1 | 1000 | test1
  2 | 2000 | test2
  3 | 3000 | test3
  4 | 4000 | test4
(4 rows)

5、执行replace 操作的数据，会与主键产生冲突。

replace test  values(1,4000,'test5');

6、查询ID为1数据是否被替换。

select * from test;

当结果显示如下信息，则表示替换成功。

 id | id2  | name  
----+------+-------
  2 | 2000 | test2
  3 | 3000 | test3
  4 | 4000 | test4
  1 | 4000 | test5
(4 rows)

如果表包含多个唯一索引，并且新行在不同的唯一索引中重复了不同旧行的值，则replace的单行可能出现替换多个旧行的结果（新插入一行，删除了多行）。