INSERT
功能描述
向表中添加一行或多行数据。
注意事项
- 使用INSERT请遵守INSERT开发规范。
- 只有拥有表INSERT权限的用户,才可以向表中插入数据。
- 如果使用RETURNING子句,用户必须要有该表的SELECT权限。
- 如果使用QUERY子句插入来自查询里的数据行,用户还需要拥有在查询里使用的表的SELECT权限。
- 如果使用OVERWRITE子句覆盖式插入数据,用户还需要拥有该表的SELECT和TRUNCATE权限(因为OVERWRITE操作是先清空目标表原有数据)。
- 当连接到TD兼容的数据库时,td_compatible_truncation 参数设置为on时,将启用超长字符串自动截断功能,在后续的INSERT语句中(不包含外表的场景下),对目标表中char和varchar类型的列上插入超长字符串时,系统会自动按照目标表中相应列定义的最大长度对超长字符串进行截断。
如果向字符集为字节类型编码(SQL_ASCII,LATIN1等)的数据库中插入多字节字符数据(如汉字等),且字符数据跨越截断位置,这种情况下,按照字节长度自动截断,自动截断后会在尾部产生非预期结果。如果用户有对于截断结果正确性的要求,建议用户采用UTF8等能够按照字符截断的输入字符集作为数据库的编码集。
语法格式
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
[ WITH [ RECURSIVE ] with_query [, ...] ] INSERT [/*+ plan_hint */] [ IGNORE | OVERWRITE ] INTO table_name [ partition_clause ] [ AS alias ] [ ( column_name [, ...] ) ] { DEFAULT VALUES | VALUES {( { expression | DEFAULT } [, ...] ) }[, ...] | query } [ ON DUPLICATE KEY duplicate_action | ON CONFLICT [ conflict_target ] conflict_action ] [ RETURNING {* | {output_expression [ [ AS ] output_name ] }[, ...]} ]; where partition_clause can be: PARTITION ( partition_name ) | PARTITION FOR ( partition_key_value [, ...] ) and duplicate_action can be: UPDATE { column_name = { expression | DEFAULT } | ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...] and conflict_target can be one of: ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ] ON CONSTRAINT constraint_name and conflict_action is one of: DO NOTHING DO UPDATE SET { column_name = { expression | DEFAULT } | ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...] [ WHERE condition ] |
参数说明
参数 |
描述 |
取值范围或示例 |
---|---|---|
WITH [ RECURSIVE ] with_query [, ...] |
用于声明一个或多个可以在主查询中通过名字引用的子查询,相当于临时表。 如果声明了RECURSIVE,则允许SELECT子查询通过名字引用它自己。 其中with_query的详细格式为: with_query_name [ ( column_name [, ...] ) ] AS ( {select | values | insert | update | delete} ) – with_query_name指定子查询生成的结果集名字,在查询中可使用该名称访问子查询的结果集。 – column_name指定子查询结果集中显示的列名。 – 每个子查询可以是SELECT,VALUES,INSERT,UPDATE或DELETE语句。 |
|
plan_hint子句 |
以/*+ */的形式在关键字后,用于对指定语句块生成的计划进行hint调优,详细用法请参见使用Plan Hint进行调优。 |
- |
IGNORE |
用于主键或者唯一约束冲突时忽略冲突的数据。 详细介绍参见UPSERT。 |
|
OVERWRITE |
仅8.1.1及以上版本支持,用于标识覆盖式插入方式,使用此种插入方式执行结束后,目标源数据被清空,只存在新插入的数据。 OVERWRITE支持指定列插入的功能,其他列为默认值,若无默认值则为NULL。 INSERT OVERWRITE约束:
|
- |
table_name |
要插入数据的目标表名。 |
已存在的表名。 |
AS |
用于给目标表table_name指定别名。alias即为别名的名字。 |
- |
column_name |
目标表中的字段名:
|
已存在的字段名。 |
partition_name |
分区名。 |
已存在的分区名。 |
partition_key_value |
分区键值。 通过PARTITION FOR ( partition_key_value [, ...] )子句指定的这一组值,可以唯一确定一个分区。支持指定多个分区键值,多个分区键值以英文逗号分隔。 |
需要进行重命名分区的分区键的取值范围。 |
expression |
赋予对应column的一个有效表达式或值:
|
|
DEFAULT |
对应字段名的缺省值。 |
如果没有缺省值,则为NULL,参见示例:写入数据时对某个字段使用默认值。 |
query |
一个查询语句(SELECT语句),将查询结果作为插入的数据。 |
|
ON DUPLICATE KEY |
用于主键或者唯一约束冲突时更新冲突的数据。 duplicate_action指定更新列和更新的数据。 详细介绍参见UPSERT。 |
|
ON CONFLICT |
用于主键或者唯一约束冲突时忽略或者更新冲突的数据。 conflict_target用于指定列名index_column_name 、包含多个列名的表达式index_expression或约束名字constraint_name。用于从列名、包含多个列名的表达式或者约束名推断是否有唯一索引。其中index_column_name和index_expression遵循CREATE INDEX的索引列格式。 conflict_action指定主键或者唯一约束冲突时执行的策略。有两种:
详细介绍参见UPSERT。 |
|
RETURNING |
返回实际插入的行,RETURNING列表的语法与SELECT的输出列表一致。 |
|
output_expression |
INSERT命令在每一行都被插入之后用于计算输出结果的表达式。 |
该表达式可以使用table的任意字段。可以使用*返回被插入行的所有字段。 |
output_name |
字段的输出名称。 |
字符串,符合标识符命名规范。 |
示例:插入单条或多条数据
创建表reason_t1:
1 2 3 4 5 6 7 |
DROP TABLE IF EXISTS reason_t1; CREATE TABLE reason_t1 ( TABLE_SK INTEGER , TABLE_ID VARCHAR(20) , TABLE_NA VARCHAR(20) ); |
向表中插入一条数据:
1
|
INSERT INTO reason_t1(TABLE_SK, TABLE_ID, TABLE_NA) VALUES (1, 'S01', 'StudentA'); |
向表中插入一条数据,和上一条语法等效:
1
|
INSERT INTO reason_t1 VALUES (1, 'S01', 'StudentA'); |
向表中插入TABLE_SK小于1的数据:
1
|
INSERT INTO reason_t1 SELECT * FROM reason_t1 WHERE TABLE_SK < 1; |
向表中插入多条数据:
1 2 |
INSERT INTO reason_t1 VALUES (1, 'S01', 'StudentA'),(2, 'T01', 'TeacherA'),(3, 'T02', 'TeacherB'); SELECT * FROM reason_t1 ORDER BY 1; |
示例:使用RETURNING返回插入结果
插入表数据,并返回插入的数据对应的字段table_id和table_na的取值。
1
|
INSERT INTO reason_t1 SELECT * FROM reason_t1 RETURNING table_id, table_na; |
插入数据,并返回插入数据的所有字段取值。
1
|
INSERT INTO reason_t1 SELECT * FROM reason_t1 RETURNING *; |
插入数据,并返回重命名后的字段my_id取值。
1
|
INSERT INTO reason_t1 VALUES (1, 'S01', 'StudentA') RETURNING table_id AS my_id; |
示例:使用INSERT OVERWRITE覆盖式插入
使用INSERT OVERWRITE更新表中的数据,即覆盖式插入数据(先清空目标表数据):
1 2 |
INSERT OVERWRITE INTO reason_t1 values (5, 'S02', 'StudentB'); SELECT * FROM reason_t1 ORDER BY 1; |
示例:写入数据时对某个字段使用默认值
1 2 3 4 5 6 |
DROP TABLE IF EXISTS products; CREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR(50), price DECIMAL(10, 2) DEFAULT 0.00 ); |
插入一条数据,未定义price字段的取值,则默认使用0.00。
1
|
INSERT INTO products (id, name)VALUES (201, 'phone'); -- price字段将使用默认值0.00 |
插入一条数据,显式使用默认值。
1 2 |
INSERT INTO products (id, name, price)VALUES (202, 'pad', DEFAULT); -- 显式使用默认值 SELECT * FROM product; |
示例:使用WITH语句生成临时表再插入另一张表
将一个表中的部分数据插入到另一个表中:先通过WITH子查询得到一张临时表temp_t,然后将临时表temp_t中的所有数据插入另一张表reason_t1中:
WITH temp_t AS (SELECT * FROM reason_t1) INSERT INTO reason_t1 SELECT * FROM temp_t ORDER BY 1;
示例:使用IGNORE忽略主键冲突
1 2 3 4 5 6 |
DROP TABLE IF EXISTS users; CREATE TABLE users ( id INT PRIMARY KEY, username VARCHAR(50) ) DISTRIBUTE BY HASH (id); |
插入两条主键都是1的记录,提示冲突,无法执行。
1 2 3 4 |
INSERT INTO users (id, username) VALUES (1, 'john_doe'), (2, 'jane_smith'), (1, 'duplicate_user'); --主键冲突,无法插入 |
使用IGNORE忽略冲突,冲突数据被忽略跳过。
1 2 3 4 5 |
INSERT IGNORE INTO users (id, username) VALUES (1, 'john_doe'), (2, 'jane_smith'), (1, 'duplicate_user'); -- 冲突数据会被忽略 SELECT * FROM users; |
示例:使用ON DUPLICATE KEY更新主键或唯一约束冲突时插入数据
1 2 3 4 5 |
DROP TABLE IF EXISTS user_logins; CREATE TABLE user_logins (user_id int primary key, login_count int) DISTRIBUTE BY HASH (user_id); |
插入2条数据,第二条主键冲突,插入失败。
1 2 3 |
INSERT INTO user_logins VALUES (1, 1); INSERT INTO user_logins VALUES (1, 1); --主键冲突,插入失败 SELECT * FROM user_logins; |
使用ON DUPLICATE KEY声明,如果主键冲突了,则更新login_count字段,使其加1。
1 2 |
INSERT INTO user_logins VALUES (1, 1) ON DUPLICATE KEY UPDATE login_count = login_count + 1; SELECT * FROM user_logins; |
示例:向某列中插入一个表达式或值
1 2 3 4 5 6 7 8 |
CREATE TABLE tt01 (id int,content varchar(50)); INSERT INTO tt01 values (1,'Jack say ''hello'''); --插入Jack say 'hello',加单引号进行转义 INSERT INTO tt01 values (2,'Rose do 50%'); INSERT INTO tt01 values (3,'Lilei say ''world'''); --插入Lilei say 'world',加单引号进行转义 INSERT INTO tt01 values (4,'Hanmei do 100%'); SELECT * FROM tt01; |
示例:向分区表的指定分区插入数据
向分区表的指定分区插入数据:
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE test_range_row(a int, d int) DISTRIBUTE BY hash(a) PARTITION BY RANGE(d) ( PARTITION p1 values LESS THAN (60), PARTITION p2 values LESS THAN (75), PARTITION p3 values LESS THAN (90), PARTITION p4 VALUES LESS THAN (maxvalue) ); INSERT OVERWRITE INTO test_range_row PARTITION(p1) VALUES(55,51); INSERT OVERWRITE INTO test_range_row PARTITION(p3) VALUES(85,80); DELETE FROM test_range_row PARTITION(p1); |
1 2 |
CREATE FOREIGN TABLE test_ft(c1 text, c2 text, c3 text, c4 text) PARTITION BY (c1,c2,c3) AUTOMAPPED; |
1
|
INSERT OVERWRITE INTO test_ft PARTITION FOR(p1_1) VALUES(p1_1,2,3,4); |
1
|
INSERT OVERWRITE INTO test_ft PARTITION FOR(p1_2,p2_2) VALUES(p1_2,p2_2,3,4); |
1
|
INSERT OVERWRITE INTO test_ft PARTITION FOR(p1_3,p2_3,p3_3) VALUES(p1_3,p2_3,p3_3,4); |