使用用户变量对行进行编号时的保证 [英] Guarantees when using user variables to number rows

问题描述

我经常在SO上找到答案，建议使用用户变量对某些事物进行编号.也许最清楚的例子是从给定结果集中选择第二行的查询. (此问题和查询类似于此答案，但它是

I often find answers here on SO suggesting the use of user variables to number some thing or other. Perhaps the clearest example would be a query to select every second row from a given result set. (This question and query is similar to this answer, but it was this answer which actually triggered this question here).

SELECT *
FROM (SELECT *, (@row := @row + 1) AS rownum
      FROM (SELECT @row := 0) AS init, tablename
      ORDER BY tablename.ordercol
     ) sub
WHERE rownum % 2 = 1

这种方法似乎通常有效.

This approach does seem to usually work.

另一方面， MySQ文档状态:

作为一般规则，永远不要为用户变量分配值，并且不要在同一条语句中读取该值.您可能会得到期望的结果，但这不能保证.涉及用户变量的表达式的求值顺序是不确定的，并且可能会根据给定语句中包含的元素而改变；此外，在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.

核心问题

因此，我的问题不是如何使用当前服务器来实现这种排序，而是保证使用用户变量的建议解决方案是否可以在所有(合理)情况下以及在将来的所有版本中使用. MySQL.

Core question

So my question is not how to achieve such an ordering using current servers, but instead whether the suggested solution using user variables is guaranteed to work under all (reasonable) circumstances and for all future versions of MySQL.

通过保证" ，我指的是权威资料，例如MySQL文档或某些标准的MySQL声明所遵循的资料.缺乏这种权威性的答案，可能会引用其他资源，例如常用教程或MySQL源代码的一部分. 作品" 我的意思是这样的事实:分配将依次执行，结果的每一行一次，并按ORDER BY行引起的顺序执行.

By "guarantees" I mean authoritative sources like the MySQL documentation or some standard MySQL claims conformance with. Lacking such authoritative answers, other sources like often-used tutorials or parts of to the MySQL source code might be quoted instead. By "works" I mean the fact that the assignments will be executed sequentially, once per row of the result, and in the order induced by the ORDER BY line.

举一个例子，说明事情如何容易失败:

To give you an example how easily things fail:

SELECT *
FROM (SELECT *, (@row := @row + 1) AS rownum
      FROM (SELECT @row := 0) AS init, tablename
      HAVING rownum > 0
      ORDER BY tablename.ordercol
     ) sub
WHERE rownum % 2 = 1

会在当前安装在 SQL小提琴的MySQL 5.5.27上产生空结果>.原因似乎是HAVING条件导致rownum表达式得到两次求值，因此最终结果将仅具有偶数.我对幕后发生的事情有一个了解，我并不是说使用HAVING进行查询是很有意义的.我只想证明有效的代码与看起来很相似但会中断的代码之间有一条很好的界线.

will produce an empty result on the MySQL 5.5.27 currently installed on SQL Fiddle. The reason appears to be that the HAVING condition causes the rownum expression to get evaluated twice, so the final result will only have even numbers. I have an idea of what's going on behind the scenes, and I'm not claiming that the query with the HAVING makes much sense. I just want to demonstrate that there is a fine line between code which works and code which looks very similar but breaks.

推荐答案

您误读了该语句.使用多个变量时，它与SELECT列表中表达式的顺序有关.
如前所述，此单变量语句上的ORDER BY的命令在MySQL的最新版本之前都具有保证的顺序，并且该文本中没有任何内容表明它将改变.

You misread the statement. It relates to the order of expressions in the SELECT list, when using multiple variables.
As presented, the ORDER BY on this single-variable statement has a guaranteed order up to the current version of MySQL and nothing in that text suggests it will change.

但是保证未来吗?谁知道.

关于中断查询，您再次误解了MySQL的工作方式.让我们分解一下您的查询.注意手册中的这一声明

Regarding the breaking query, you've again misunderstood how MySQL works. Let's break down your query. Take note of this statement in the manual

在SELECT语句中，仅在以下情况下才对每个选择表达式求值: 发送给客户.这意味着在HAVING，GROUP BY或ORDER BY中 子句，引用在select中分配了值的变量 表达式列表无法正常工作

In a SELECT statement, each select expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, referring to a variable that is assigned a value in the select expression list does not work as expected

查询的处理顺序大致

FROM / JOIN
WHERE / ON
GROUP BY / ROLLUP
HAVING
UNION
SELECT
ORDER BY
@variable resolution

您的中断"查询尝试在同一级别使用WITHIN变量，这与对列别名使用WHERE/HAVING子句几乎一样有罪.这就是为什么您永远不会在同一查询级别上看到使用该变量的基于MySQL变量的row_numbering解决方案的原因，它始终在子查询中.可以将外部查询视为内部查询的client，在该阶段已呈现变量/占位符表达式.根据您的说法，您可以使用直接涉及@row的WHERE子句轻松地将其破坏(是的，将运行！).

Your "broken" query attempts to use the variable WITHIN the same level, which is just about as sinful as using a WHERE/HAVING clause against a column alias. That's why you'll never see MySQL variable-based row_numbering solutions using the variable on the same query-level, it is always in a subquery. The outer query can be considered the client of the inner query at which stage the variable/placeholder-expression has been rendered. By your argument, you can just as easily break it using a WHERE clause involving the @row directly (yes it will run!).

这篇关于使用用户变量对行进行编号时的保证的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！