文档中心

INSERT REPLACE ​

INSERT REPLACE INTO语句用于检查插入数据时的主键冲突或唯一索引冲突，若有冲突就会先删除之前的数据，然后再执行插入，若没有冲突则与INSERT INTO一样执行插入。

主要语法结构 ​

语法格式 ​

sql
replace_stmt::= 
    REPLACE INTO [schema_name.] table_name [PARTITION ( part_name_list ) | SUBPARTITION ( subpart_name_list )] insert_rest

参数说明 ​

  • schema_name：模式名。
  • table_name：表名。
  • part_name_list：表的分区名。
  • subpart_name_list：表的子分区名。

插入内容insert_rest ​

语法格式 ​

sql
insert_rest::=
    VALUES ( target_list )[( target_list ) | ,( target_list )]...
|   DEFAULT VALUES
|   selectstmt
|   ( columnList ) VALUES ( target_list )[( target_list ) | ,( target_list )]...
|   ( columnList ) selectstmt
|   VALUES ident
|   ( columnList ) VALUES ident

参数说明 ​

  • VALUES ( target_list )[( target_list ) | ,( target_list )]...：直接插入一组或多组值。
  • DEFAULT VALUES：插入默认值，适用于所有列都有默认值的情况。
  • selectstmt：一个 SELECT 语句，返回要插入的数据。从子查询中插入数据。
  • ( columnList ) VALUES ( target_list )[( target_list ) | ,( target_list )]...：指定列列表并插入一组或多组值。( target_list )插入的数据有以下形式
    • (expr1, expr2, ..., exprn)，括号中以逗号分隔的每项可以是常值、表达式等，每项与要插入数据的列对应。
    • (expr1, expr2, ..., exprn) (expr1, expr2, ..., exprn) ... 等，用以在一条语句里插入多行数据。
    • (expr1, expr2, ..., exprn),(expr1, expr2, ..., exprn),... 等，使用英文逗号分隔每一行插入的数据，插入多行数据。
  • ( columnList ) selectstmt：指定列列表并从子查询中插入数据。
  • VALUES ident：插入一个标识符（通常是变量或参数）。
  • ( columnList ) VALUES ident ：指定列列表并插入一个标识符（通常是变量或参数）。

示例 ​

  • 示例1
    同时执行多行插入命令，向含有唯一值约束的表插入三行数据，其中前两行为相同的数据，若不带REPLACE关键字，插入第二行数据时会检测到约束冲突，终止当前插入操作返回错误信息，因此只有第一行数据插入成功。若带REPLACE关键字则检查约束冲突，若存在冲突则删除原有数据，插入最新数据，因此第一和第三行数据插入成功，第二行数据更新成最新值。

    sql
    CREATE TABLE table_2(i INT IDENTITY(1,1),id int,name VARCHAR CONSTRAINT d_1 DEFAULT 'default',CONSTRAINT u_1 UNIQUE(id));

--INSERT 插入多行数据，违反自增序列的唯一值约束
INSERT INTO table_2(id,name) VALUES(1,'abc'); 
INSERT INTO table_2(id,name) VALUES(1,'bbb'); 
INSERT INTO table_2(id,name) VALUES(2,'abc'); 

--只有第一行数据插入成功
SELECT * FROM table_2;

--REPLACE 插入多行数据
REPLACE INTO table_2(id,name) VALUES(1,'abc'); 
REPLACE INTO table_2(id,name) VALUES(1,'bbb'); 
REPLACE INTO table_2(id,name) VALUES(2,'abc'); 

--第二行数据被替换，新插入第一、三行数据
SELECT * FROM table_2;

--REPLACE 字段取默认值
REPLACE INTO table_2(id) VALUES(3); 

--数据被替换成默认值
SELECT * FROM table_2;

    说明：

    • 若REPLACE的数据段不包含全部的数据，且字段含义默认值，则未指定的数据会被替换成默认值。
    • 若行存在自增字段，REPLACE含冲突的字段，对应的行该字段也会自增1。

  • 示例2
    执行一个插入命令，向含有唯一值约束的表插入三行数据，其中前两行为相同的数据，若不带REPLACE关键字，插入第二行数据时会检测到约束信息限制，终止当前插入操作返回错误信息，没有数据插入成功。若带REPLACE关键字则检查约束冲突，若存在冲突则删除原有数据，插入最新数据，不冲突数据照常插入。

    sql
    --INSERT 插入多行数据，违反自增序列的唯一值约束
INSERT INTO table_2(id,name) VALUES(3,'abc')(3,'bbb')(4,'abc'); 

--没有插入成功
SELECT * FROM table_2;

--REPLACE 插入多行数据
REPLACE INTO table_2(id,name) VALUES(3,'abc')(3,'bbb')(4,'abc'); 

--第二行数据被替换，新插入第一、三行数据
SELECT * FROM table_2;