使用LOAD DATA INFILE导入数据:mysql> LOAD DATA INFILE 'result.csv' INTO TABLE
employee_names
FIELDS TERMINATED BY ',' # 列分隔符
OPTIONALLY ENCLOSED BY '"' # 字段值用双引号包裹
LINES TERMINATED BY '\n'; # 使用\n分隔行
Query OK, 10 rows affected (0.01 sec)
Records: 10 Deleted: 0 Skipped: 0 Warnings: 0
文件可以使用绝对路径。
如果想要忽略文件头,可以使用IGNORE n lines:mysql> LOAD DATA INFILE 'result.csv' INTO TABLE
employee_names
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
可以使用 REPLACE或IGNORE来解决数据重复的问题:mysql> LOAD DATA INFILE 'result.csv' REPLACE
INTO TABLE employee_names FIELDS TERMINATED BY
','OPTIONALLY ENCLOSED BY '"' LINES TERMINATED
BY '\n';
Query OK, 10 rows affected (0.01 sec)
Records: 10 Deleted: 0 Skipped: 0 Warnings: 0
mysql> LOAD DATA INFILE 'result.csv' IGNORE INTO
TABLE employee_names FIELDS TERMINATED BY
','OPTIONALLY ENCLOSED BY '"' LINES TERMINATED
BY '\n';
Query OK, 10 rows affected (0.06 sec)
Records: 10 Deleted: 0 Skipped: 0 Warnings: 0
如果数据在远程客户,可以先将文件下载到本地并存在/tmp临目录下,再导入:mysql> LOAD DATA LOCAL INFILE 'result.csv'
IGNORE INTO TABLE employee_names FIELDS
TERMINATED BY ','OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';