使用更新命令的 C# MySQL 语法错误

Question

大家好。我在与数据库交互时遇到了另一个问题。这次使用更新 命令。

我有一个包含以下列的表格:

Identity Number (int, auto-increment)
Username (varchar)
Password (varchar)
Authority Level (int)
Last Login (timestamp)

我正在尝试使用身份号码更新用户名、密码和权限级别。

这是我要执行的代码:

public static async Task<bool> UpdateAdministrator(
    int identityNumber,
    string username,
    string password,
    int authorityLevel)
{
    try
    {
        await OpenConnection();
        MySqlCommand mySqlCommand = mySqlConnection.CreateCommand();
        mySqlCommand.CommandText = "UPDATE Administrators SET Username = @Username, Password = @Password, 'Authority Level' = @Authority_Level WHERE 'Identity Number' = @Identity_Number;";
        mySqlCommand.Parameters.Add("@Identity_Number", MySqlDbType.Int32).Value = identityNumber;
        mySqlCommand.Parameters.Add("@Username", MySqlDbType.VarChar).Value = username;
        mySqlCommand.Parameters.Add("@Password", MySqlDbType.VarChar).Value = password;
        mySqlCommand.Parameters.Add("@Authority_Level", MySqlDbType.Int32).Value = authorityLevel;
        await mySqlCommand.ExecuteNonQueryAsync();
        mySqlCommand.Dispose();
        await CloseConnection();
        return true;
    }
    catch
    {
        return false;
    }
}

这是我遇到的异常:

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 ''Authority Level' = 0 WHERE 'Identity Number' = 1' at line 1

谁能帮我找出问题所在？提前致谢

Answer 1

只需将 ' 替换为 `

在 MySQL 中，` 字符用于 identify表/列名

The identifier quote character is the backtick (“`”):

所以你的代码应该是这样的

mySqlCommand.CommandText = "UPDATE Administrators SET Username = @Username, Password = @Password, `Authority Level` = @Authority_Level WHERE `Identity Number` = @Identity_Number;";