不使用MySqlDataReader的正确查询

Question

因此，我试图找出这一点，特别是我有一个使用PhpMyAdmin完美工作的查询

SELECT tt.team_id, (CASE WHEN t.id IS NULL THEN 0 ELSE 1 END) as exist FROM(SELECT 13048 as team_id  UNION ALL SELECT 17058 UNION ALL SELECT 38809 UNION ALL SELECT 8216 UNION ALL SELECT 5466) tt LEFT JOIN team t on t.id = tt.team_id WHERE t.id IS NULL OR t.update_at < DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)

总之，我从Visual Studio那里得到了这个错误

MySql.Data.MySqlClient.MySqlException：“您的SQL语法有错误；请检查与您的MySQL服务器版本对应的手册，以获得在”UNION 17058 UNION“附近使用的正确语法。

此错误发生在以下几个方面：

 using (MySqlDataReader reader = command.ExecuteReader())

我以这样的方式设置查询：

command.CommandText = "SELECT tt.team_id, " +
    "(CASE WHEN t.id IS NULL THEN 0 ELSE 1 END) as exist " +
    "FROM(SELECT @first as team_id @others) tt LEFT JOIN team t on t.id = tt.team_id " +
    "WHERE t.id IS NULL OR " +
    "t.update_at < DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)";

command.Parameters.Add("@first", MySqlDbType.Int32).Value = teams.First().Id;
command.Parameters.Add("@others", MySqlDbType.String).Value = string.Concat(teams.Skip(1).Select(c => " UNION ALL SELECT " + c.Id));

有人能帮我吗？

Answer 1

这就是我如何构建一个动态的参数列表，以传递给您的查询。

警告，没有测试，但这会产生预期的输出。

// Command text with a placeholder where we insert the dynamic text
string cmd = @"SELECT tt.team_id, 
                      (CASE WHEN t.id IS NULL THEN 0 ELSE 1 END) as exist 
               FROM (SELECT {texttoreplace}) tt  
               LEFT JOIN team t on t.id = tt.team_id WHERE t.id IS NULL 
                     OR t.update_at < DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)";

int prmCounter = 1;

// Where we keep the text to insert at the appropriate place
StringBuilder unions = new StringBuilder();
// Where we keep the parameters to add at the MySqlCommand
List<MySqlParameter> prms = new List<MySqlParameter>();

// First parameter
MySqlParameter pr = new MySqlParameter("@first", MySqlDbType.Int32) { Value = teams.First().id};
prms.Add(pr);
unions.Append($" @first as team_id ");

// Loop over your IDs and build parameters and text
foreach (var t in teams.Skip(1))
{
    // Giving an unique name to the parameter
    string placeholder = "@p" + prmCounter;
    unions.Append($" UNION ALL SELECT {placeholder}");
    pr = new MySqlParameter(placeholder, MySqlDbType.Int32) { Value = t.id};
    prms.Add(pr);
    prmCounter++;
}
// Add all the required parameters
command.Parameters.AddRange(prms.ToArray());
// Replace the placeholder with the built text
cmd = cmd.Replace("{texttoreplace}",  unions.ToString());