C＃ - INSERT到DB的MySQL语法错误

I am encountering a MySqlException error in my program. It is a simple Test program where I am trying to insert log errors with time stamps into a table in my database.

我在我的程序中遇到MySqlException错误。这是一个简单的测试程序,我试图将带有时间戳的日志错误插入到我的数据库中的表中。

// -------------PROGRAM----------------------

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;  


string Log = "Sample Log Error";
string key = "11111";

string date;
string time;
int unixT;
TimeSpan unix_time;

// geting current date in yyyy-mm-dd format     
date = DateTime.UtcNow.ToString("yyyy-MM-dd"); 

//getting current time in hh:mm:ss format
time = string.Format("{0:HH:mm:ss tt}", DateTime.Now); 

// Getting Unix Time
unix_time = (System.DateTime.UtcNow - new DateTime(1970, 1, 1, 0, 0, 0)); 

//converting Unix time to seconds
unixT = Convert.ToInt32(unix_time.TotalSeconds); 

MySqlConnection myConn = new MySqlConnection("Server=172.178.2.143;" +            "Port=3306;" + "Database=DB;" + "Uid=user;" + "Pwd=pass;");                

 try
 {
   myConn.Open();
 }
 catch (MySqlException e)
 {
   Console.WriteLine(e);
 }                   

 MySqlCommand comm1 = new MySqlCommand("INSERT INTO Logger(date_stamp,         time_stamp, log, unix_time, key) VALUES(@date_stamp, @time_stamp, @log, @unix_time, @key)", myConnection);

 //inserting each element from GPS split message into its respective table    and column in the database                
comm1.Parameters.AddWithValue("date_stamp", date);              
comm1.Parameters.AddWithValue("time_stamp", time);                
comm1.Parameters.AddWithValue("log", Log);                
comm1.Parameters.AddWithValue("unix_time", unixT);
comm1.Parameters.AddWithValue("key", key);

try
{
  comm1.ExecuteNonQuery(); //executing the INSERT Query Above
}

catch (MySqlException e)               
{                    
  Console.WriteLine(e);                
}

myConnection.Close();

// ------------END PROGRAM------------------

// ------------ END PROGRAM ------------------

I am getting the following error:

我收到以下错误:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key) VALUES('2013-09-13', '16:21:15 PM', 'Sample Log Error', 1379103676, '11111'' at line 1"

“您的SQL语法有错误;请查看与您的MySQL服务器版本对应的手册,以便在'key'附近使用正确的语法)VALUES('2013-09-13','16:21:15 PM','第1行的“采样记录错误”,1379103676,“11111”

2 个解决方案

#1

The word KEY is a reserved keyword for MySql

KEY这个词是MySql的保留关键字

If you want to use that name in your code you need to add backticks around it

如果要在代码中使用该名称,则需要在其周围添加反引号

 MySqlCommand comm1 = new MySqlCommand("INSERT INTO Logger(date_stamp," + 
                          "time_stamp, log, unix_time, `key`) " + 
                          "VALUES(@date_stamp, @time_stamp, @log, @unix_time, @key)",
                           myConnection);

If it is not too late in the development stage of your application I suggest you to change that name because every time you touch this field you will have the same problem to remember.

如果在应用程序的开发阶段还为时已晚,我建议您更改该名称,因为每次触摸此字段时都会遇到同样的问题。

I wish also to suggest the using statement around your code and just one try catch

我还希望建议你的代码周围的using语句,只需一次尝试catch

try
{
    using(MySqlConnection myConn = new MySqlConnection(.....))
    using(MySqlCommand comm1 = new MySqlCommand(......))
    {
         myConn.Open();
         comm1.Parameters.AddWithValue("date_stamp", date);              
         .....
         comm1.ExecuteNonQuery(); 
    }
}
catch (MySqlException e)               
{                    
    Console.WriteLine(e);                
}

In this way you get a correct handling of edge case scenarios (fail to open the connection, fail to execute the command) and your connection will be correctly closed and disposed when the code flows out of the closing brace.

通过这种方式,您可以正确处理边缘情况(无法打开连接,无法执行命令),并且当代码流出结束括号时,您的连接将被正确关闭和处理。

#2

key is a reserved word, wrap it in backticks so that MySQL knows you're talking about a column name.

key是一个保留字,用反引号包装,以便MySQL知道你在谈论列名。

#1