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

2021-11-20 00:00:00 mysql mysql-variables

有没有可能做这样的事情?

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;

一切都按预期进行.为什么?

everything works as expected. Why?

推荐答案

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;

相关文章