sql语句中mysql会话变量值的优先级 [英] Precedence of a mysql session variable value in an sql statement
问题描述
在SQL语句中使用会话变量时,标准行为是什么?
What is the standard behaviour of a session variable when used in an SQL statement.
案例1 :
在下面的示例中,会话变量的行为符合预期.
Case 1:
In the following example, session variable is behaving as expected.
mysql> set @m1=0, @m2=0, @m3=0;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select
-> @m1 := 55 m1, @m2 := 42 m2, @m3 := 66 m3,
-> @m1, @m2, @m3,
-> @b1 := greatest( @m1, @m2, @m3 ) b1,
-> @b2 := ( ( @total := @m1 + @m2 + @m3 )
-> - ( @b1 + least( @m1, @m2, @m3 ) )) b2,
-> @total total;
+----+----+----+------+------+------+------+------+-------+
| m1 | m2 | m3 | @m1 | @m2 | @m3 | b1 | b2 | total |
+----+----+----+------+------+------+------+------+-------+
| 55 | 42 | 66 | 55 | 42 | 66 | 66 | 55 | 163 |
+----+----+----+------+------+------+------+------+-------+
1 row in set (0.00 sec)
mysql>
mysql> set @m1=0, @m2=0, @m3=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select
-> @m1 := m1+3, @m2 := m2+3, @m3 := m3+3,
-> @m1, @m2, @m3,
-> @b1 := greatest( @m1, @m2, @m3 ) b1,
-> @b2 := ( ( @total := @m1 + @m2 + @m3 )
-> - ( @b1 + least( @m1, @m2, @m3 ) )) b2,
-> @total total
-> from
-> ( select 55 m1, 42 m2, 66 m3 union all select 48, 63, 92 ) marks_list
-> ;
+-------------+-------------+-------------+------+------+------+------+------+-------+
| @m1 := m1+3 | @m2 := m2+3 | @m3 := m3+3 | @m1 | @m2 | @m3 | b1 | b2 | total |
+-------------+-------------+-------------+------+------+------+------+------+-------+
| 58 | 45 | 69 | 58 | 45 | 69 | 69 | 58 | 172 |
| 51 | 66 | 95 | 51 | 66 | 95 | 95 | 66 | 212 |
+-------------+-------------+-------------+------+------+------+------+------+-------+
2 rows in set (0.00 sec)
上面的示例未使用任何聚合函数.
The above example did not use any of aggregate functions.
案例2 :
当使用诸如count
,sum
,group by
之类的聚合函数执行类似的语句时,结果模式将完全不同.
Case 2:
When a similar statement was executed with aggregate functions like count
, sum
, group by
, the results pattern was entirely different.
请在以下位置找到示例: SQL小提琴
Please find an example on: SQL Fiddle
在发布此查询之前,我试图了解 SQL查询操作顺序 .
Before posting this query, I tried to understand the SQL-Query-Order-of-Operations.
推荐答案
如果我理解正确,那么您正在询问select
语句中表达式的求值顺序. MySQL 文档很清楚,您不能依赖于此:
If I understand correctly, you are asking about the order of evaluation of expressions in select
statements. MySQL documentation is quite clear that you cannot depend on this:
在下面的语句中,您 可能认为MySQL首先会评估@a然后执行 作业第二:
In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second:
SELECT @a, @a:=@a+1, ...;
但是,涉及用户的表达式的求值顺序 变量未定义.
However, the order of evaluation for expressions involving user variables is undefined.
我认为问题是您期望按顺序分配值.这是不正确的.就是说,我必须承认,在使用变量时,确实确实会时不时地做出相同的顺序评估假设,主要是出于懒惰和事实,它通常是有效的.
I think the issue is your expectation that the values are assigned in order. This is incorrect. That said, I must admit that I do make the same sequential evaluation assumption from time-to-time when using variables, primarily out of laziness and the fact that it does generally work.
如果要保证顺序评估,请使用case
技巧:
If you want to guarantee sequential evaluation, then use the case
trick:
select (case when (@m := 55) is null then null
when (@m := 42) is null then null
. . .
end)
case
保证对条件进行顺序评估,直到满足条件为止.与null
的比较可确保对所有内容进行评估.
The case
guarantees sequential evaluation of the conditions until one is true. The comparison to null
ensures that all get evaluated.
这篇关于sql语句中mysql会话变量值的优先级的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!