LOAD DATA

LOAD DATA 语句用于将数据批量加载到 TiDB 表中。

在 v7.0.0 版本支持以下功能:

  • 支持从 S3、GCS 导入数据
  • 新增参数 FIELDS DEFINED NULL BY

语法图

LoadDataStmt
LOADDATALocalOptINFILEstringLitDuplicateOptINTOTABLETableNameCharsetOptFieldsLinesIgnoreLinesColumnNameOrUserVarListOptWithBracketsLoadDataSetSpecOpt
LocalOpt
LOCAL
Fields
TERMINATEDBYstringLitOPTIONALLYENCLOSEDBYstringLitESCAPEDBYstringLitDEFINEDNULLBYstringLitOPTIONALLYENCLOSED

参数说明

LOCAL

你可以使用 LOCAL 来指定导入位于客户端的数据文件,此时传入文件参数必须为客户端文件系统路径。

S3/GCS 路径

如果你不指定 LOCAL,则文件参数必须是有效的 S3/GCS URI 路径,详见外部存储

当数据文件存储在 S3/GCS 上时,你可以导入单个文件,也可使用通配符 * 来匹配需要导入的多个文件。注意通配符不会递归处理子目录下相关的文件。以数据存储在 S3 为例,示例如下:

  • 导入单个文件:s3://<bucket-name>/path/to/data/foo.csv
  • 导入指定路径下的所有文件:s3://<bucket-name>/path/to/data/*
  • 导入指定路径下的所有以 .csv 结尾的文件:s3://<bucket-name>/path/to/data/*.csv
  • 导入指定路径下所有以 foo 为前缀的文件:s3://<bucket-name>/path/to/data/foo*
  • 导入指定路径下以 foo 为前缀、以 .csv 结尾的文件:s3://<bucket-name>/path/to/data/foo*.csv

FieldsLinesIgnore Lines

你可以使用 FieldsLines 参数来指定如何处理数据格式:

  • 使用 FIELDS TERMINATED BY 来指定数据的分隔符号。
  • 使用 FIELDS ENCLOSED BY 来指定数据的包围符号。
  • 如果你希望以某个字符为结尾来切分行数据,可以使用 LINES TERMINATED BY 来指定行的终止符。

可以使用 DEFINED NULL BY 来指定数据文件中如何表示 NULL 值。

  • 与 MySQL 行为一致,如果 ESCAPED BY 不为空时,例如是默认值 \,那么 \N 会被认为是 NULL 值。
  • 如果使用 DEFINED NULL BY,例如 DEFINED NULL BY 'my-null'my-null 会被认为是 NULL 值。
  • 如果使用 DEFINED NULL BY ... OPTIONALLY ENCLOSED,例如 DEFINED NULL BY 'my-null' OPTIONALLY ENCLOSEDmy-null"my-null"(假设 ENCLOSED BY '"')会被认为是 NULL 值。
  • 如果没有使用 DEFINED NULL BY 或者 DEFINED NULL BY ... OPTIONALLY ENCLOSED,但使用了 ENCLOSED BY,例如 ENCLOSED BY '"',那么 NULL 会被认为是 NULL 值。这个行为与 MySQL 一致。
  • 其他情况不会被认为是 NULL 值。

例如对于以下格式的数据:

"bob","20","street 1"\r\n "alice","33","street 1"\r\n

如果想分别提取 bob20street 1,可以指定数据的分隔符号为 ',',数据的包围符号为 '\"'。可以写成:

FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n'

当不指定处理数据的参数时,将按以下参数处理:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY ''

你可以通过 IGNORE <number> LINES 参数来忽略文件开始的 <number> 行。例如,可以使用 IGNORE 1 LINES 来忽略文件的第一行。

示例

通过 LOAD DATA 导入数据,指定数据的分隔符为逗号,忽略包围数据的引号,并且忽略文件的第一行数据。

如果此时遇到 ERROR 1148 (42000): the used command is not allowed with this TiDB version 报错信息。可以参考文档解决:ERROR 1148 (42000): the used command is not allowed with this TiDB version 问题的处理方法

LOAD DATA LOCAL INFILE '/mnt/evo970/data-sets/bikeshare-data/2017Q4-capitalbikeshare-tripdata.csv' INTO TABLE trips FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (duration, start_date, end_date, start_station_number, start_station, end_station_number, end_station, bike_number, member_type);
Query OK, 815264 rows affected (39.63 sec) Records: 815264 Deleted: 0 Skipped: 0 Warnings: 0

LOAD DATA 也支持使用十六进制 ASCII 字符表达式或二进制 ASCII 字符表达式作为 FIELDS ENCLOSED BYFIELDS TERMINATED BY 的参数。示例如下:

LOAD DATA LOCAL INFILE '/mnt/evo970/data-sets/bikeshare-data/2017Q4-capitalbikeshare-tripdata.csv' INTO TABLE trips FIELDS TERMINATED BY x'2c' ENCLOSED BY b'100010' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (duration, start_date, end_date, start_station_number, start_station, end_station_number, end_station, bike_number, member_type);

以上示例中 x'2c' 是字符 , 的十六进制表示,b'100010' 是字符 " 的二进制表示。

MySQL 兼容性

TiDB 中的 LOAD DATA 语句语法上兼容 MySQL(除字符集选项被解析但会被忽略以外)。若发现任何语法兼容性差异,请尝试 TiDB 支持资源

另请参阅