是否可以在 MySqlCommand 中使用 MySql 赋值运算符 (:=)? [英] Is it possible to use MySql assignment operator (:=) in a MySqlCommand?

问题描述

我有一个在 MySql 工作台中运行良好的查询，但产生了一个语法错误 <代码>你的 SQL 语法有错误；检查与你的 MySQL 服务器版本相对应的手册，以获取附近使用的正确语法':= 0+1 AS Rank, z.* &#xD;&#xA;FROM(SELECT MemberId, Valu' at line 2" 当通过 MySqlCommand.ExecuteReader 运行时.有问题的语法是选择内的变量赋值(即 SELECT @r := @r + 1).我正在使用 .net 连接器 v.6.9.9，以防万一.另外，这是我的连接字符串:<代码>"server=mymysqlserveraddress;port=3306;database=dbName;user id=username;password=********;Convert Zero Datetime=True;Allow User Variables=true"

I have a query that works fine in MySql workbench, but is producing a syntax 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 ':= 0+1 AS Rank, z.* 
FROM(SELECT MemberId, Valu' at line 2" when run via MySqlCommand.ExecuteReader. The syntax in question is a variable assignment inside of a select (i.e. SELECT @r := @r + 1). I am using .net connector v.6.9.9 in case that's relevant. Also, here's my connection string: "server=mymysqlserveraddress;port=3306;database=dbName;user id=username;password=********;Convert Zero Datetime=True;Allow User Variables=true"

我尝试在连接字符串中设置 Allow User Variables=true ，如下所示:是否可以在 .NET MySqlCommand 中使用 MySql 用户定义变量?

I have tried setting Allow User Variables=true in the connection string as shown here: Is it possible to use a MySql User Defined Variable in a .NET MySqlCommand?

我寻找了与此匹配的其他问题，但最终只找到了一个同样的问题，但对于 java 中的休眠:如何在休眠原生查询中使用 MySQL 赋值运算符(:=)?

I looked for other questions that matched this but ultimately, only found one that is the same problem, but for hibernate in java: How can I use MySQL assign operator(:=) in hibernate native query?

这是在 MySql Workbench 中工作的原始 sql 查询:

Here is the raw sql query that works in MySql Workbench:

SELECT Rank, Value 
FROM (SELECT @r := @r+1 AS Rank, z.* 
      FROM(SELECT MemberId, Value 
           FROM (SELECT Id, MemberId, Value, CreatedDate, EventId 
                 FROM Scores
                 WHERE (LoadTime IS NULL AND EventId = 408)
                 ORDER BY Value DESC
                ) AS sub 
     )z, 
     (SELECT @r:=0)y
)x 
WHERE x.Value<=228000;

408 和 228000 只是示例 - 我的 C# 代码构造字符串，然后通过以下方式将其发送到服务器:

The 408 and 228000 are just examples - my C# code constructs the string and then sends it to the server via:

StringBuilder statisticQueryString = new StringBuilder("");
// construct statisticQueryString
using (MySqlCommand cmd = new MySqlCommand(statisticQueryString.ToString(), conn))
{
    cmd.Parameters.AddWithValue("r", 0);

    using (MySqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
    {
        // Iterate through the collection of RankResult items...
    }
}

由于上述语法错误而失败的示例构造查询(我在发送之前将最终查询打印到日志中)是:

A sample constructed query (I am printing the final query to the log before sending) that fails with the syntax error above is:

SELECT Rank, Value 
FROM (SELECT @r := @r+1 AS Rank, z.* 
      FROM(SELECT MemberId, Value 
           FROM (SELECT Id, MemberId, Value, CreatedDate, EventId 
                 FROM Scores
                 WHERE (LoadTime IS NULL AND EventId = 408)
                 ORDER BY Value DESC
                ) AS sub 
     )z, 
     (SELECT @r:=0)y
)x 
WHERE x.Value<=228000;

如您所见，构造的查询与我可以在 MySql 工作台中无错误运行的查询相同.

As you can see, the constructed query is identical to the one I can run without errors in MySql workbench.

推荐答案

@Uueerdo 的评论提示我尝试删除 cmd.Parameters.AddWithValue("r", 0); 行C# 代码.这修复了错误并给了我预期的结果.

The comment by @Uueerdo prompted me to try removing the cmd.Parameters.AddWithValue("r", 0); line from the C# code. This fixed the error and gave me the results I was expecting.

这篇关于是否可以在 MySqlCommand 中使用 MySql 赋值运算符 (:=)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！