嵌套SQL查询中的Order

Question

我想请您帮助我在使用MySQL中的"order“指令时遇到的一个奇怪的事实。

让我们看下表：

CREATE TABLE `test_nested_order_by` (
  `id` int(11) NOT NULL,
  `timestamp` int(11) NOT NULL COMMENT 'Timestamp',
  `index_continuity_month` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into test_nested_order_by (id,timestamp,index_continuity_month) values (1,1583772141,0) ;
insert into test_nested_order_by (id,timestamp,index_continuity_month) values (1,1583708400,0) ;
insert into test_nested_order_by (id,timestamp,index_continuity_month) values (5,1583708400,0) ;
insert into test_nested_order_by (id,timestamp,index_continuity_month) values (4,1583708400,0) ;
insert into test_nested_order_by (id,timestamp,index_continuity_month) values (5,1583794800,0) ;
insert into test_nested_order_by (id,timestamp,index_continuity_month) values (4,1583794800,0) ;

正如您所看到的，0值是为列"index_continuity_month“在每一行设置的。

现在，我希望将这个值设置为:一个唯一的值，它将以id和时间戳列的升序递增。这张桌子看起来应该是：

mysql>  select * from test_nested_order_by :

+----+------------+------------------------+
| id | timestamp  | index_continuity_month |
+----+------------+------------------------+
|  1 | 1583772141 |                      2 |
|  1 | 1583708400 |                      1 |
|  5 | 1583708400 |                      5 |
|  4 | 1583708400 |                      3 |
|  5 | 1583794800 |                      6 |
|  4 | 1583794800 |                      4 |
+----+------------+------------------------+
6 rows in set (0,00 sec)

或者，如果你喜欢：

mysql> select * from test_nested_order_by order by id,timestamp ;
+----+------------+------------------------+
| id | timestamp  | index_continuity_month |
+----+------------+------------------------+
|  1 | 1583708400 |                      1 |
|  1 | 1583772141 |                      2 |
|  4 | 1583708400 |                      3 |
|  4 | 1583794800 |                      4 |
|  5 | 1583708400 |                      5 |
|  5 | 1583794800 |                      6 |
+----+------------+------------------------+

为此，我使用以下查询：

UPDATE  test_nested_order_by t1,
(SELECT
id,
timestamp,
@last_continuity_month := @last_continuity_month +1, @last_continuity_month AS index_continuity_month

FROM test_nested_order_by, (

SELECT @last_continuity_month :=0
)SQLVars
ORDER BY id , timestamp) t2

SET t1.index_continuity_month = t2.index_continuity_month

WHERE t1.id = t2.id
      AND t1.timestamp = t2.timestamp;

但当我看到结果时，它似乎不起作用：

mysql> select * from test_nested_order_by order by id,timestamp ;
+----+------------+------------------------+
| id | timestamp  | index_continuity_month |
+----+------------+------------------------+
|  1 | 1583708400 |                      2 |
|  1 | 1583772141 |                      1 |
|  4 | 1583708400 |                      4 |
|  4 | 1583794800 |                      6 |
|  5 | 1583708400 |                      3 |
|  5 | 1583794800 |                      5 |
+----+------------+------------------------+
6 rows in set (0,00 sec)

我怀疑"order“指令没有被考虑在内(如果我从查询中删除它，结果是完全相同的)。

我们可以注意到，index_continuity_month的增量并不是按照id列和时间戳列的升序进行的，而是按照在表中插入行的顺序进行的。

但是，如果我只运行查询的嵌套部分：

SELECT
id,
timestamp,
@last_continuity_month := @last_continuity_month +1, @last_continuity_month AS index_continuity_month

FROM test_nested_order_by, (

SELECT @last_continuity_month :=0
)SQLVars
ORDER BY id , timestamp;

+----+------------+-----------------------------------------------------+------------------------+
| id | timestamp  | @last_continuity_month := @last_continuity_month +1 | index_continuity_month |
+----+------------+-----------------------------------------------------+------------------------+
|  1 | 1583708400 |                                                   1 |                      1 |
|  1 | 1583772141 |                                                   2 |                      2 |
|  4 | 1583708400 |                                                   3 |                      3 |
|  4 | 1583794800 |                                                   4 |                      4 |
|  5 | 1583708400 |                                                   5 |                      5 |
|  5 | 1583794800 |                                                   6 |                      6 |
+----+------------+-----------------------------------------------------+------------------------+

结果很好！

有人能解释我出了什么问题吗？更具体地说，为什么SQL查询在嵌套到另一个查询时没有相同的行为？

非常感谢！

Answer 1

子查询结果是一组无序的行。因此，MySQL优化器可以忽略子查询中的ORDER BY。