mysql - 在 SELECT 语句中设置变量 mysql

Question

您好，我想知道是否可以假设 SELECT 语句从左到右选择字段，以确保构建一个安全的语句如下(尝试使用尚未设置的变量是不好的):

SELECT @variable := IFNULL(var, 0), value + @variable, somevalue, value2*@variable, ..., @variable/whatever...

FROM table1 LEFT JOIN table2 LEFT JOIN table3

我问这个问题的原因是因为由于使用了 LEFT JOINS，某些元组中的 var 可能等于 null(假设 var 来自表 3)，并且我需要使用该变量如您所见，多次创建衍生字段，因此我不想每次都使用 IFNULL(var, 0)。

Answer 1

来自the docs (我强调):

As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement. For example, to increment a variable, this is okay:

SET @a = @a + 1;

For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed. 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.

因此，如果我们照章办事，那么您问题的答案就是否。