如何在VB.NET查询中使用MySQL变量？执行遇参数识别错误

问题背景 #

在VB.NET中执行包含MySQL变量（如@sum）的查询时，触发错误：Fatal error encountered during command execution.。核心原因是MySqlCommand会将查询字符串中@开头的标识符识别为参数占位符，而非MySQL原生的会话变量，导致查询执行失败。

用户需要实现每行的累计余额计算，该查询在phpMyAdmin中可正常运行，但在VB.NET中无法执行。

测试用表结构与数据 #

CREATE TABLE `stack` (
  `id` int(11) NOT NULL,
  `debe` double NOT NULL,
  `haber` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `stack` (`id`, `debe`, `haber`) VALUES
(1, 100, 0),
(2, 100, 0),
(3, 100, 0),
(4, 100, 0),
(5, 0, 50),
(6, 100, 0),
(7, 0, 150),
(8, 100, 0),
(9, 100, 0),
(10, 100, 0),
(11, 0, 100),
(12, 100, 0);

ALTER TABLE `stack`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `stack`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;
COMMIT;

预期输出为每行的累计余额（如第一行Saldo=100，第二行Saldo=200，第五行Saldo=350等）。

用户尝试过的错误写法 #

写法1：直接拼接查询字符串 #

dim query as string = "SET @sum = 0; SELECT @sum := (@sum + debe - haber) AS 'Saldo' from cccustomers WHERE customer = 443"
using con
    If con.State = ConnectionState.Closed Then con.Open()
    Using cmd As New MySqlCommand(query, con)
        Dim adp As New MySqlDataAdapter(cmd)
        Dim dt As New DataTable
        adp.Fill(dt)
        DataGridView1.DataSource = dt
    end using
end using

写法2：通过变量拼接字符串 #

dim sums as string = "@sum"
dim query as string = "SET " & sums & " = 0; SELECT " & sums & " := (" & sums & " + debe - haber) AS 'Saldo' from cccustomers WHERE customer = 443"

写法3：错误添加参数 #

dim query as string = "SET @sum = 0; SELECT @sum := (@sum + debe - haber) AS 'Saldo' from cccustomers WHERE customer = 443"
using con
    If con.State = ConnectionState.Closed Then con.Open()
    Using cmd As New MySqlCommand(query, con)
        cmd.Parameters.AddWithValue("@sum", "@sum")
        Dim adp As New MySqlDataAdapter(cmd)
        Dim dt As New DataTable
        adp.Fill(dt)
        DataGridView1.DataSource = dt
    end using
end using

该写法会将@sum作为字符串'@sum'传递，触发MySQL语法错误。

可行解决方案 #

方法1：修改MySqlCommand的参数前缀 #

MySqlCommand支持自定义参数占位符前缀，将其改为MySQL不使用的符号（如?），即可让查询中的@sum被识别为MySQL变量而非参数。

代码示例：

dim query as string = "SET @sum = 0; SELECT debe, haber, @sum := (@sum + debe - haber) AS 'Saldo' from stack;"
using con
    If con.State = ConnectionState.Closed Then con.Open()
    Using cmd As New MySqlCommand(query, con)
        ' 修改参数前缀为?，避免与MySQL的@变量冲突
        cmd.ParameterPrefix = "?"
        Dim adp As New MySqlDataAdapter(cmd)
        Dim dt As New DataTable
        adp.Fill(dt)
        DataGridView1.DataSource = dt
    end using
end using

方法2：拆分变量初始化与查询执行 #

将变量初始化和累计查询拆分为两个独立的MySqlCommand，在同一个连接会话中先初始化变量，再执行查询，确保@sum被识别为已存在的会话变量。

代码示例：

using con
    If con.State = ConnectionState.Closed Then con.Open()
    ' 第一步：初始化MySQL会话变量@sum
    Using initCmd As New MySqlCommand("SET @sum = 0;", con)
        initCmd.ExecuteNonQuery()
    End Using
    ' 第二步：执行累计余额查询
    Using queryCmd As New MySqlCommand("SELECT debe, haber, @sum := (@sum + debe - haber) AS 'Saldo' from stack;", con)
        Dim adp As New MySqlDataAdapter(queryCmd)
        Dim dt As New DataTable
        adp.Fill(dt)
        DataGridView1.DataSource = dt
    End Using
end using

方法3：改用窗口函数实现累计计算（无需MySQL变量） #

若使用MySQL 8.0及以上版本，可利用窗口函数SUM() OVER()替代会话变量，避免参数机制冲突，同时符合现代SQL规范。

查询语句修改为：

SELECT 
    debe, 
    haber, 
    SUM(debe - haber) OVER (ORDER BY id) AS 'Saldo' 
from stack;

对应的VB.NET代码：

dim query as string = "SELECT debe, haber, SUM(debe - haber) OVER (ORDER BY id) AS 'Saldo' from stack;"
using con
    If con.State = ConnectionState.Closed Then con.Open()
    Using cmd As New MySqlCommand(query, con)
        Dim adp As New MySqlDataAdapter(cmd)
        Dim dt As New DataTable
        adp.Fill(dt)
        DataGridView1.DataSource = dt
    end using
end using

内容的提问来源于stack exchange，提问作者Liyo