mysql变量赋值:如何强制赋值顺序? [英] mysql variable assignment : how to force assignment order?

问题描述

由于mysql是一种声明性语言，因此我找不到强制分配变量顺序的方法.

Due to the fact that mysql is a declarative language, I can't find a way to force assignment variable order.

进行此查询:

SET @v1=0;
SET @v2=0;

SELECT @v1, @v2 
FROM MyTable table 
WHERE (@v1:=@v2) is not null 
  AND (@v2:=2) is not null;

结果是:

@v1 | @v2
---------
 2  |  2

这是因为mysql引擎在@ v1之前解析了@ v2.

This is because @v2 is parsed before @v1 by the mysql engine.

如何强制分配顺序获得此结果:

How can I force order of assignment to have this result :

@v1 | @v2
---------
 0  |  2

这与以下问题不同: mysql的可变赋值

这是一个有关强制分配顺序的问题，而不是为什么结果不是预期的结果.

Here is a question about to force assignment order not why the result is not the expected one.

更新: 当您使用左外部联接时，结果也很奇怪:

UPDATE : when you use left outer join, the result is also strange:

SET @v1=0;

SELECT @v1
FROM Account other 
LEFT OUTER JOIN SimpleValue f_PC ON f_PC.accountId=other.id AND f_PC.refShortcut='PC'  
WHERE CASE WHEN (other.context='44') THEN (@v1:=@v1+1) ELSE null END
ORDER BY @v1 ASC

在这种情况下，查询返回60个结果，但@ v1值为120.

In this case, the query returns 60 results but @v1 value is 120.

如果删除左外部联接，则v1值为60.为什么?

If I remove the left outer join, v1 value is 60. Why ?

推荐答案

在select语句中，该子句从LEFT到RIGHT，从TOP到BOTTOM，所以它按预期进行工作(MS Access使用相同的策略) .但是，在WHERE子句中，所有投注均关闭，并且选择了最佳过滤器.您可以利用CASE语句需要从左到右评估(保留显示顺序)这一事实来强制执行此操作.

In a select statement, the clause goes from LEFT to RIGHT, TOP to BOTTOM, so it works out as expected (MS Access uses the same strategy). In a WHERE clause however, all bets are off and the best filter is chosen. You can force it by taking advantage of the fact that the CASE statement requires left to right evaluation (preserving the presentation order).

WHERE CASE WHEN (@v1:=@v2) is not null THEN (@v2:=2) ELSE (@v2:=2) = 0 END

此力是在任一分支中求值(并将2分配给@ v2)，但仅在第一次运行时，@ v1:= @ v2返回null，并且@ v2变为2，将其与整体. @v1 := @v2 [= 2]和(@v2:=2)第二次出现2(即true).

This force is to evaluate (and assign 2 to @v2) in either branch, but for the 1st run ONLY, @v1:=@v2 returns null, and @v2 becomes 2 which is compared against 0 for overall FALSE. The 2nd time around, @v1 := @v2 [= 2], and (@v2:=2) results in 2 (which is true).

这篇关于mysql变量赋值:如何强制赋值顺序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！