mysql - 行编号 (@i := @i + 1) doesn't work as expected

Question

SET @i := 0;
SELECT posts.post_id,
       members.member_joined,
       @i := @i + 1
FROM   posts LEFT JOIN members
             ON posts.member_id = members.member_id
WHERE posts.topic_id = 1
ORDER BY posts.post_created ASC;

该查询返回以下结果集:

+---------+---------------------+--------------+
| post_id | member_joined       | @i := @i + 1 |
+---------+---------------------+--------------+
|       1 | 2014-10-14 20:28:15 |            1 |
+---------+---------------------+--------------+
|       3 | 2014-10-14 20:28:15 |            4 |
+---------+---------------------+--------------+
|       4 | 2014-10-14 20:28:15 |            5 |
+---------+---------------------+--------------+
|       5 | 2014-10-14 20:28:15 |            6 |
+---------+---------------------+--------------+
|      14 | 2014-10-14 20:28:15 |            7 |
+---------+---------------------+--------------+
|      17 | 2014-10-14 20:28:15 |            8 |
+---------+---------------------+--------------+
|      35 | 2014-10-14 20:28:15 |            9 |
+---------+---------------------+--------------+
|      37 | 2014-10-14 20:28:15 |            2 | -- What's the 2 doing down here?
+---------+---------------------+--------------+
|      42 | 2014-10-14 20:28:15 |            3 | -- Ditto.
+---------+---------------------+--------------+

如您所见，由于某些我不知道的原因，数字 2 和 3 出现在结果集的最底部，而它们本不该出现。

如果我像这样注释掉 members.member_joined, 行:

SET @i := 0;
SELECT posts.post_id,
       -- members.member_joined,
       @i := @i + 1
FROM   posts LEFT JOIN members
             ON posts.member_id = members.member_id
WHERE posts.topic_id = 1
ORDER BY posts.post_created ASC;

现在的结果集符合预期:

+---------+--------------+
| post_id | @i := @i + 1 |
+---------+--------------+
|       1 |            1 |
+---------+--------------+
|       3 |            2 |
+---------+--------------+
|       4 |            3 |
+---------+--------------+
|       5 |            4 |
+---------+--------------+
|      14 |            5 |
+---------+--------------+
|      17 |            6 |
+---------+--------------+
|      35 |            7 |
+---------+--------------+
|      37 |            8 |
+---------+--------------+
|      42 |            9 |
+---------+--------------+

我实在想不通。可能出了什么问题？

Answer 1

很可能您的排序依据导致计数被关闭。ORDER BY 在选择完成后执行，因此它可以重新排序您的计数。尝试将其放入子查询中。

SET @i := 0;
SELECT post_id, member_joined, @i := @i + 1
FROM
(   SELECT p.post_id, m.member_joined
    FROM posts p
    LEFT JOIN members m ON p.member_id = m.member_id
    WHERE p.topic_id = 1
    ORDER BY p.post_created ASC
)t;

这样想.. SELECT 到 WHERE 正在从表中提取...因此任何特定要求都可以在 WHERE.. GROUP BY 到最后是在请求完成之后并且您正在更改结果回来了......所以这就是为什么顺序可能会导致问题......当你包括加入选择的成员时它返回的结果被计算然后按日期重新排序

注意:

MySQL DOCS对此很清楚:

As a general rule, you should never assign a value to a user variableand read the value within the same statement. You might get theresults you expect, but this is not guaranteed. The order ofevaluation for expressions involving user variables is undefined andmay change based on the elements contained within a given statement;in addition, this order is not guaranteed to be the same betweenreleases of the MySQL Server. In SELECT @a, @a:=@a+1, ..., you mightthink that MySQL will evaluate @a first and then do an assignmentsecond. However, changing the statement (for example, by adding aGROUP BY, HAVING, or ORDER BY clause) may cause MySQL to select anexecution plan with a different order of evaluation.