mysql加载数据本地infile [英] mysql load data local infile

问题描述

我正在尝试使用以下代码使用LOAD DATA LOCAL INFILE将数据加载到mysql表中。

I'm trying to load data into a mysql table using LOAD DATA LOCAL INFILE using the code below.

Mysql：

LOAD DATA INFILE '/var/www/vhosts/domain.com/httpdocs/test1.csv' INTO TABLE temp_table FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (recloc,client_acc)

编辑：将LOAD DATA LOCAL INFILE更改为LOADA DATA INFILE，删除SET id = null，添加IGNORE 1 LINES

changed LOAD DATA LOCAL INFILE to LOADA DATA INFILE, removed SET id=null, added IGNORE 1 LINES

我没有错误，也没有导入的记录。我相信这个问题与列名相关，但我很难完全理解这些名称应该是什么。它们应该是CSV中的实际列名吗？还是DB表中的字段名称？我也想要auto_incremented主键（id）。

I'm getting no errors and no imported records. I believe the issue is related to the column names but i'm having a hard time fully understanding what those names should be. Should they be the actual column names within the CSV? or the field names in the DB Table? I would also like the have an auto_incremented primary key (id).

CSV：

recloc,client_acc
"NLGSX3","CORPORATE"
"7SC3BA","QUALITY ASSURANCE"
"3B9OHF","90717-6710"

对我可能做错的任何建议？谢谢！

Any suggestions to what I may be doing wrong? thanks!

推荐答案

CSV中的列名不是必需的，因此您应该添加 IGNORE 1 LINES 子句。

Column names in CSV are not necessary, so you should add IGNORE 1 LINES clause.

查询中的列（recloc，client_acc）需要匹配表中的列。
CSV中的第一列将插入 recloc ，第二列插入 client_acc 。

Columns in your query (recloc,client_acc) need to match columns in table. First column from CSV will be inserted into recloc, second into client_acc.

如果你没有在语句中指定AUTO_INCREMENT列，但表中有一个，它应该自动填充。

If you don't specifu AUTO_INCREMENT column in the statement, but there is one in the table, it should fill automatically.

这篇关于mysql加载数据本地infile的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！