MySQL:根据查询结果设置用户变量 [英] MySQL: Set user variable from result of query
问题描述
是否可以根据MySQL中的查询结果设置用户变量?
Is it possible to set an user variable based on the result of a query in MySQL?
我想要实现的是这样的东西(我们可以假设USER
和GROUP
都是唯一的):
What I want to achieve is something like this (we can assume that both USER
and GROUP
are unique):
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.
推荐答案
是的,但是您需要将变量分配移到查询中:
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;
测试用例:
CREATE TABLE user (`user` int, `group` int);
INSERT INTO user VALUES (123456, 5);
INSERT INTO user VALUES (111111, 5);
结果:
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)
请注意,对于SET
,可以将=
或:=
用作赋值运算符.但是在其他语句中,赋值运算符必须为:=
而不是=
,因为在非SET语句中将=
视为比较运算符.
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.
更新:
在下面的评论中,您还可以执行以下操作:
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;
这篇关于MySQL:根据查询结果设置用户变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!