MySQL：根据查询结果设置用户变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3888735/
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: Set user variable from result of query
提问by Avada Kedavra
Is it possible to set an user variable based on the result of a query in MySQL?
是否可以根据 MySQL 中的查询结果设置用户变量？
What I want to achieve is something like this (we can assume that both USERand GROUPare unique):
我想实现的就是这样的事情（我们可以假定这两个USER和GROUP是唯一的）：
set @user = 123456;
set @group = select GROUP from USER where User = @user;
select * from USER where GROUP = @group;
Please note that I know it's possible but I do not wish to do this with nested queries.
请注意，我知道这是可能的，但我不希望使用嵌套查询来执行此操作。
回答by Daniel Vassallo
Yes, but you need to move the variable assignment into the query:
是的，但您需要将变量赋值移动到查询中：
SET @user := 123456;
SELECT @group := `group` FROM user WHERE user = @user;
SELECT * FROM user WHERE `group` = @group;
Test case:
测试用例：
CREATE TABLE user (`user` int, `group` int);
INSERT INTO user VALUES (123456, 5);
INSERT INTO user VALUES (111111, 5);
Result:
结果：
SET @user := 123456;
SELECT @group := `group` FROM user WHERE user = @user;
SELECT * FROM user WHERE `group` = @group;
+--------+-------+
| user | group |
+--------+-------+
| 123456 | 5 |
| 111111 | 5 |
+--------+-------+
2 rows in set (0.00 sec)
Note that for SET, either =or :=can be used as the assignment operator. However inside other statements, the assignment operator must be :=and not =because =is treated as a comparison operator in non-SET statements.
请注意， for SET、=或:=都可以用作赋值运算符。但是在其他语句中，赋值运算符必须是:=而不是=因为=在非 SET 语句中被视为比较运算符。
UPDATE:
更新：
Further to comments below, you may also do the following:
除了下面的评论，您还可以执行以下操作：
SET @user := 123456;
SELECT `group` FROM user LIMIT 1 INTO @group;
SELECT * FROM user WHERE `group` = @group;
回答by Sdz
Just add parenthesis around the query:
只需在查询周围添加括号：
set @user = 123456;
set @group = (select GROUP from USER where User = @user);
select * from USER where GROUP = @group;
回答by dilraj singh
First lets take a look at how can we define a variable in mysql
首先让我们看看我们如何在mysql中定义一个变量
To define a varible in mysql it should start with '@' like @{variable_name} and this '{variable_name}', we can replace it with our variable name.
要在 mysql 中定义一个变量，它应该以 '@' 开头，如 @{variable_name} 和这个 '{variable_name}'，我们可以用我们的变量名替换它。
Now, how to assign a value in a variable in mysql. For this we have many ways to do that
现在，如何在 mysql 中为变量赋值。为此，我们有很多方法可以做到这一点
- Using keyword 'SET'.
- 使用关键字“SET”。
Example :- mysql > SET @a = 1;
示例：- mysql > SET @a = 1;
- Without using keyword 'SET' and using ':='.
- 不使用关键字 'SET' 和使用 ':='。
Example:- mysql > @a:=1;
示例：- mysql > @a:=1;
- By using 'SELECT' statement.
- 通过使用“SELECT”语句。
Example:- mysql > select 1 into @a;
示例：- mysql > select 1 into @a;
Here @a is user defined variable and 1 is going to be assigned in @a.
这里@a 是用户定义的变量，将在@a 中分配1。
Now how to get or select the value of @{variable_name}.
现在如何获取或选择@{variable_name} 的值。
we can use select statement like
我们可以使用 select 语句，如
Example :-
例子 ：-
mysql > select @a;
mysql > 选择@a;
it will show the output and show the value of @a.
它将显示输出并显示@a 的值。
Now how to assign a value from a table in a variable.
现在如何从变量中的表中分配值。
For this we can use two statement like :-
为此，我们可以使用两个语句，如：-
@a := (select emp_name from employee where emp_id = 1);
select emp_name into @a from employee where emp_id = 1;
@a :=（从emp_id = 1的employee中选择emp_name）；
从emp_id = 1的employee中选择emp_name到@a中；
Always be careful emp_name must return single value otherwise it will throw you a error in this type statements.
始终小心 emp_name 必须返回单个值，否则它会在此类型语句中抛出错误。
refer this:- http://www.easysolutionweb.com/sql-pl-sql/how-to-assign-a-value-in-a-variable-in-mysql
参考这个：- http://www.easysolutionweb.com/sql-pl-sql/how-to-assign-a-value-in-a-variable-in-mysql
回答by Rupam
Use this way so that result will not be displayed while running stored procedure.
使用这种方式，在运行存储过程时不会显示结果。
The query:
查询：
SELECT a.strUserID FROM tblUsers a WHERE a.lngUserID = lngUserID LIMIT 1 INTO @strUserID;
