加载数据 infile 错误:MySQL 在指定列时抛出语法错误 [英] load data infile error: MySQL throws a syntax error when specifying columns

问题描述

StackOverflowers！

StackOverflowers!

我在尝试将 .csv 文件导入表格时遇到问题.

I have an issue when trying to import a .csv file into a table.

据我所知(正如我在 参考手册)，语法是这样的:

As far as I know (and as I've read in the reference manual), the syntax is something like this:

load data infile '/path/to/my/file.csv'
    into table myTable(field1, field2, field3,..., fieldk)
    fields terminated by ',' optionally enclosed by '"'
    lines terminated by '\n'
    ignore 1 lines;

然而，MySQL 客户端抛出此错误:

However, MySQL client throws this error:

ERROR 1064 (42000):您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在以 ' 终止的字段"附近使用的正确语法，可选择由以 '\n' 终止的 '"' 行括起来

我需要指定列列表，因为我的表有一个文件中不存在的列(id 列).

I need to specify the column list, because my table has a column that are not present in the file (the id column).

我已经检查(并重新检查)了语法，一切似乎都没问题……所以我不知道哪里出了问题.我试过用反引号 (`) 将字段名称括起来，但它也不起作用......字段列表有错吗?我需要指定我遗漏的东西吗?

I've checked (and rechecked) the syntax, and everything seems ok... so I don't have a clue what could be wrong. I've tried enclosing the field names in backticks (`) but it doesn't work either... Is the field list wrong? Do I need to specify something I'm missing?

背景信息:

• 操作系统:Debian 7.5 (wheezy)，64 位
• MySQL 版本:5.5.37-0+wheezy1
• 所有表都是 MyISAM

尝试的解决方案:

我当然可以创建一个临时表并删除有问题"的列.我已经测试了这个解决方案，它有效:

I could, of course, create a temporary table and drop the "problematic" column. I've tested this solution, and it works:

drop table if exists temp_myTable;
create temporary table temp_myTable like myTable;
alter table temp_myTable drop column id;
load data infile '/path/to/my/file.csv'
    into table temp_myTable
    fields terminated by ',' optionally enclosed by '"'
    lines terminated by '\n'
    ignore 1 lines;
insert into myTable (field1, field2, ... fieldk)
    select field1, field2, ... fieldk
    from temp_myTable;
drop table if exists temp_myTable;

但是，我觉得这很麻烦……我应该可以用一条指令解决它，为什么还要写6条指令?

However, I think this is quite cumbersome... why should I write 6 instructions when I should be able to solve it with one?

你能帮我吗?

推荐答案

仔细阅读参考手册后，发现列列表的正确写法是在句末:

After reading more carefully the reference manual, I found that the right way to write the column list is at the end of the sentence:

load data infile '/path/to/my/file.csv'
    into table myTable
    fields terminated by ',' optionally enclosed by '"'
    lines terminated by '\n'
    ignore 1 lines
    (field1, field2, field3,..., fieldk); -- The field list goes here

现在，我觉得自己很傻……但我把这些知识放在这里，以防万一有人面临同样的问题.

Right now, I'm feeling really dumb... but I put this knowledge here, just in case anyone is facing this same issue.

这篇关于加载数据 infile 错误:MySQL 在指定列时抛出语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！