时间戳

首页 > 解决方案 > C# - Load data infile to mysql - fatal error

c# - C# - Load data infile to mysql - fatal error

问题描述

I'm trying to load data to a mysql table, when I run the code in the MySql Workbench, it works fine, but when I try to run it in my c# application, I get this error:

Fatal error encountered during command execution.

Here's my code:

mConn.Open(); //Opens the connection
string query = "delete from time; alter table time auto_increment=1; LOAD DATA INFILE 'C://time_u.txt' INTO TABLE `time` CHARACTER SET 'utf8' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (@col2, @col3) set date = @col2, hour = @col3;";
WriteDataToDataBase(query); //This guy just executes the command.ExecuteNonQuery();
mConn.Close();

Ps: I have 3 columns, the first one is a primary key (id) which I can't import, it needs to go up by one automatically, the second is the date (varchar) and third is the hours (varchar as well).

UPDATE
I tried to change the LOAD DATA code and I found out that the part that gives the error is this part: (@col2, @col3) set date = @col2, hour = @col3;. When I remove this, the code works (it doesn't work in my case because without this it tries to put data in the Primary key) but it doesn't show that first error.

Thanks...

标签： c#mysqlsqlvisual-studiofile

解决方案

MySql.Data 将@col2,@col3视为（未定义的）查询参数，这会导致此错误。一种解决方法是添加AllowUserVariables=true到您的连接字符串。

另一种解决方法是避免变量并直接加载到您想要的列中：

LOAD DATA INFILE 'C://time_u.txt'
INTO TABLE time
CHARACTER SET 'utf8'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(date, hour);

推荐阅读