MySQL-在select中定义一个变量，并在同一个select中使用它

问题描述：

是否可以做这样的事情?

Is there a possibility to do something like this?

SELECT 
    @z:=SUM(item),
    2*@z
FROM
    TableA;

第二列我总是得到NULL.奇怪的是，虽然做类似的事情

I always get NULL for the second column. The strange thing is, that while doing something like

SELECT 
    @z:=someProcedure(item),
    2*@z
FROM
    TableA;

一切正常.为什么?

答

MySQL 文档对此很清楚:

MySQL documentation is quite clear on this:

作为一般规则，永远不要为用户变量分配值并读取同一条语句中的值.您可能会得到您期望的结果，但是不能保证.的顺序未定义涉及用户变量的表达式的求值，并且可能会根据给定语句中包含的元素进行更改；此外，此顺序不能保证在 MySQL服务器的发行版本.在SELECT @ a，@ a:= @ a + 1，...中认为MySQL将首先评估@a然后进行赋值第二.但是，更改语句(例如，通过添加一个 GROUP BY，HAVING或ORDER BY子句)可能会导致MySQL选择具有不同评估顺序的执行计划.

As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed. The order of evaluation for expressions involving user variables is undefined and may change based on the elements contained within a given statement; in addition, this order is not guaranteed to be the same between releases of the MySQL Server. In SELECT @a, @a:=@a+1, ..., you might think that MySQL will evaluate @a first and then do an assignment second. However, changing the statement (for example, by adding a GROUP BY, HAVING, or ORDER BY clause) may cause MySQL to select an execution plan with a different order of evaluation.

您可以使用子查询来执行所需的操作:

You can do what you want using a subquery:

select @z, @z*2
from (SELECT @z:=sum(item)
      FROM TableA
     ) t;

MySQL-在select中定义一个变量，并在同一个select中使用它

相关推荐