你可以使用MySQL @会话变量在C＃接口？ [英] Can you use MySQL @ session variables in the C# connector?

问题描述

我有利用MySQL的会话变量（注意@rank变量）

查询

  SELECT排名，用户ID，CurrentVDOT
从
    （
        选择@rank：= @rank + 1等级，用户ID，MaxVDOT AS CurrentVDOT
        从
            （
                SELECT用户ID，MAX（VDOT）AS MaxVDOT
                从
                    （
                        选择U.UserId，U.VDOT
                        从
                            （
                                SELECT用户ID，MAX（创建）创建为
                                从UserVDOT
                                GROUP BY用户ID
                            ） G
                        INNER JOIN UserVDOTū
                        ON U.UserId = G.UserId
                            和U.Created = G.Created
                    ）M
                GROUP BY用户ID
                ORDER BY MaxVDOT DESC
            ）R，（SELECT @rank：= 0）富
    ） F
WHERE F.UserId = @UserId;
 

如果我试图针对C＃的MySQL Connector执行此，它试图告诉我，我需要声明@rank作为输入参数变量。

有没有解决这个办法吗？

感谢。

解决方案

  

您好佳佳！

  
  

我最近面临同样的问题，但是我发现在默认情况下，
  SQL连接禁用查询中使用的变量。解决
  这个问题，你可以启用连接字符串如下：

连接字符串：

 服务器192.168.0.0 =;密码=根;用户ID =根;坚持安全信息= TRUE;数据库= MY_DB;允许用户变量= TRUE
 

您必须输入连接参数允许用户变量= TRUE ，以使其工作。

  

我希望我帮助。

  
  

Hiago高木的

I have a query that utilizes MySQL session variables (note the @rank variable)

SELECT Rank, UserId, CurrentVDOT
FROM
    (
        SELECT @rank := @rank + 1 AS Rank, UserId, MaxVDOT AS CurrentVDOT
        FROM
            (
                SELECT UserId, MAX(VDOT) AS MaxVDOT
                FROM
                    (
                        SELECT U.UserId, U.VDOT
                        FROM
                            (
                                SELECT UserId, MAX(Created) AS Created
                                FROM UserVDOT
                                GROUP BY UserId
                            ) G
                        INNER JOIN UserVDOT U
                        ON U.UserId = G.UserId
                            AND U.Created = G.Created
                    ) M
                GROUP BY UserId
                ORDER BY MaxVDOT DESC
            ) R, (SELECT @rank := 0) foo
    ) F
WHERE F.UserId = @UserId;

If I try to execute this against the C# MySQL connector, it tries to tell me that I need to declare @rank as an input parameter variable.

Is there any way around this?

Thanks.

解决方案

Hello Allison!

I was facing the same problem recently, but I found that by default, the SQL connection disables the use of variables in queries. To solve this problem you can enable the connection string as follows:

Connection String:

server=192.168.0.0;password=root;User Id=root;Persist Security Info=True;database=my_db;Allow User Variables=True

You must enter connection parameter Allow User Variables=True to make it work.

I hope I've helped.

Hiago Takaki

这篇关于你可以使用MySQL @会话变量在C＃接口？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！