variable value mysql,Precedence of a mysql session variable value in an sql statement

问题

What is the standard behaviour of a session variable when used in an SQL statement.

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.

Case 2:

When a similar statement was executed with aggregate functions like count, sum, group by, the results pattern was entirely different.

Please find an example on: SQL Fiddle

Before posting this query, I tried to understand the SQL-Query-Order-of-Operations.

回答1:

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:

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.

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)

The case guarantees sequential evaluation of the conditions until one is true. The comparison to null ensures that all get evaluated.

来源：https://stackoverflow.com/questions/22388419/precedence-of-a-mysql-session-variable-value-in-an-sql-statement