MySQL:根据查询结果设置用户变量 [英] MySQL: Set user variable from result of query

查看:38
本文介绍了MySQL:根据查询结果设置用户变量的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

是否可以根据 MySQL 中的查询结果设置用户变量?

Is it possible to set an user variable based on the result of a query in MySQL?

我想要实现的是这样的(我们可以假设 USERGROUP 都是唯一的):

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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