全部产品
云市场

使用LOAD DATA导入本地数据

更新时间:2019-08-06 12:08:05

语法

  1. LOAD DATA LOCAL
  2. INFILE 'file_name'
  3. [REPLACE | IGNORE]
  4. INTO TABLE table_name
  5. [{FIELDS | COLUMNS}
  6. [TERMINATED BY 'string']
  7. [[OPTIONALLY] ENCLOSED BY 'char']
  8. ]
  9. [LINES
  10. [TERMINATED BY 'string']
  11. ]
  12. [IGNORE number {LINES | ROWS}]
  13. [(column_name_or_user_var
  14. [, 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功能。

    1. cat ~/.my.cnf
    2. [mysqld]
    3. local-infile
    4. [mysql]
    5. local-infile

    更多my.cnf文件信息,请参见MySQL官方文档

  • 导入数据时无法保证操作的原子性。

    • IGNORE模式下,忽略导入失败的数据行。

    • REPLACE模式下,一旦有数据行导入失败,系统将中止后续INSERT操作,因此可能存在部分行数据导入,部分行数据未导入的情况。

  • 支持通过SHOW WARNINGS命令,查看失败行的错误信息。

示例

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

  1. 1 bb
  2. 2 bb
  3. 3 bb
  4. 4 bb
  5. 5 bb
  6. 6 bb
  7. 7 bb
  8. 8 bb
  9. 9 bb
  10. bb 10
  11. ...
  1. 连接集群,通过CREATE DATABASECREATE TABLE,在adb_demo数据库下创建表test表。从本地文件导入的数据将存储在test表中。

    1. CREATE TABLE test (
    2. a int(11) NOT NULL DEFAULT '0',
    3. b varchar(8) NOT NULL,
    4. PRIMARY KEY (a) ) DISTRIBUTED by hash(a);
  2. 在mysql-client中执行LOAD DATA命令将本地文件out.bak中的数据导入AnalyticDB for MySQL的test表中。

    1. #执行LOAD DATA命令, ignore模式下,部分行导入失败但不影响其他行导入。
    2. mysql> load data local infile '~/out.bak' ignore into table test FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' ;
    3. Query OK, 4999 rows affected, 1 warning (0.69 sec)
    4. mysql> show warnings;
    5. +---------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    6. | Level | Code | Message |
    7. +---------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    8. | 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 |
    9. +---------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    10. 1 row in set (0.01 sec)
    11. mysql> select count(1) from test;
    12. +----------+
    13. | count(1) |
    14. +----------+
    15. | 4999 |
    16. +----------+
    17. 1 row in set (0.14 sec)
    18. #执行LOAD DATA命令,replace模式下,部分行导入失败后立即终止后续导入操作。
    19. mysql> load data local infile '~/out.bak' replace into table test FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' ;
    20. ERROR 1064 (42000): [13000, 2019061210070703000511314203303000266] syntax error :syntax error => IDENTIFIER is not value type pos:34 row: 0 and ceil:0
    21. #执行LOAD DATA命令,导入数据时会跳过前10条数据。
    22. mysql> load data local infile '~/out.bak' replace into table test FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' IGNORE 10 LINES;
    23. Query OK, 4990 rows affected (0.37 sec)
    24. mysql> show warnings;
    25. Empty set (0.00 sec)