WHERE 子句中的 MySQL 用户定义变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3984019/
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
MySQL user-defined variable in WHERE clause
提问by Paulo Freitas
I want to know if there is a way to use a user-defined variable in WHEREclause, as in this example:
我想知道是否有办法在WHERE子句中使用用户定义的变量，如本例所示：
SELECT id, location, @id := 10 FROM songs WHERE id = @id
This query runs with no errors but doesn't work as expected.
此查询运行时没有错误，但没有按预期工作。
回答by Maxym
Not far from what Mike E. proposed, but one statement:
与 Mike E. 的提议相去不远，但有一个声明：
SELECT id, location FROM songs, ( SELECT @id := 10 ) AS var WHERE id = @id;
I used similar queries to emulate window functions in MySQL. E.g. Row sampling- just an example of using variables in the same statement
我使用类似的查询来模拟 MySQL 中的窗口函数。例如行抽样——只是在同一语句中使用变量的一个例子
回答by steampowered
From the MySQL manual page on User Defined Variables:
从用户定义变量的 MySQL 手册页：
As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed.
作为一般规则，您永远不应为用户变量赋值并在同一语句中读取该值。您可能会得到预期的结果，但这并不能保证。
So you should separate the assignment from the select statement:
因此，您应该将赋值与 select 语句分开：
SET @id = 10;
SELECT id, location, @id FROM songs WHERE id = @id;
回答by Mike E.
Sure, but I've never seen anyone try to set a variable and use it in the same statement like you are. Try:
当然，但我从未见过有人像您一样尝试设置变量并在同一语句中使用它。尝试：
SET @id := 10;
SELECT @id := 10 FROM songs WHERE id = @id;
or
或者
SELECT @id := 10 FROM songs;
SELECT @id := 10 FROM songs WHERE id = @id;
I've used both, and they both seem to work for me.
我都用过，它们似乎都适合我。
回答by Jonas
This worked for me!
这对我有用！
SET @identifier = 7;
SELECT * FROM test where identifier = @identifier;
