虚谷文档 v1.0.0

适用虚谷数据库版本

v12.10.13



适用虚谷数据库版本

v12.10.13


LOAD导入文件数据到表 ​

📄字数 5.1K
👁️阅读量 加载中...

概述 ​

LOAD 命令用于将结构化数据文件导入数据库表。相比 SQLLDR 和 TABLDR 等导入工具，LOAD 在数据源位置、约束处理、文件格式适配等方面提供了更灵活的选项，适用于多种数据导入场景。

主要特性 ​

  • 双端数据源支持：通过 LOCAL 关键字区分数据文件位于客户端还是服务端，适应不同部署环境。
  • 唯一约束冲突处理：提供 IGNORE 与 REPLACE 两种策略，控制主键/唯一索引冲突时的行为，无需手动预处理数据。
  • 包裹符支持：支持 ENCLOSED BY 指定字段包裹符，可处理字段内容包含分隔符的情况，提高解析鲁棒性。
  • 行忽略机制：支持跳过文件头部若干行，便于处理含表头或注释行的数据文件。
  • 乱序导入支持：允许通过列映射将文件中的字段按任意顺序匹配到表列，并支持表达式转换（如 NULLIF、函数调用等）。
  • 性能考量：与 TABLDR 相比，LOAD 在解析和转换层面引入了更多灵活性，因此导入吞吐量略低；但作为通用导入工具，其适用性更广。

关于 SQLLDR 与 TABLDR 的详细说明，可参考：

一、 语法 ​

1.1 语法格式 ​

1.2 参数说明 ​

参数项说明
LOCAL可选关键字。若指定，表示数据文件位于客户端；省略则表示文件位于数据库服务端。
file_path数据文件的绝对路径或相对路径（相对于服务端或客户端的工作目录）。
table_name目标表名，支持模式限定（如 schema.table）。
IGNORE / REPLACE唯一约束冲突处理模式：
- IGNORE：跳过导致唯一性冲突的行，继续处理后续行。
- REPLACE：删除已有冲突行，插入新行（需满足引用完整性）。
若未指定且冲突发生，则事务回滚并报错。
row_end_char行分隔符。支持字符串常量、十六进制常量（如 X'0a'）或二进制常量。常用值：X'0a'（换行）、X'0d'（回车）、X'0d0a'（回车换行）。
col_end_char列分隔符。同样支持字符串或十六进制常量，例如 '
ENCLOSED BY可选字段包裹符。若字段值中包含列分隔符或行分隔符，可用包裹符将字段整体括起，解析时自动去除。常用字符为双引号 '"'。
IGNORE row_num可选行忽略数。row_num 表示文件开头需要跳过的行数，适用于跳过表头或注释行。
LoadColumnList定义从数据文件中读取的字段列表。每个元素可以是：
- 表列名（ColLabel）：表示该列的值直接取自文件对应位置。
- 变量（@variable）：表示将该位置的值存入用户变量，供后续 SET 子句使用。
SET update_target_list定义表列与变量（或表达式）之间的映射关系。每个赋值项左边为目标列名，右边可以是变量、表达式或 DEFAULT 关键字。支持在赋值时进行数据转换（如 NULLIF、类型转换函数等）。

1.3 实现与使用约束 ​

  1. 字符集一致性：数据文件的字符集应与客户端字符集保持一致，否则可能导致解析错误或乱码。服务端导入时，文件字符集需与数据库服务端字符集匹配。

  2. 网络稳定性（LOCAL模式）：从客户端导入时，数据通过客户端/服务端协议传输，应确保网络连接稳定，避免传输中断导致导入失败。

  3. 分隔符准确性：行分隔符与列分隔符必须严格匹配文件实际格式。若分隔符设置错误，导入过程将无法正确解析记录，并最终报错。

  4. 包裹符嵌套限制：目前仅支持单字符包裹符，且包裹符内若包含转义字符，需由前端客户端进行转义处理。

  5. 变量作用域：LoadColumnList 中定义的 @variable 仅在当前 LOAD 语句的 SET 子句中有效，不保留至后续语句。

二、 示例 ​

  • 服务端与客户端导入
sql
SQL> CREATE TABLE stream_full_type_compare(C01 TINYINT,
     C02 SMALLINT,
     C03 INTEGER,
     C04 INT,
     C05 BIGINT,
     C06 DOUBLE,
     C07 FLOAT,
     C08 NUMERIC,
     C09 NUMERIC(10,2),
     C10 CHAR(10),
     C11 CHAR,
     C12 VARCHAR(30),
     C13 VARCHAR,
     C14 DATE,
     C15 DATETIME,
     C16 TIMESTAMP,
     C17 DATETIME WITH TIME ZONE,
     C18 TIMESTAMP WITH TIME ZONE,
     C19 TIME,
     C20 TIME WITH TIME ZONE,
     C21 INTERVAL YEAR,
     C22 INTERVAL MONTH,
     C23 INTERVAL DAY,
     C24 INTERVAL HOUR,
     C25 INTERVAL MINUTE,
     C26 INTERVAL SECOND,
     C27 INTERVAL YEAR TO MONTH,
     C28 INTERVAL DAY TO MINUTE,
     C29 INTERVAL DAY TO SECOND,
     C30 INTERVAL HOUR TO MINUTE,
     C31 INTERVAL HOUR TO SECOND,
     C32 INTERVAL MINUTE TO SECOND,
     C33 GUID,
     C34 BOOLEAN);

--服务端导入，即文件在服务端环境
SQL> LOAD DATAFILE 'D:\XGDBMS\full_datatype_2w.txt'  INTO TABLE stream_full_type_compare RECORDS DELIMITED BY X'0a' FIELDS TERMINATED BY '|';       

