使用LOAD DATA导入本地数据 - 开发指南| 阿里云

本文介绍如何通过LOAD DATA将本地数据导入AnalyticDB for MySQL。

语法

LOAD DATA LOCAL '通过该命令导入本地文件'
    INFILE 'file_name' '本地文件的路径，包含文件地址和文件名。'
    [REPLACE | IGNORE] '导入数据遇到主键重复时用当前数据覆盖已有数据或者自动忽略失败行'
    INTO TABLE table_name 'AnalyticDB for MySQL中的目标表名'
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string'] '定义每行数据的列分隔符，默认为\t，与MySQL一致。'
        [[OPTIONALLY] ENCLOSED BY 'char'] '定义每列数据的enclosed符号'
    ]
    [LINES
        [TERMINATED BY 'string'] '定义行分隔符，默认为\n。'
    ]
    [IGNORE number {LINES | ROWS}] '设置导入数据时忽略开始的某几行'
    [(column_name_or_user_var
    [, column_name_or_user_var] ...)] '设置导入的列，如果不设置，默认按照列的顺序来导入数据'

参数

LOAD DATA LOCAL INFILE：表示要进行本地文件导入操作。
file_name：要导入AnalyticDB for MySQL的本地文件的路径，包含文件地址和文件名。

说明如果file_name使用相对路径，则是相对于客户端程序启动时的路径。
table_name：AnalyticDB for MySQL中的目标表名。

说明表名前无需携带数据库名。
REPLACE：导入数据时，遇到主键重复则强制用当前数据覆盖已有数据。
IGNORE：导入数据时，遇到主键重复或者数据问题时则自动忽略失败行，部分行导入失败但不影响其他行导入。
[FIELDS] TERMINATED BY 'string'：定义每行数据的列分隔符，默认为\t，与MySQL一致。
[FIELDS] ENCLOSED BY 'char': 定义每列数据的enclosed符号。
例如，某一列数据为"a"，定义enclosed by '"'后，导入数据时先将"a"前后的"移除，然后导入数据。
[LINES] TERMINATED BY 'string'：定义行分隔符，默认为\n。
IGNORE number LINES：设置导入数据时忽略开始的某几行。例如IGNORE 1 LINES，导入数据时忽略第一行数据，即第一行数据不会导入AnalyticDB for MySQL。
(column_name_or_user_var,...)：设置导入的列，如果不设置，默认按照列的顺序来导入数据。
- 如果导入列数 > 目标表的列数，则系统自动忽略多余的列。
- 如果导入列数 < 目标表的列数，则后续缺少的列将自动填充默认值。

注意事项

客户端开启local-file。
以mysql-client为例，您需要在my.cnf文件中增加以下配置开启local-file功能。
```
cat ~/.my.cnf
[mysqld]
local-infile
[mysql]
local-infile                    
```
更多my.cnf文件信息，请参见MySQL官方文档。
导入数据时无法保证操作的原子性。
- 在IGNORE模式下，忽略导入失败的数据行。
- 在REPLACE模式下，一旦有数据行导入失败，系统将中止后续INSERT操作，因此可能存在部分行数据导入，部分行数据未导入的情况。
支持通过SHOW WARNINGS命令，查看失败行的错误信息。

示例

本示例将本地文件out.bak中的数据导入AnalyticDB for MySQL的test表中。out.bak文件中共有5000行数据，列分隔符为\t，行分隔符\n，其中第10行数据存在问题，如下所示。

1 bb
2 bb
3 bb
4 bb
5 bb
6 bb
7 bb
8 bb
9 bb
bb   10
...

连接AnalyticDB for MySQL集群，通过CREATE DATABASE和CREATE TABLE，在adb_demo数据库下创建表test表，从本地文件导入的数据将存储在test表中。
```
CREATE TABLE test (
a int(11) NOT NULL DEFAULT '0', 
b varchar(8) NOT NULL, 
PRIMARY KEY (a) 
) DISTRIBUTED by hash(a);                
```

在mysql-client中执行LOAD DATA命令将本地文件out.bak中的数据导入AnalyticDB for MySQL的test表中。

#执行LOAD DATA命令， ignore模式下，部分行导入失败但不影响其他行导入。
mysql>  load data local infile '~/out.bak' ignore into table test FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' ;
Query OK, 4999 rows affected, 1 warning (0.69 sec)
mysql> show warnings;
+---------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code  | Message                                                                                                                                                                                            |
+---------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 13000 | com.alibaba.cloud.analyticdb.common.sql.hardcode.HardParserException: syntax error :syntax error => IDENTIFIER is not value type pos:33 row: 0 and ceil:0 |
+---------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select count(1) from test;
+----------+
| count(1) |
+----------+
|     4999 |
+----------+
1 row in set (0.14 sec)

#执行LOAD DATA命令，replace模式下，部分行导入失败后立即终止后续导入操作。
mysql> load data local infile '~/out.bak' replace into table test FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' ;
ERROR 1064 (42000): [13000, 2019061210070703000511314203303000266] syntax error :syntax error => IDENTIFIER is not value type pos:34 row: 0 and ceil:0

#执行LOAD DATA命令，导入数据时会跳过前10条数据。
mysql> load data local infile '~/out.bak' replace into table test FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' IGNORE 10 LINES;
Query OK, 4990 rows affected (0.37 sec)

mysql> show warnings;
Empty set (0.00 sec)