mysql - MySQL中以下变量初始化风格有什么区别？

Question

我对涉及 MySQL 中的变量声明的查询相当陌生。我见过各种样式，但我并不完全清楚它们的实际作用。我对它们的实际作用有疑问。

1)

set @row:=0;
SELECT name, @row:=@row + 1 AS rownum
FROM animal

2)

SELECT name, @row:=@row + 1 AS rownum
FROM (SELECT @row:= 0) c, animal

两者返回相同:

  name rownum
|| cat || 1 ||
|| cat || 2 ||
|| dog || 3 ||
|| dog || 4 ||
|| dog || 5 ||
|| ant || 6 ||

以上两种查询有何不同，在范围、效率、编码习惯、用例等方面采用哪一种？

3) 现在如果我这样做:

set @row:=0;
SELECT name, @row:=@row + 1 AS rownum
FROM (SELECT @row:= 123) c, animal

我明白了

  name  rownum
|| cat || 124 ||
|| cat || 125 ||
|| dog || 126 ||
|| dog || 127 ||
|| dog || 128 ||
|| ant || 129 ||

那么这是否意味着内部变量初始化覆盖了外部初始化并因此使后者变得多余(因此在 SELECT 中初始化始终是更好的做法？

4) 如果我只是这样做:

SELECT name, @row:=@row + 1 AS rownum
FROM animal

我明白了

   name  rownum
|| cat || NULL ||
|| cat || NULL ||
|| dog || NULL ||
|| dog || NULL ||
|| dog || NULL ||
|| ant || NULL ||

我可以理解，因为 row 没有初始化。但是，如果我运行任何其他查询(可能是变量 row 正在初始化？)，我会看到每次运行上述查询时 row 变量都会递增。那就是它给了我第一次运行的结果:

  name rownum
|| cat || 1 ||
|| cat || 2 ||
|| dog || 3 ||
|| dog || 4 ||
|| dog || 5 ||
|| ant || 6 ||

然后在重新运行时它产生

  name rownum
|| cat || 7  ||
|| cat || 8  ||
|| dog || 9  ||
|| dog || 10 ||
|| dog || 11 ||
|| ant || 12 ||

row 是否存储在某处？它的范围和生命周期是多少？

5) 如果我有这样的查询:

SELECT (CASE WHEN @name <> name THEN @row:=1 ELSE @row:=@row + 1 END) AS rownum, 
       @name:=name AS name
FROM animal

这总是会产生正确的结果:

rownum  name
|| 1 || cat ||
|| 2 || cat ||
|| 1 || dog ||
|| 2 || dog ||
|| 3 || dog ||
|| 1 || ant ||

那么这是否意味着它并不总是需要根据查询在顶部或 SELECT 中初始化变量？

Answer 1

请务必阅读 manual section on user variables .

What are the differences in the above two queries and which of the two to adopt as to their scope, efficiency, coding habit, use-cases?

查询 1) 使用多个语句。因此，它可以依赖于这些语句的执行顺序，确保变量在递增之前设置。
另一方面，查询 2) 在嵌套子查询中进行初始化。这会将整个事情变成一个查询。您不会冒险忘记初始化。但是代码更多地依赖于 mysql 服务器的内部工作，特别是它会在开始为外部查询计算结果之前执行子查询。

So doesn't that mean that the inner variable initialization is overriding the outer initialization and leaving the latter redundant hence (and hence its always a better practice to initialize in a SELECT?

这不是关于内在和外在的，而是关于顺序的:子查询在SET之后执行。 , 所以它会简单地覆盖旧值。

So is row being stored somewhere? And what is its scope and lifespan?

用户变量是服务器连接的本地变量。因此任何其他进程都不会受到该设置的影响。即使是同一个进程也可能维护多个连接，用户变量的设置是独立的。连接关闭后，所有变量设置都会丢失。

So doesn't that mean its not always necessary to initialize variable at the top or in a SELECT depending on the query?

引自the manual :

If you refer to a variable that has not been initialized, it has a value of NULL and a type of string.

因此您可以在变量初始化之前使用它，但您必须小心，您实际上可以处理结果NULL。以合理的方式估价。但是请注意，您的查询 5) 遇到手册中明确指出的另一个问题:

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.

所以在你的例子中，@name:=name部分可以在 @name <> name 之前执行检查，导致你所有的rownum值相同。因此，即使它现在有效，也不能保证它在未来会有效。

请注意，我一直对以这种方式使用用户变量持怀疑态度。我已经在对几个答案的评论中引用了手册中的上述警告。我也问过关于 Guarantees when using user variables to number rows 的问题.其他用户更务实，因此更愿意使用看起来可以正常工作的代码，而无需明确保证事情将继续按预期工作。