# LOAD DATA #### 功能描述 将文件中的数据导入到数据库指定表中。 #### 注意事项 - 仅在MYSQL兼容模式（sql_compatibility = 'MYSQL'）下且开启b_format_version='5.7'和b_format_dev_version='s2'参数后可正常使用该语法。 - 当在其他兼容模式下或未设置兼容性参数（b_format_version='5.7'和b_format_dev_version='s2'）时会返回若干条语句，其主要用于gs_loader工具内部使用。 - [原生语法]仅供gs_loader工具内部调用，不建议用户直接使用，建议用户按[推荐语法]进行使用。 - LOAD DATA语法权限相关GUC参数与COPY FROM语法[注意事项](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/gaussdb-08-0236.html#ZH-CN_TOPIC_0000002793707393__zh-cn_topic_0059778766_sc996fd2c14664963bae3e1e0ce655441)一致。 - LOAD DATA语法需要拥有表的INSERT和DELETE权限。 - 执行LOAD DATA语法写入表中的数据若无法转换为表中数据类型格式时，将导致导入失败。 - LOAD DATA只能用于表，不能用于视图。 #### 推荐语法 ``` LOAD DATA [LOCAL] INFILE 'file_name' [REPLACE | IGNORE] INTO TABLE tbl_name [PARTITION (partition_name [, ...])] [CHARACTER SET charset_name] [{FIELDS | COLUMNS} {TERMINATED BY 'string' | [OPTIONALLY] ENCLOSED BY 'char' | ESCAPED BY 'char'}] [LINES {STARTING BY 'string' | TERMINATED BY 'string'}] [IGNORE number {LINES | ROWS}] [(col_name_or_user_var [, ...])] [SET {col_name={expr | DEFAULT}} [, ...]]; ``` ![](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/figure/zh-cn_image_0000002793788015.png "点击放大") #### 原生语法 ``` LOAD [DATA] [LOCAL] [{INFILE 'file_name' | CHARACTERSET 'name' | STR 'name' } [,...]] [REPLACE | IGNORE | APPEND | TRUNCATE | INSERT ] INTO TABLE tbl_name [PARTITION (partition_name [, ...])] [REPLACE | APPEND | TRUNCATE | INSERT ] [load_item [,...]] [SET {col_name={expr | DEFAULT}} [,...]]; ``` ![](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/figure/zh-cn_image_0000002764309278.png "点击放大") - 其中load_item为导入文件的具体选项，其语法格式为： ``` { TRAILING NULLCOLS | IGNORE num {ROWS | LINES} | CHARACTER SET 'str'| LINES {{TERMINATED BY 'str' | STARTING BY 'str'} [, ...]} | LOAD_SESSION_ID 'str' | {field_option [,...]} | load_when_option | ( load_column_usrvar_list ) } ``` ![](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/figure/zh-cn_image_0000002793707819.png "点击放大") - 其中filed_option为导入文件的格式相关选项，其语法格式为： ``` { {FIELDS | COLUMNS} {{TERMINATED [BY] 'str' | [OPTIONALLY] ENCLOSED BY 'char' | ESCAPED BY 'char' | CSV } [, ...]} } ``` ![](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/figure/zh-cn_image_0000002764469204.png "点击放大") - 其中load_when_option为导入文件时使用的具体过滤选项，其语法格式为： ``` { WHEN {{{( start - end ) | (start) | col_name } cmp_op 'str' }[AND ...]} } ``` ![](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/figure/zh-cn_image_0000002793788017.png "点击放大") - 其中load_column_usrvar_list为在导入数据时，对于列进行赋值处理、生成序列值、过滤等操作的语法选项，其语法格式为： ``` { col_name CONSTANT 'str' | col_name SYSDATE | col_name SEQUENCE ( {num | MAXVALUE | ROWS} [ {, num} [, ...]] ) | col_name FILLER [data_type] | usr_var | col_name [ POSTITION ( start - end ) ] [ data_type ] [ NULLIF ( col_name = BLANKS )] [ name ] } ``` ![](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/figure/zh-cn_image_0000002764469206.png "点击放大") ![](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/public_sys-resources/note_3.0-zh-cn.png) 当开启[disable_unsupported_copy_load_options](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/gaussdb-08-0802.html#ZH-CN_TOPIC_0000002793707543__section138823479427)时，若使用[原生语法]选项将会报错。 #### 参数说明 - **LOCAL** 指定导入文件的位置。 不指定LOCAL时，若file_name为相对路径，则默认导入路径为数据目录。 若指定LOCAL参数则需要指定file_name为绝对路径，当指定为相对路径时默认导入路径为数据库二进制所在路径，即$GAUSSHOME/bin/。 - **REPLACE \| IGNORE** 控制导入文件数据冲突及错误处理的行为选项。 - 指定REPLACE选项 当导入数据与表中原有数据冲突时，替换冲突行数据；当文件中字段数小于指定表的字段数时，宽松模式下告警，严格模式下报错。 - 指定IGNORE选项 当导入数据与表中原有数据冲突时，跳过冲突行数据；当文件中字段数小于指定表的字段数时，按默认值填充表中未指定的字段，并产生告警。 - 不指定REPLACE与IGNORE选项 - 不指定LOCAL选项时，当导入数据与表中原有数据冲突时报错；当文件中字段数小于指定表的字段数时，宽松模式下告警，严格模式下报错。 - 指定LOCAL选项时，行为与指定IGNORE选项一致。 - **PARTITION** 当导入表为分区表时，此参数用来指定分区。若数据与指定分区范围不一致，则报错。 - **CHARACTER SET** 指定数据文件的编码格式名称，缺省为当前客户端编码格式。 - **FIELDS \| COLUMNS** - **TERMINATED BY** 指定两列之间分隔符，缺省为"\\t"。 ![](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/public_sys-resources/note_3.0-zh-cn.png) 指定换行符不能与分隔符相同。 - **\[OPTIONALLY\] ENCLOSED BY** 指定引号字符，缺省为''。 OPTIONALLY参数为可选参数，无实际作用。 引号符仅支持单字符，不支持字符串。 - **ESCAPED BY** 指定转义符，缺省为"\\"。 ![](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/public_sys-resources/note_3.0-zh-cn.png) - 转义字符仅支持单字符，不支持字符串。 - 如果转义符非空，当处理到转义符时，会判断转义符及其后的字符序列是否满足指定的转义序列。如果满足指定的转义序列，则将转义符及其后的字符转义成指定字符。如果不满足指定的转义序列，则会忽略该转义符并继续导入数据。支持的转义序列受GUC参数[load_data_behavior_compat_options](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/gaussdb-08-0802.html#ZH-CN_TOPIC_0000002793707543__section41023173287)是否配置new_escape_string选项控制。 - **LINES** - **STARTING BY** 指定导入数据文件起始字段样式。 - **TERMINATED BY** 指定导入数据文件换行符样式。 ![](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/public_sys-resources/note_3.0-zh-cn.png) GUC参数[load_data_behavior_compat_options](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/gaussdb-08-0802.html#ZH-CN_TOPIC_0000002793707543__section41023173287)的配置选项new_escape_string，控制是否对指定的行终结符做转义处理。 - **IGNORE** 指定数据导入时，跳过数据文件的前number行。 - **col_name_or_user_var** 可选的待复制字段列表。 取值范围：如果没有声明字段列表，将使用所有字段。 ![](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/public_sys-resources/note_3.0-zh-cn.png) - 指定列参数不支持重复指定列。 - LOAD DATA语法指定列时，col_name_or_user_var仅支持指定为表中存在列，不支持指定为用户变量。 - **SET** 指定列值，可以指定为表达式或DEFAULT。 ![](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/public_sys-resources/note_3.0-zh-cn.png) - 表达式中不支持列名。 - 若表达式结果类型与被赋值列对应类型之间不存在隐式转换函数则报错。 #### 示例 ``` -- 环境准备。 gaussdb=# CREATE DATABASE test_load WITH DBCOMPATIBILITY = 'MYSQL'; gaussdb=# \c test_load test_load=# SET b_format_version='5.7'; test_load=# SET b_format_dev_version='s2'; -- 准备待导入的文件。 test_load=# CREATE TABLE load_data_tmp1(a int, b int); test_load=# INSERT INTO load_data_tmp1 VALUES(1,1),(2,2),(3,3); test_load=# \copy load_data_tmp1 to '/home/omm/load1.csv'; test_load=# CREATE TABLE load_data_tmp2(a int, b int); test_load=# INSERT INTO load_data_tmp2 VALUES(1,2); test_load=# \copy load_data_tmp2 to '/home/omm/load2.csv'; test_load=# CREATE TABLE load_data_tmp3(a int, b int); test_load=# INSERT INTO load_data_tmp3 VALUES(4,4),(5,5); test_load=# \copy load_data_tmp3 to '/home/omm/load3.csv'; test_load=# CREATE TABLE load_data_tmp4(a char(50)); test_load=# INSERT INTO load_data_tmp4 VALUES('"load test quote"'), ('\'load test single_quote\''); test_load=# \copy load_data_tmp4 to '/home/omm/load4.csv'; -- 创建表。 test_load=# CREATE TABLE load_data_tbl1(load_col1 INT UNIQUE, load_col2 INT, load_col3 CHAR(10)); -- 向表中插入一条数据。 test_load=# INSERT INTO load_data_tbl1 VALUES(0,0,'load0'); -- 从文件/home/omm/load1.csv中复制数据到load_data_tbl表,指定列名，设置.load_col3列值统一为"load"。 test_load=# LOAD DATA INFILE '/home/omm/load1.csv' INTO TABLE load_data_tbl1(load_col1, load_col2) SET load_col3 = 'load'; --后面导入数据load_col3列值均为'load' test_load=# SELECT * FROM load_data_tbl1 ORDER BY load_col1; load_col1 | load_col2 | load_col3 -----------+-----------+----------- 0 | 0 | load0 1 | 1 | load 2 | 2 | load 3 | 3 | load (4 rows) -- 从文件/home/omm/load2.csv中复制数据到load_data_tbl表，指定IGNORE忽略冲突。 test_load=# LOAD DATA INFILE '/home/omm/load2.csv' IGNORE INTO TABLE load_data_tbl1; -- 表load_data_tbl1中数据不变，冲突数据跳过。 test_load=# SELECT * FROM load_data_tbl1 ORDER BY load_col1; load_col1 | load_col2 | load_col3 -----------+-----------+----------- 0 | 0 | load0 1 | 1 | load 2 | 2 | load 3 | 3 | load (4 rows) -- 创建分区表。 test_load=# CREATE TABLE load_data_tbl2 ( load_col_col1 INT, load_col_col2 INT ) PARTITION BY RANGE (load_col_col2) ( PARTITION load_p1 VALUES LESS THAN(3), PARTITION load_p2 VALUES LESS THAN(9), PARTITION load_p3 VALUES LESS THAN(MAXVALUE) ); -- 从文件/home/omm/load3.csv中复制数据到load_data_tbl2表，指定PARTITION。 test_load=# LOAD DATA INFILE '/home/omm/load3.csv' INTO TABLE load_data_tbl2 PARTITION (load_p2); --数据导入到load_data_tbl2表中指定分区 test_load=# SELECT * FROM load_data_tbl2; load_col_col1 | load_col_col2 ---------------+--------------- 4 | 4 5 | 5 (2 rows) -- 创建表。 test_load=# CREATE TABLE load_data_tbl3(load_col_col1 CHAR(30)); -- 从文件/home/omm/load4.csv中复制数据到load_data_tbl3表，指定FIELDS ENCLOSED BY。 test_load=# LOAD DATA INFILE '/home/omm/load4.csv' INTO TABLE load_data_tbl3 FIELDS ENCLOSED BY '"'; -- 数据"load test quote"双引号被去掉，'load test single_quote'单引号保留。 test_load=# SELECT * from load_data_tbl3; load_col_col1 -------------------------------- load test quote 'load test single_quote' (2 rows) -- 删除表。 test_load=# DROP TABLE load_data_tmp1; test_load=# DROP TABLE load_data_tmp2; test_load=# DROP TABLE load_data_tmp3; test_load=# DROP TABLE load_data_tmp4; test_load=# DROP TABLE load_data_tbl1; test_load=# DROP TABLE load_data_tbl2; test_load=# DROP TABLE load_data_tbl3; ```