MySQL - 在 select 中定义一个变量并在同一个 select 中使用它
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16715504/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL - Define a variable within select and use it within the same select
提问by user2370579
Is there a possibility to do something like this?
有没有可能做这样的事情？
SELECT
@z:=SUM(item),
2*@z
FROM
TableA;
I always get NULL for the second column. The strange thing is, that while doing something like
对于第二列，我总是得到 NULL。奇怪的是，在做类似的事情时
SELECT
@z:=someProcedure(item),
2*@z
FROM
TableA;
everything works as expected. Why?
一切都按预期工作。为什么？
回答by Gordon Linoff
MySQL documentationis quite clear on this:
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.
作为一般规则，您永远不应为用户变量赋值并在同一语句中读取该值。您可能会得到预期的结果，但这并不能保证。涉及用户变量的表达式的求值顺序未定义，可能会根据给定语句中包含的元素而改变；此外，不保证此顺序在 MySQL 服务器的版本之间是相同的。在 SELECT @a, @a:=@a+1, ... 中，您可能认为 MySQL 会先评估 @a，然后再进行赋值。但是，更改语句（例如，通过添加 GROUP BY、HAVING 或 ORDER BY 子句）可能会导致 MySQL 选择具有不同评估顺序的执行计划。
You can do what you want using a subquery:
您可以使用子查询执行您想要的操作：
select @z, @z*2
from (SELECT @z:=sum(item)
FROM TableA
) t;
回答by Ravi Parekh
Works in mysql 5.5
适用于 mysql 5.5
select @code:=sum(2), 2*@code
+---------------+---------+
| @code:=sum(2) | 2*@code |
+---------------+---------+
| 2 | 4 |
+---------------+---------+
回答by pala_
mysql> select @z := sum(5), if(@z := sum(5), 2*@z, 0) ;
+--------------+------------------------------+
| @z := sum(5) | if(@z := sum(5), 2*@z, null) |
+--------------+------------------------------+
| 5 | 10 |
+--------------+------------------------------+
I believe wrapping the 2*@zin the ifstatement will ensure the sumis performed BEFORE the additional calculation.
我相信将 包装2*@z在if语句中将确保sum在附加计算之前执行。
