MySQL报错Operand should contain 1 column(s)：C#执行SQL语句失败

问题描述 #

直接在MySQL客户端执行以下UPDATE语句可正常运行，但通过C#的String.Format拼接SQL后执行，报错MySQL - Operand should contain 1 column(s)：

double deltaTime = (DateTime.Now - collectTime).TotalSeconds;

string query = String.Format("UPDATE buildings LEFT JOIN server_buildings ON buildings.global_id = server_buildings.global_id AND buildings.level = server_buildings.level SET buildings.storage = buildings.storage + (server_buildings.speed * {0} * IF(buildings.boost >= NOW(), 2, 1)) WHERE buildings.global_id IN('generator', 'test')", deltaTime / 3600d);

预期需求是随时间推移为buildings表的storage字段累加数值。

问题原因 #

报错根源是浮点数的区域格式问题：String.Format默认使用当前系统的区域设置，若系统用逗号作为小数点分隔符（比如中文、欧洲部分地区），拼接后的SQL中数值会变成类似0,5的格式，MySQL会将逗号识别为列分隔符，误以为此处存在多列，从而抛出错误。

解决方案 #

方案1：使用参数化查询（推荐） #

参数化查询既能彻底避免格式问题，还能防止SQL注入，是最佳实践：

double deltaTime = (DateTime.Now - collectTime).TotalSeconds;
double hours = deltaTime / 3600d;

// 定义带参数的SQL语句
string query = @"UPDATE buildings 
                 LEFT JOIN server_buildings 
                 ON buildings.global_id = server_buildings.global_id AND buildings.level = server_buildings.level 
                 SET buildings.storage = buildings.storage + (server_buildings.speed * @hours * IF(buildings.boost >= NOW(), 2, 1)) 
                 WHERE buildings.global_id IN('generator', 'test')";

using (MySqlConnection conn = new MySqlConnection("你的数据库连接字符串"))
{
    conn.Open();
    using (MySqlCommand cmd = new MySqlCommand(query, conn))
    {
        // 添加参数并赋值
        cmd.Parameters.AddWithValue("@hours", hours);
        // 执行更新
        cmd.ExecuteNonQuery();
    }
}

方案2：强制使用不变区域格式拼接字符串（不推荐） #

若必须用字符串拼接，需指定CultureInfo.InvariantCulture确保小数点为英文格式：

double deltaTime = (DateTime.Now - collectTime).TotalSeconds;

string query = string.Format(CultureInfo.InvariantCulture, 
    "UPDATE buildings LEFT JOIN server_buildings ON buildings.global_id = server_buildings.global_id AND buildings.level = server_buildings.level SET buildings.storage = buildings.storage + (server_buildings.speed * {0} * IF(buildings.boost >= NOW(), 2, 1)) WHERE buildings.global_id IN('generator', 'test')", 
    deltaTime / 3600d);

注意：该方案仍存在SQL注入风险，仅作为临时替代方案使用。

内容的提问来源于stack exchange，提问作者Ekornseter