LOAD DATA
LOAD DATA
语句用于批量将数据加载到 TiDB 表中。
从 TiDB v7.0.0 开始,LOAD DATA
SQL 语句支持以下功能:
- 支持从 S3 和 GCS 导入数据
- 新增参数
FIELDS DEFINED NULL BY
语法
- LoadDataStmt
- LocalOpt
- Fields
LoadDataStmt ::=
'LOAD' 'DATA' LocalOpt 'INFILE' stringLit DuplicateOpt 'INTO' 'TABLE' TableName CharsetOpt Fields Lines IgnoreLines ColumnNameOrUserVarListOptWithBrackets LoadDataSetSpecOpt
LocalOpt ::= ('LOCAL')?
Fields ::=
('TERMINATED' 'BY' stringLit
| ('OPTIONALLY')? 'ENCLOSED' 'BY' stringLit
| 'ESCAPED' 'BY' stringLit
| 'DEFINED' 'NULL' 'BY' stringLit ('OPTIONALLY' 'ENCLOSED')?)?
参数说明
LOCAL
你可以使用 LOCAL
指定要导入的客户端本地数据文件,此时文件参数必须为客户端文件系统路径。
如果你在使用 TiDB Cloud,想通过 LOAD DATA
语句加载本地数据文件,连接 TiDB Cloud 时需要在连接字符串中添加 --local-infile
选项。
以下是 TiDB Cloud Serverless 的连接字符串示例:
mysql --connect-timeout 15 -u '<user_name>' -h <host_name> -P 4000 -D test --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ssl/cert.pem -p<your_password> --local-infile以下是 TiDB Cloud Dedicated 的连接字符串示例:
mysql --connect-timeout 15 --ssl-mode=VERIFY_IDENTITY --ssl-ca=<CA_path> --tls-version="TLSv1.2" -u root -h <host_name> -P 4000 -D test -p<your_password> --local-infile
S3 和 GCS 存储
如果你未指定 LOCAL
,则文件参数必须为合法的 S3 或 GCS 路径,具体格式详见 外部存储。
当数据文件存储在 S3 或 GCS 上时,你可以导入单个文件,也可以使用通配符 *
匹配多个文件进行导入。注意,通配符不会递归处理子目录下的文件。以下是一些示例:
- 导入单个文件:
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
Fields
、Lines
和 Ignore Lines
你可以通过 Fields
和 Lines
参数指定数据格式的处理方式。
FIELDS TERMINATED BY
:指定字段分隔符。FIELDS ENCLOSED BY
:指定字段包裹字符。LINES TERMINATED BY
:指定行结束符,如果你希望以某个字符结尾。
你可以使用 DEFINED NULL BY
指定数据文件中 NULL 值的表示方式。
- 与 MySQL 行为一致,如果
ESCAPED BY
不为 null,例如使用默认值\
,则\N
会被视为 NULL 值。 - 如果你使用
DEFINED NULL BY
,如DEFINED NULL BY 'my-null'
,则my-null
会被视为 NULL 值。 - 如果你使用
DEFINED NULL BY ... OPTIONALLY ENCLOSED
,如DEFINED NULL BY 'my-null' OPTIONALLY ENCLOSED
,则my-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
如果你想提取 bob
、20
和 street 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 BY
和 FIELDS 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'
是 "
字符的二进制表示。
以下示例展示了如何使用 LOAD DATA INFILE
语句从 Amazon S3 向 TiDB Cloud Dedicated 集群导入数据:
LOAD DATA INFILE 's3://<your-bucket-name>/your-file.csv?role_arn=<The ARN of the IAM role you created for TiDB Cloud import>&external_id=<TiDB Cloud external ID (optional)>'
INTO TABLE <your-db-name>.<your-table-name>
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
MySQL 兼容性
LOAD DATA
语句的语法与 MySQL 兼容,字符集相关选项会被解析但会被忽略。如果你发现任何语法兼容性差异,可以报告 bug。