MySQL-在select中定义一个变量，并在同一个select中使用它 [英] MySQL - Define a variable within select and use it within the same 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中使用它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！