mysql - MySQL 中用户定义变量的奇怪行为

Question

我正在构建一个报告并使用用户定义的变量来使查询尽可能紧凑。我在 MySQL Workbench 和 PHP 中注意到的是它们并不总是正常工作。

例如:

SELECT
@NewSales := SUM(CASE WHEN `v`.`new_used`='N' THEN 1 ELSE 0 END) AS `NewSales`,
@UsedSales := SUM(CASE WHEN `v`.`new_used`='U' THEN 1 ELSE 0 END) AS `UsedSales`,
@UsedSales + @NewSales AS `TotalSales`
FROM `fi_sales` `s`
LEFT JOIN `vehicles` `v` ON `v`.`stock_number`=`s`.`stock_number`

如果我在 Workbench 中运行上述查询，第一次运行输出 TotalSales=NULL:

NewSales, UsedSales, TotalSales
3418, 2889, NULL

如果我刷新查询，输出会产生 TotalSales 的预期结果:

NewSales, UsedSales, TotalSales
3418, 2889, 6307.000000000000000000000000000000

有点奇怪；几乎就好像该变量在设置它的同一查询中不可用一样。我通常通过在不使用变量的情况下重现计算来解决这个问题。

我的下一个问题是，如果我将相同的查询从 Workbench 复制到我的应用程序 (PHP) 中，TotalSales 输出将产生“0”零。

我确信这里有一个非常好的解释，但我找不到它。非常感谢任何答案。

Answer 1

您处于非确定性领域，在查询中使用用户定义的变量来更改它们，并且解释很简单:您得到的答案实际上来自同一查询的前一次运行。

UDV 的范围仅限于您的个人数据库连接。它们的值在查询之间持续存在，但在连接之间不存在。此查询为您提供 @UsedSales + @NewSales 的值，该值来自查询运行之前，而不是之后。 (为什么？因为它就是这样。没有理由......它可能会发生任何变化。见下文。)

SET @UsedSales = 1, @NewSales = 2; 并再次运行您的查询。 Total 在下一次运行中将是 3，显然是错误的答案(与您的预期相比)但从服务器可以自由地以其喜欢的任何顺序解析这些问题的意义上来说并没有错，因为它们 < em>看起来像常量。

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

...

The order of evaluation for expressions involving user variables is undefined.

https://dev.mysql.com/doc/refman/5.7/en/user-variables.html

您正在尝试解决一个实际上不是问题的问题。

相反，要么这样做:

SELECT
SUM(CASE WHEN `v`.`new_used`='N' THEN 1 ELSE 0 END) AS `NewSales`,
SUM(CASE WHEN `v`.`new_used`='U' THEN 1 ELSE 0 END) AS `UsedSales`,
SUM(CASE WHEN `v`.`new_used` IN ('N','U') THEN 1 ELSE 0 END) AS `TotalSales`
FROM `fi_sales` `s`
LEFT JOIN `vehicles` `v` ON `v`.`stock_number`=`s`.`stock_number`;

或者如果您坚持，创建一个派生表(此处命名为 x)并对结果列进行额外添加。

SELECT
x.NewSales, 
x.UsedSales, 
x.NewSales + x.UsedSales AS TotalSales
FROM (
  SELECT
  SUM(CASE WHEN `v`.`new_used`='N' THEN 1 ELSE 0 END) AS `NewSales`,
  SUM(CASE WHEN `v`.`new_used`='U' THEN 1 ELSE 0 END) AS `UsedSales`
  FROM `fi_sales` `s`
  LEFT JOIN `vehicles` `v` ON `v`.`stock_number`=`s`.`stock_number`
) x;

这会将内部结果具体化到一个临时表中，该临时表会在查询执行完毕后立即被丢弃。

---

或者，如果你真的想要聪明和简短，那就用这个:

SELECT
  COUNT(`v`.`new_used`='N' OR NULL) AS `NewSales`,
  COUNT(`v`.`new_used`='U' OR NULL) AS `UsedSales`,
  COUNT(`v`.`new_used` IN ('N','U') OR NULL) AS `TotalSales`
FROM `fi_sales` `s`
LEFT JOIN `vehicles` `v` ON `v`.`stock_number`=`s`.`stock_number`;

这行得通，因为 COUNT() 仅计算具有非空参数的行，并且任何表达式 expr OR NULL 强制 expr 被评估为 bool 表达式，因此在逻辑上等同于 CASE WHEN expr [IS TRUE] THEN 1 ELSE NULL END 因此只能评估为 1(如果 expr 为真)...或 NULL(如果 expr 为 false 或 null)...这与 COUNT() 的工作方式完全匹配。