c# - 使用带有 C# 变量的 MySQL 查询

Question

在 MySQL Workbench 中的 MySQL 查询

SET @rownum=-1;
SELECT @rownum:=@rownum+1 AS row_num
FROM someTable

返回一个表，其中 row_num 的值从 0 开始，每行增加 1:

+---------+
| row_num |
+---------+
| 0       |
+---------+
| 1       |
+---------+
| 2       |

  ....

我正在尝试从 C# 执行相同的查询。

string connectionString = "SERVER=" + server + ";" + "DATABASE=" + database + ";" +
    "UID=" + uid + ";" + "PASSWORD=" + password + ";" + "ALLOW USER VARIABLES = true;";

MySqlConnection connection = new MySqlConnection(connectionString);
MySqlCommand command;
MySqlDataAdapter adapter = new MySqlDataAdapter();

connection.Open();
command = connection.CreateCommand();

command.CommandText = "SELECT @rownum := @rownum + 1 AS row_num FROM someTable";
command.Parameters.Add("@rownum", MySqlDbType.Int32);
command.Parameters["@rownum"].Value = -1;
adapter.SelectCommand = command;

DataTable table = new DataTable();
adapter.Fill(table);

上面的最后一行导致以下 MySqlException

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 ':= -1 + 1 AS row_num FROM someTable'

为什么查询在 C# 中不起作用？

Answer 1

删除参数的这些行:

command.Parameters.Add("@rownum", MySqlDbType.Int32);
command.Parameters["@rownum"].Value = -1;

并使用这个查询:

command.CommandText = "SET @rownum=-1;SELECT @rownum := @rownum + 1 AS row_num FROM someTable";

您的做法是“@rownum”都将替换为 -1。所以你最终得到这样的查询:

SELECT -1:= -1 + 1 AS row_num FROM someTable