MYSQL 使用 LOAD DATA INFILE 从 csv 导入数据 [英] MYSQL import data from csv using LOAD DATA INFILE

问题描述

我正在将一些 20000 行的数据从 CSV 文件导入 Mysql.

I am importing some data of 20000 rows from a CSV file into Mysql.

CSV 中的列与 MySQL 表的列的顺序不同.如何自动分配Mysql表列对应的列?

Columns in the CSV are in a different order than MySQL table's columns. How to automatically assign columns corresponding to Mysql table columns?

当我执行

LOAD DATA INFILE'abc.csv' INTO TABLE abc

此查询将所有数据添加到第一列.

this query adds all data to the first column.

请建议将数据导入 Mysql 的自动语法.

Please suggest auto syntax for importing data to Mysql.

推荐答案

您可以使用 LOAD DATA INFILE 命令将 csv 文件导入表中.

You can use LOAD DATA INFILE command to import csv file into table.

检查这个链接MySQL - LOAD DATA INFILE.

Check this link MySQL - LOAD DATA INFILE.

LOAD DATA LOCAL INFILE 'abc.csv' INTO TABLE abc
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(col1, col2, col3, col4, col5...);

对于 MySQL 8.0 用户:

使用 LOCAL 关键字会带来安全风险，从 MySQL 8.0 开始，LOCAL 功能默认设置为 False.您可能会看到错误:

Using the LOCAL keyword hold security risks and as of MySQL 8.0 the LOCAL capability is set to False by default. You might see the error:

ERROR 1148: 此 MySQL 版本不允许使用的命令

ERROR 1148: The used command is not allowed with this MySQL version

您可以按照中的说明覆盖它文档.请注意，这种覆盖并不能解决安全问题，而只是承认您知道并愿意承担风险.

You can overwrite it by following the instructions in the docs. Beware that such overwrite does not solve the security issue but rather just an acknowledge that you are aware and willing to take the risk.

这篇关于MYSQL 使用 LOAD DATA INFILE 从 csv 导入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！