在mysql语法中声明变量并使用dapper查询,get exception

Chacha • 5 年前 • 1516 次点击

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
            set @i = 0;
            select @i := @i+1 as Row,Target,StartDate,EndDate
            from (
                     select target,
                            StartDate,
                            EndDate
                     from t_xxx_table
                     where 1 = 1
                       and target= @TargetSn
                     order by StartDate desc
                 )
            COMMIT;

和C代码:

using (var connection = new MySqlConnection(ConnectionString))
        {
            try
            {
                var result = connection.Query<XXXClass>(sqlString, new
                {
                    TargetSn
                });
                return result;
            }
            catch (Exception e)
            {
                throw new Exception(e.ToString());
            }
        }

获取异常“对象引用未设置为对象实例。” 我试图在连接字符串中添加'Allow User Variables=True',但没有成功。当我移除“@I”时,可能是工作。

我想,问题在于一代又一代的人不能处理“@i”和“@TargetSn”。。

有什么解决办法吗?

Python社区是高质量的Python/Django开发社区
本文地址：http://www.python88.com/topic/57007

1516 次点击

文章 [ 2 ] | 最新文章 5 年前

• 1 楼

Akina 5 年前

尝试

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT (@i := @i+1) as `Row`,
       Target,
       StartDate,
       EndDate
FROM t_learning_note, 
     (SELECT @i:=0) init_variable
WHERE 1 = 1
  AND target= @TargetSn
ORDER BY StartDate DESC

COMMIT;

该问题可能无法修复,但此代码保证计算的枚举在成功执行后是正确的(而源查询不能)。

• 2 楼

nbk 5 年前

行是mysql中的保留字

对文本使用记号

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
set @i = 0;
select @i := @i+1 as 'Row',Target,StartDate,EndDate
from (
         select target,
                StartDate,
                EndDate
         from t_learning_note
         where 1 = 1
           and target= @TargetSn
         order by StartDate desc
     )
COMMIT;

正如Akina指出的,这在mysql中运行,但可能会导致问题。

所以你也可以用像

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
set @i = 0;
select @i := @i+1 as `Row`,Target,StartDate,EndDate
from (
         select target,
                StartDate,
                EndDate
         from t_learning_note
         where 1 = 1
           and target= @TargetSn
         order by StartDate desc
     )
COMMIT;

登录后回复