在查询中使用 MySQL 变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2556385/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using MySQL variables in a query
提问by Jon Tackabury
I am trying to use this MySQL query:
我正在尝试使用此 MySQL 查询：
SET @a:=0; UPDATE tbl SET sortId=@a:=@a+1 ORDER BY sortId;
Unfortunately I get this error:
"Parameter '@a' must be defined"
不幸的是，我收到此错误：
“必须定义参数‘@a’”
Is it possible to batch commands into 1 query like this, or do I need to create a stored procedure for this?
是否可以像这样将命令批处理为 1 个查询，或者我是否需要为此创建一个存储过程？
采纳答案by Joshua Jewell
I think you need a stored procedure for any kind of statefullness. Is there a reason you have been reluctant to create one?
我认为您需要一个用于任何状态的存储过程。您是否有理由不愿意创建一个？
Also how are you running this code? Is it in an editor like SQL Server Manager or as a string in a program?
另外你是如何运行这段代码的？它是在 SQL Server Manager 之类的编辑器中还是作为程序中的字符串？
回答by newtover
You placed the variable assignment in a wrong place:
您将变量赋值放在了错误的位置：
SET @a:=0; UPDATE tbl SET @a:=sortId=@a+1 ORDER BY sortId;
回答by Mark Byers
Your query works fine for me. I tried running it from MySQL Query Browser:
您的查询对我来说很好用。我尝试从 MySQL 查询浏览器运行它：
CREATE TABLE tbl (Id INT NOT NULL, SortId INT NOT NULL);
INSERT INTO tbl (Id, SortId) VALUES (1, 9), (2, 22), (3, 13);
SET @a:=0;
UPDATE tbl SET sortId=@a:=@a+1 ORDER BY sortId;
SELECT * From tbl;
Result:
结果：
Id sortId
1 1
2 3
3 2
Note that when running queries from MySQL Query Browser should enter one query per line, not two on one line as you are doing. If you want to put this in a stored procedure (probably a good idea) you can create it like this:
请注意，当从 MySQL 查询浏览器运行查询时，应每行输入一个查询，而不是像您现在所做的那样在一行中输入两个查询。如果你想把它放在一个存储过程中（可能是个好主意），你可以像这样创建它：
DELIMITER //
CREATE PROCEDURE updateSortIds()
BEGIN
SET @a:=0;
UPDATE tbl SET SortId=@a:=@a+1 ORDER BY SortId;
END //
DELIMITER ;
And to execute it, use this:
要执行它，请使用以下命令：
CALL updateSortIds();
