# INSERT #### 功能描述 向表中添加一行或多行数据。 #### 注意事项 - 使用INSERT请遵守[INSERT开发规范](https://support.huaweicloud.com/devg-dws/dws_04_0116.html)。 - 只有拥有表INSERT权限的用户，才可以向表中插入数据。 - 如果使用RETURNING子句，用户必须要有该表的SELECT权限。 - 如果使用QUERY子句插入来自查询里的数据行，用户还需要拥有在查询里使用的表的SELECT权限。 - 如果使用OVERWRITE子句覆盖式插入数据，用户还需要拥有该表的SELECT和TRUNCATE权限（因为OVERWRITE操作是先清空目标表原有数据）。 - 当连接到TD兼容的数据库时，[td_compatible_truncation](https://support.huaweicloud.com/devg-dws/dws_04_0931.html)参数设置为on时，将启用超长字符串自动截断功能，在后续的INSERT语句中（不包含外表的场景下），对目标表中char和varchar类型的列上插入超长字符串时，系统会自动按照目标表中相应列定义的最大长度对超长字符串进行截断。 ![](https://support.huaweicloud.com/sqlreference-dws/public_sys-resources/notice_3.0-zh-cn.png) 如果向字符集为字节类型编码（SQL_ASCII，LATIN1等）的数据库中插入多字节字符数据（如汉字等），且字符数据跨越截断位置，这种情况下，按照字节长度自动截断，自动截断后会在尾部产生非预期结果。如果用户有对于截断结果正确性的要求，建议用户采用UTF8等能够按照字符截断的输入字符集作为数据库的编码集。 #### 语法格式 ``` [ 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 ] ``` #### 参数说明 表1INSERT参数说明 | 参数 | 描述 | 取值范围或示例 | |:---|:---|:---| | 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语句。 | [示例：使用WITH语句生成临时表再插入另一张表] | | plan_hint子句 | 以/\*+ \*/的形式在关键字后，用于对指定语句块生成的计划进行hint调优，详细用法请参见[使用Plan Hint进行调优](https://support.huaweicloud.com/devg-dws/dws_04_0454.html)章节。 | - | | IGNORE | 用于主键或者唯一约束冲突时忽略冲突的数据。 详细介绍参见[UPSERT](https://support.huaweicloud.com/sqlreference-dws/dws_06_0237.html)。 | [示例：使用IGNORE忽略主键冲突] | | OVERWRITE | **仅8.1.1及以上版本支持**，用于标识覆盖式插入方式，使用此种插入方式执行结束后，目标源数据被清空，只存在新插入的数据。 OVERWRITE支持指定列插入的功能，其他列为默认值，若无默认值则为NULL。 INSERT OVERWRITE约束： - OVERWRITE不要和INSERT INTO这类实时写入的操作并发，否则实时写入数据有被意外清理的风险。 - 复制表（Replication）不支持INSERT OVERWRITE。OVERWRITE适用于大批量数据导入场景，不建议用于少量数据的插入场景。 - 避免对同一张表执行并发insert overwrite操作，否则会出现类似报错"tuple concurrently updated."。 - 如果集群正在扩缩容，且INSERT OVERWRITE的写入表需要执行数据重分布，则INSERT OVERWRITE会清除当前数据，并自动将插入的数据按扩缩容后的节点来进行数据分布。如果INSERT OVERWRITE和该表的数据重分布过程同时执行，INSERT OVERWRITE会中断该表的数据重分布过程。 - OVERWRITE支持覆盖式插入OBS外表，此方式会删除原目录中数据文件。 - 外表支持OVERWRITE单分区，支持Value多级分区，该功能仅9.1.0.100及以上版本支持。 示例：INSERT OVERWRITE into table_name partition for (p1_value, p2_value...) ... - 对同一张表同时执行INSERT OVERWRITE和ALTER TABLE...ADD PARTITION操作时，偶发可能会出现不同节点的分区数量不一致的情况，从而导致INSERT OVERWRITE报错。遇到这种情况时，建议重新执行INSERT OVERWRITE语句，即可解决。 | - | | table_name | 要插入数据的目标表名。 | 已存在的表名。 | | AS | 用于给目标表table_name指定别名。alias即为别名的名字。 | - | | column_name | 目标表中的字段名： - 字段名可以用子字段名或者数组下标修饰。 - 没有在字段列表中出现的每个字段，将由系统默认值，或者声明时的默认值填充，若都没有则用NULL填充。例如，向一个复合类型中的某些字段插入数据，其他字段将是NULL。 - 目标字段（column_name）可以按顺序排列。如果没有列出任何字段，则默认全部字段，且顺序为表声明时的顺序。 - 如果value子句和query中只提供了N个字段，则目标字段为前N个字段。 - value子句和query提供的值在表中从左到右关联到对应列。 | 已存在的字段名。 | | partition_name | 分区名。 | 已存在的分区名。 | | partition_key_value | 分区键值。 通过PARTITION FOR ( partition_key_value \[, ...\] )子句指定的这一组值，可以唯一确定一个分区。支持指定多个分区键值，多个分区键值以英文逗号分隔。 | - | | expression | 赋予对应column的一个有效表达式或值： - 向表中字段插入单引号时需要使用单引号自身进行转义。 - 如果插入行的表达式不是正确的数据类型，系统试图进行类型转换，若转换不成功，则插入数据失败，系统返回错误信息。 | [示例：向某列中插入一个表达式或值] | | DEFAULT | 对应字段名的缺省值。 | 如果没有缺省值，则为NULL，参见[示例：写入数据时对某个字段使用默认值]。 | | query | 一个查询语句（SELECT语句），将查询结果作为插入的数据。 | [示例：将查询结果插入表] | | ON DUPLICATE KEY | 用于主键或者唯一约束冲突时更新冲突的数据。 duplicate_action指定更新列和更新的数据。 详细介绍参见[UPSERT](https://support.huaweicloud.com/sqlreference-dws/dws_06_0237.html)。 | [示例：使用ON DUPLICATE KEY更新主键或唯一约束冲突时插入数据] | | ON CONFLICT | 用于主键或者唯一约束冲突时忽略或者更新冲突的数据。 conflict_target用于指定列名index_column_name 、包含多个列名的表达式index_expression或约束名字constraint_name。用于从列名、包含多个列名的表达式或者约束名推断是否有唯一索引。其中index_column_name和index_expression遵循CREATE INDEX的索引列格式。 conflict_action指定主键或者唯一约束冲突时执行的策略。有两种： - DO NOTHING冲突忽略。 - DO UPDATE SET冲突更新。 后面指定更新列和更新的数据。 详细介绍参见[UPSERT](https://support.huaweicloud.com/sqlreference-dws/dws_06_0237.html)。 | [示例](https://support.huaweicloud.com/sqlreference-dws/dws_06_0237.html#ZH-CN_TOPIC_0000001811515753__section414485520537) | | RETURNING | 返回实际插入的行，RETURNING列表的语法与SELECT的输出列表一致。 | [示例：使用RETURNING返回插入结果] | | output_expression | INSERT命令在每一行都被插入之后用于计算输出结果的表达式。 | 该表达式可以使用table的任意字段。可以使用\*返回被插入行的所有字段。 | | output_name | 字段的输出名称。 | 字符串，符合标识符命名规范。详情请参见[标识符命名规范](https://support.huaweicloud.com/sqlreference-dws/dws_06_0007.html#ZH-CN_TOPIC_0000001811634529__section1475018612353)。 | #### 示例：插入单条或多条数据 创建表reason_t1： ``` DROP TABLE IF EXISTS reason_t1; CREATE TABLE reason_t1 ( TABLE_SK INTEGER , TABLE_ID VARCHAR(20) , TABLE_NA VARCHAR(20) ); ``` 向表中插入一条数据： ``` INSERT INTO reason_t1(TABLE_SK, TABLE_ID, TABLE_NA) VALUES (1, 'S01', 'StudentA'); ``` 向表中插入一条数据，和上一条语法等效： ``` INSERT INTO reason_t1 VALUES (1, 'S01', 'StudentA'); ``` 向表中插入TABLE_SK小于1的数据： ``` INSERT INTO reason_t1 SELECT * FROM reason_t1 WHERE TABLE_SK < 1; ``` 向表中插入多条数据： ``` INSERT INTO reason_t1 VALUES (1, 'S01', 'StudentA'),(2, 'T01', 'TeacherA'),(3, 'T02', 'TeacherB'); SELECT * FROM reason_t1 ORDER BY 1; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002318288577.png "点击放大") #### 示例：将查询结果插入表 将表reason_t1的数据插入到表reason_t1中： ``` INSERT INTO reason_t1 SELECT * FROM reason_t1; ``` #### 示例：使用RETURNING返回插入结果 插入表数据，并返回插入的数据对应的字段table_id和table_na的取值。 ``` INSERT INTO reason_t1 SELECT * FROM reason_t1 RETURNING table_id, table_na; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002318968541.png "点击放大") 插入数据，并返回插入数据的所有字段取值。 ``` INSERT INTO reason_t1 SELECT * FROM reason_t1 RETURNING *; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002318853121.png "点击放大") 插入数据，并返回重命名后的字段my_id取值。 ``` INSERT INTO reason_t1 VALUES (1, 'S01', 'StudentA') RETURNING table_id AS my_id; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002284205078.png "点击放大") #### 示例：使用INSERT OVERWRITE覆盖式插入 使用INSERT OVERWRITE更新表中的数据，即覆盖式插入数据（先清空目标表数据）： ``` INSERT OVERWRITE INTO reason_t1 values (5, 'S02', 'StudentB'); SELECT * FROM reason_t1 ORDER BY 1; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002318887361.png "点击放大") #### 示例：写入数据时对某个字段使用默认值 ``` 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。 ``` INSERT INTO products (id, name)VALUES (201, 'phone'); -- price字段将使用默认值0.00 ``` 插入一条数据，显式使用默认值。 ``` INSERT INTO products (id, name, price)VALUES (202, 'pad', DEFAULT); -- 显式使用默认值 SELECT * FROM products; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002283848380.png "点击放大") #### 示例：使用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忽略主键冲突 ``` DROP TABLE IF EXISTS users; CREATE TABLE users ( id INT PRIMARY KEY, username VARCHAR(50) ) DISTRIBUTE BY HASH (id); ``` 插入两条主键都是1的记录，提示冲突，无法执行。 ``` INSERT INTO users (id, username) VALUES (1, 'john_doe'), (2, 'jane_smith'), (1, 'duplicate_user'); --主键冲突，无法插入 ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002283677652.png "点击放大") 使用IGNORE忽略冲突，冲突数据被忽略跳过。 ``` INSERT IGNORE INTO users (id, username) VALUES (1, 'john_doe'), (2, 'jane_smith'), (1, 'duplicate_user'); -- 冲突数据会被忽略 SELECT * FROM users; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002283684468.png "点击放大") #### 示例：使用ON DUPLICATE KEY更新主键或唯一约束冲突时插入数据 ``` DROP TABLE IF EXISTS user_logins; CREATE TABLE user_logins (user_id int primary key, login_count int) DISTRIBUTE BY HASH (user_id); ``` 插入2条数据，第二条主键冲突，插入失败。 ``` INSERT INTO user_logins VALUES (1, 1); INSERT INTO user_logins VALUES (1, 1); --主键冲突，插入失败 SELECT * FROM user_logins; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002283915832.png "点击放大") 使用ON DUPLICATE KEY声明，如果主键冲突了，则更新login_count字段，使其加1。 ``` INSERT INTO user_logins VALUES (1, 1) ON DUPLICATE KEY UPDATE login_count = login_count + 1; SELECT * FROM user_logins; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002318531533.png "点击放大") #### 示例：向某列中插入一个表达式或值 ``` 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; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002283805422.png "点击放大") #### 示例：向分区表的指定分区插入数据 向分区表的指定分区插入数据： ``` 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); ``` 向分区外表中的指定分区插入数据： ``` CREATE FOREIGN TABLE test_ft(c1 text, c2 text, c3 text, c4 text) PARTITION BY (c1,c2,c3) AUTOMAPPED; ``` 向一级(p1_1)分区中插入数据： ``` INSERT OVERWRITE INTO test_ft PARTITION FOR(p1_1) VALUES(p1_1,2,3,4); ``` 向二级(p1_2,p2_2)分区中插入数据： ``` INSERT OVERWRITE INTO test_ft PARTITION FOR(p1_2,p2_2) VALUES(p1_2,p2_2,3,4); ``` 向三级(p1_3,p2_3,p3_3)分区中插入数据： ``` INSERT OVERWRITE INTO test_ft PARTITION FOR(p1_3,p2_3,p3_3) VALUES(p1_3,p2_3,p3_3,4); ``` #### 示例：MySQL兼容模式下，通过设置hive_compat_options实现插入空串不被当作0处理 DWS在MySQL兼容模式下进行数据操作时，如果插入空串，系统会默认**将空串当作0处理**。 在金融行业，例如从Hive导入数据到DWS场景中，如果将空串当作0处理会导致数据被篡改（金融场景下空串和0具有不同的意义），影响数据的准确性和可靠性。 如何确保数据在导入过程中的完整性和一致性？通过设置GUC参数[hive_compat_options](https://support.huaweicloud.com/devg-dws/dws_04_0931.html)值为**empty_str_to_null** 实现转换逻辑：**当插入空串时，系统会将空串转为NULL**，从而保持数据的一致性和准确性，下面模拟这个过程。 创建一个兼容模式为MySQL的数据库。 ``` DROP DATABASE IF EXISTS mysqldb; CREATE DATABASE mysqldb DBCOMPATIBILITY 'MYSQL'; ``` 重新连接新的数据库mysqldb，执行以下语句，并插入空串。 ``` CREATE TABLE test1 (a int, b int); INSERT INTO test1 VALUES (1, ''); ``` 查询数据，发现空串被设置为0。 ``` SELECT * FROM test1; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002490483460.png "点击放大") 将GUC参数hive_compat_options设置为**empty_str_to_null**。 ``` ALTER DATABASE mysqldb SET hive_compat_options TO empty_str_to_null; ``` 再次插入数据。 ``` INSERT INTO test1 VALUES (2, ''); ``` 查询数据，发现空串自动转换为NULL。 ``` SELECT * FROM test1; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002490647638.png "点击放大") #### 常见问题 - [分区表插入数据报错：inserted partition key does not map to any table partition](https://support.huaweicloud.com/trouble-dws/dws_09_0119.html) - [插入数据时，VARCHAR(n)类型的字段中存在中文字符，提示value too long for type character varying](https://support.huaweicloud.com/trouble-dws/dws_09_0043.html) #### 优化建议 - VALUES 通过insert语句批量插入数据时，建议将多条记录合并入一条语句中执行插入，以提高数据加载性能。例如，INSERT INTO sections VALUES (30, 'Administration', 31, 1900),(40, 'Development', 35, 2000), (50, 'Development' , 60 , 2001);