一、问题描述
1.语句
SELECT
AVG(jsjg) as '平均接收间隔',
AVG(fyjg) as '平均发药间隔',
AVG(jsfyjg) as '平均接收-发药间隔',
AVG(fywcjg) as '平均发药-完成间隔',
AVG(jswcjg) as '平均接收-完成间隔'
FROM (
SELECT
aa.ct1 as ct,
aa.t1 as t,
aa.ft1 as ft,
TIMESTAMPDIFF(SECOND,bb.ct2,aa.ct1) as jsjg,
TIMESTAMPDIFF(SECOND,bb.t2,aa.t1) as fyjg,
TIMESTAMPDIFF(SECOND,aa.ct1,aa.t1) as jsfyjg,
TIMESTAMPDIFF(SECOND,aa.t1,aa.ft1) as fywcjg,
TIMESTAMPDIFF(SECOND,aa.ct1,aa.ft1) as jswcjg
FROM (
SELECT
(@i := @i + 1) as id,
a.create_time as ct1,
c.checkin_time as ckt1,
a.validate_time as vt1,
a.finish_time as ft1,
b.time as t1,
b.txt
FROM
prescription as a
LEFT JOIN (
SELECT
REPLACE(substring_index(result,' ',4),CONCAT(substring_index(result,' ',3),' '),'') as pre_id,
time,
GROUP_CONCAT(substring_index(result,' ',2)) as txt
FROM
system_log
WHERE oper_id = '1809' GROUP BY pre_id) as b on a.pre_id = b.pre_id
LEFT JOIN work_queue as c on a.pre_id = c.pre_id ,
(select @i := 0) as d
WHERE b.pre_id is not NULL
and c.checkin_time is not NULL
AND a.create_time BETWEEN '2020-06-27 00:00:00' AND '2020-06-28 00:00:00'
and c.split_id = 0) as aa
LEFT JOIN (
SELECT
(@j := @j + 1) as id,
a.create_time as ct2,
c.checkin_time as ckt2,
a.validate_time as vt2,
a.finish_time as ft2,
b.time as t2 ,
b.txt
FROM
prescription as a
LEFT JOIN (
SELECT
REPLACE(substring_index(result,' ',4),CONCAT(substring_index(result,' ',3),' '),'') as pre_id,
time,
GROUP_CONCAT(substring_index(result,' ',2)) as txt
FROM
system_log
WHERE oper_id = '1809'
GROUP BY pre_id) as b on a.pre_id = b.pre_id
LEFT JOIN work_queue as c on a.pre_id = c.pre_id ,
(select @j := 1) as d
WHERE b.pre_id is not NULL
and c.checkin_time is not NULL
AND a.create_time BETWEEN '2020-06-27 00:00:00' AND '2020-06-28 00:00:00'
and c.split_id = 0)
as bb on aa.id = bb.id )
as aaa
where aaa.jsjg is not null
and aaa.fyjg is not null
and aaa.jsfyjg is not null
and aaa.fywcjg is not null
and aaa.jswcjg is not null;
2.编程语言及异常信息
2.1 异常1
public static void Test()
{
try
{
string connectionString = "Database=test;Data Source=127.0.0.1;uid=root;pwd=root;pooling=true;CharSet=utf8;port=3306;Connect Timeout=2;";
string sql = "SELECT (@i:=@i+1) id FROM medicine a, (select @i:= 0) b";
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
using (MySqlCommand com = new MySqlCommand())
{
com.Connection = conn;
com.CommandText = sql;
com.CommandTimeout = 3600;
// 异常位置
MySqlDataReader dr = com.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (dr.Read())
{
string text = dr.GetString(0);
}
if (dr != null && dr.IsClosed == false) dr.Close();
if (com != null) com.Dispose();
}
if (conn != null)
{
conn.Close();
conn.Dispose();
}
}
}
catch (Exception exp)
{
}
finally
{
}
}
Exception.Message
未将对象引用设置到对象的实例。
2.2 异常2
public static void Test()
{
try
{
string connectionString = "Database=test;Data Source=127.0.0.1;uid=root;pwd=root;pooling=true;CharSet=utf8;port=3306;Connect Timeout=2;";
string sql = "SELECT (@i:=@i+1) id FROM medicine a, (select @i:= 0) b";
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
using (MySqlCommand com = new MySqlCommand())
{
com.Connection = conn;
com.CommandText = sql;
com.CommandTimeout = 3600;
// 异常位置
MySqlDataReader dr = com.ExecuteReader();
while (dr.Read())
{
string text = dr.GetString(0);
}
if (dr != null && dr.IsClosed == false) dr.Close();
if (com != null) com.Dispose();
}
if (conn != null)
{
conn.Close();
conn.Dispose();
}
}
}
catch (Exception exp)
{
}
finally
{
}
}
Exception.Message
Fatal error encountered during command execution.
(Exception.InnerException).Message
Parameter '@i' must be defined.
二、问题分析
在下面查询语句中包含了用户变量@i，在默认的连接字符串中 Allow User Variables=false;，也就是不支持使用用户变量。如果不进行设置，调用Mysql的接口时就会报 "Fatal error encountered during command execution." + "Parameter '@i' must be defined." 或 "未将对象引用设置到对象的实例。" 的异常。
SELECT (@i:=@i+1) id FROM medicine a, (select @i:= 0) b
三、解决方案
如下列代码，在链接字符串中添加允许用户变量的属性设置 Allow User Variables=True;
public static void Test()
{
try
{
string connectionString = "";
connectionString += "Database=test;";
connectionString += "Data Source=127.0.0.1;";
connectionString += "uid=root;";
connectionString += "pwd=root;";
connectionString += "pooling=true;";
connectionString += "CharSet=utf8;";
connectionString += "port=3306;";
connectionString += "Connect Timeout=2;";
connectionString += "Allow User Variables=True;";
string sql = "SELECT (@i:=@i+1) id FROM medicine a, (select @i:= 0) b";
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
using (MySqlCommand com = new MySqlCommand())
{
com.Connection = conn;
com.CommandText = sql;
com.CommandTimeout = 3600;
MySqlDataReader dr = com.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (dr.Read())
{
string text = dr.GetString(0);
}
if (dr != null && dr.IsClosed == false) dr.Close();
if (com != null) com.Dispose();
}
if (conn != null)
{
conn.Close();
conn.Dispose();
}
}
}
catch (Exception exp)
{
}
finally
{
}
}
