如何使用 LOAD DATA INFILE 将 CSV 文件中的选定列插入 MySQL 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4202564/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert selected columns from a CSV file to a MySQL database using LOAD DATA INFILE
提问by Ugesh Gali
I have a CSV file which contains 10 columns. I want to select only some columns from that file and load them into a MySQL database using the LOAD DATA INFILEcommand.
我有一个包含 10 列的 CSV 文件。我只想从该文件中选择一些列并使用该LOAD DATA INFILE命令将它们加载到 MySQL 数据库中。
回答by ArK
Load data into a table in MySQL and specify columns:
将数据加载到 MySQL 中的表中并指定列：
LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE t1
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
(@col1,@col2,@col3,@col4) set name=@col4,id=@col2 ;
@col1,2,3,4 are variables to hold the csv file columns (assume 4 ) name,id are table columns.
@col1,2,3,4 是保存 csv 文件列（假设为 4 ）名称的变量，id 是表列。
回答by Marc B
LOAD DATA INFILE 'file.csv'
INTO TABLE t1
(column1, @dummy, column2, @dummy, column3, ...)
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\r\n';
Just replace the column1, column2, etc.. with your column names, and put @dummy anwhere there's a column in the CSV you want to ignore.
只需将 column1、column2 等替换为您的列名，然后将 @dummy 放在您想要忽略的 CSV 列中。
Full details here.
完整的细节在这里。
回答by Ataboy Josef
Specify the name of columns in the CSV in the load data infile statement.
在 load data infile 语句中指定 CSV 中的列名。
The code is like this:
代码是这样的：
LOAD DATA INFILE '/path/filename.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
(column_name3, column_name5);
Here you go with adding data to only two columns(you can choose them with the name of the column) to the table.
在这里，您只将数据添加到表中的两列（您可以使用列名选择它们）。
The only thing you have to take care is that you have a CSV file(filename.csv) with two values per line(row). Otherwise please mention. I have a different solution.
您唯一需要注意的是您有一个每行（行）有两个值的 CSV 文件（文件名.csv）。否则请提及。我有一个不同的解决方案。
Thank you.
谢谢你。
回答by Roberto Góes
Example:
例子：
contents of the ae.csv file:
ae.csv 文件的内容：
"Date, xpto 14"
"code","number","year","C"
"blab","15885","2016","Y"
"aeea","15883","1982","E"
"xpto","15884","1986","B"
"jrgg","15885","1400","A"
CREATE TABLE Tabletmp (
rec VARCHAR(9)
);
For put only column 3:
对于仅放置第 3 列：
LOAD DATA INFILE '/local/ae.csv'
INTO TABLE Tabletmp
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES
(@col1, @col2, @col3, @col4, @col5)
set rec = @col3;
select * from Tabletmp;
2016
1982
1986
1400
回答by Abderrahmane
if you have number of columns in your database table more than number of columns in your csv you can proceed like this:
如果您的数据库表中的列数多于 csv 中的列数，您可以像这样继续：
LOAD DATA LOCAL INFILE 'pathOfFile.csv'
INTO TABLE youTable
CHARACTER SET latin1 FIELDS TERMINATED BY ';' #you can use ',' if you have comma separated
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\'
LINES TERMINATED BY '\r\n'
(yourcolumn,yourcolumn2,yourcolumn3,yourcolumn4,...);
回答by Nicolas Bouvrette
For those who have the following error:
对于那些有以下错误的人：
Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
错误代码：1290。MySQL 服务器正在使用 --secure-file-priv 选项运行，因此无法执行此语句
You can simply run this command to see which folder can load files from:
您可以简单地运行此命令来查看哪个文件夹可以从以下位置加载文件：
SHOW VARIABLES LIKE "secure_file_priv";
After that, you have to copy the files in that folder and run the query with LOAD DATA LOCAL INFILEinstead of LOAD DATA INFILE.
在这之后，你必须在文件夹中复制，并运行查询LOAD DATA LOCAL INFILE代替LOAD DATA INFILE。
