mysql保存plc数据_MySQL 8 中将导出的CSV文件中的数据再导入到MySQL表中

使用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';