--加上LOCAL选项即客户端导入，即文件在客户端环境
SQL> LOAD LOCAL DATAFILE 'D:\XGDBMS\full_datatype_2w.txt'  INTO TABLE stream_full_type_compare RECORDS DELIMITED BY X'0a' FIELDS TERMINATED BY '|';
  • 设置约束选项
sql

SQL> alter table stream_full_type_compare add constraint stream_full_type_compare_pkey primary key (C03);

--首次导入不涉及约束问题
SQL> LOAD DATAFILE 'D:\XGDBMS\full_datatype_2w.txt'  INTO TABLE stream_full_type_compare RECORDS DELIMITED BY X'0a' FIELDS TERMINATED BY '|';        

--第二次导入存在约束问题报错
SQL> LOAD DATAFILE 'D:\XGDBMS\full_datatype_2w.txt'  INTO TABLE stream_full_type_compare RECORDS DELIMITED BY X'0a' FIELDS TERMINATED BY '|';        

Error: [E18074] 向表中导入文件数据错误

--添加ignore选项，忽略冲突行
SQL> LOAD DATAFILE 'D:\XGDBMS\full_datatype_2w.txt'  INTO TABLE stream_full_type_compare IGNORE RECORDS DELIMITED BY X'0a' FIELDS TERMINATED BY '|';   

--添加replace选项，会删除冲突行插入新行，通过查询行号变化
SQL> LOAD DATAFILE 'D:\XGDBMS\full_datatype_2w.txt'  INTO TABLE stream_full_type_compare IGNORE RECORDS DELIMITED BY X'0a' FIELDS TERMINATED BY '|';
  • 忽略行
sql
--忽略998行数据
SQL> LOAD DATAFILE 'D:\XGDBMS\full_datatype_2w.txt'  INTO TABLE stream_full_type_compare IGNORE RECORDS DELIMITED BY X'0a' FIELDS TERMINATED BY ','  IGNORE 998 ; 

-- 存在数据文件有表头，需忽略表头导入后续数据
SQL> LOAD DATAFILE 'D:\XGDBMS\full_datatype_2w.txt'  INTO TABLE stream_full_type_compare IGNORE RECORDS DELIMITED BY X'0a' FIELDS TERMINATED BY ','  IGNORE 1 ;
  • 指定行分割符合列分隔符
sql
--依据实际情况设置分隔符，一般来说，csv文件行分隔符为/n，列分隔符为逗号；txt文件列分隔符伟|。
--行分隔符为/n，列分隔符为，
SQL> LOAD DATAFILE 'D:\XGDBMS\full_datatype_2w.txt'  INTO TABLE stream_full_type_compare IGNORE RECORDS DELIMITED BY X'0a' FIELDS TERMINATED BY ',' ;   

Error: [E18074] 向表中导入文件数据错误

--行分隔符为/r/n，列分隔符为|
SQL> LOAD DATAFILE 'D:\XGDBMS\full_datatype_2w.txt'  INTO TABLE stream_full_type_compare IGNORE RECORDS DELIMITED BY X'0d0a' FIELDS TERMINATED BY '|' ;
  • 指定字符串包裹符
sql
-- 根据实际情况，字符串包裹符一般用来解决文件中字符串有特殊字符的情况。例如$N(cost_te,cost_yeb1)这个字段，本身有逗号，但是逗号又是列分隔符，如果不用特殊符号包裹起来，会存在冲突导致数据导入失败，而特殊符号，又需要通过字符串包裹符来识别。
--指定字符串包裹符为"
SQL> LOAD DATAFILE 'D:\XGDBMS\full_datatype_2w.txt'  INTO TABLE stream_full_type_compare IGNORE RECORDS DELIMITED BY X'0d0a' FIELDS TERMINATED BY '|'  ENCLOSED BY '"';
  • 设置字段序
sql
-- 如果表的字段和需要导入的文件字段序不一致，可以采用设置字段序的方式

SQL> LOAD DATAFILE 'D:\XGDBMS\full_datatype_2w.txt'  INTO TABLE stream_full_type_compare RECORDS DELIMITED BY X'0a' FIELDS TERMINATED BY ',' (w_id,@w_ytd,@w_tax,@w_name,
     @w_street_1,@w_street_2,@w_city,@w_state,@w_zip) SET w_ytd = @w_ytd,w_tax = @w_tax,w_name = NULLIF(@w_name,''),w_street_1 = @w_street_1, w_street_2 = @w_street_2, w_city = @w_city, w_state = @w_state, w_zip = @w_zip;

-- 括号里面@w_state这种是变量的方式，且必须以变量的形式设置，后面需要set进去值，w_id是本身在文件中找的到的字段名，也和表里的列名能够对应，所以后面不需要在set值进去。
-- w_street_1 = @w_street_1是将表的列设置到变量中，用于后续给表列插入值。
-- 在set的时候也可以对变量进行相关的处理，例如例子中的NULLIF就是指定变量对应的值为文件中的字段为null，置为空串。
-- 除此之外，你还可以选择给表列附表达式值，例如,w_ytd = current_datetime(),给这一列设置当前时间
  • 选取部分列进行导入
sql
-- 也可以选取表的部分列进行导入，通过设置变量的方式。
SQL> LOAD DATAFILE 'D:\XGDBMS\full_datatype_2w.txt'  INTO TABLE stream_full_type_compare RECORDS DELIMITED BY X'0a' FIELDS TERMINATED BY ',' (w_id,@w_ytd,@w_tax,@w_name,
     @w_street_1,@w_street_2) SET w_ytd = @w_ytd,w_tax = @w_tax,w_name = NULLIF(@w_name,''),w_street_1 = @w_street_1, w_street_2 = @w_street_2;