LOAD DATA

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

语法图

LoadDataStmt:

LoadDataStmt

示例

  1. mysql> CREATE TABLE trips (
  2.     ->  trip_id bigint NOT NULL PRIMARY KEY auto_increment,
  3.     ->  duration integer not null,
  4.     ->  start_date datetime,
  5.     ->  end_date datetime,
  6.     ->  start_station_number integer,
  7.     ->  start_station varchar(255),
  8.     ->  end_station_number integer,
  9.     ->  end_station varchar(255),
  10.     ->  bike_number varchar(255),
  11.     ->  member_type varchar(255)
  12.     -> );
  13. Query OK, 0 rows affected (0.14 sec)
  15. mysql> 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);
  17. Query OK, 815264 rows affected (39.63 sec)
  18. Records: 815264  Deleted: 0  Skipped: 0  Warnings: 0

MySQL 兼容性

  • 默认情况下，TiDB 每 20,000 行会进行一次提交。这类似于 MySQL NDB Cluster，但并非 InnoDB 存储引擎的默认配置。

另请参阅