mysql - 内部连接更新不正确

Question

我遇到了一个我无法解决的问题。例如，我有包含行、ID、季节、剧集、顺序的表格。表格中的数据如下所示:

+--------+---------------+----------------+--------------+
| id     |     season    |     episode    |     order    |
+--------+---------------+----------------+--------------+
| 153914 | 1             | 1              |         NULL |
| 153915 | 1             | 3              |         NULL |
| 153916 | 1             | 2              |         NULL |
| 153919 | 1             | 3              |         NULL |
| 153920 | 1             | 4              |         NULL |
| 153921 | 1             | 3              |         NULL |
+--------+---------------+----------------+--------------+

因此，当我在不使用 UPDATE 的情况下运行 SELECT 查询时，行顺序绝对正确排序

SELECT id, season, episode, (@row:=@row+1) as order
                            FROM `shows`, (select @row:=0) as rc
                            WHERE `show_id`= 14670
                            ORDER BY CAST(season AS UNSIGNED) ASC, CAST(episode AS UNSIGNED) ASC

+--------+--------+---------+--------+
| id     | season | episode | order  |
+--------+--------+---------+--------+
| 153914 | 1      | 1       |      1 |
| 153916 | 1      | 2       |      2 |
| 153915 | 1      | 3       |      3 |
| 153919 | 1      | 3       |      4 |
| 153921 | 1      | 3       |      5 |
| 153920 | 1      | 4       |      6 |
+--------+--------+---------+--------+

但是当我使用相同的查询作为 UPDATE 语句的子查询时，它不会以相同的方式排序并设置不同的顺序值。

UPDATE `shows` f
JOIN
(
    SELECT id, (@row:=@row+1) as rowOrder
    FROM `shows` as Fl, (select @row:=0) as rc
    WHERE Fl.`show_id` = 14670
    ORDER BY Fl.season ASC, Fl.episode ASC
) t 
ON t.id = f.id 
SET f.order = t.rowOrder

mysql> SELECT id, season, episode, order FROM `shows`  WHERE `show_id`=14670;
+--------+--------+---------+--------+
| id     | season | episode | order  |
+--------+--------+---------+--------+
| 153914 | 1      | 1       |      1 |
| 153915 | 1      | 3       |      2 |
| 153916 | 1      | 2       |      3 |
| 153919 | 1      | 3       |      4 |
| 153920 | 1      | 4       |      5 |
| 153921 | 1      | 3       |      6 |
+--------+--------+---------+--------+

请向我解释为什么会发生以及如何解决。MySQL版本:

>mysql --version
mysql  Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using  EditLine wrapper

Answer 1

嗯。看来 order by 不会影响变量。我想知道这在最新版本的 MySQL 中是否有所改变。它确实曾经有效。

在任何情况下，您都可以使用子查询来修复它:

UPDATE shows s JOIN
       (SELECT id, (@row:=@row+1) as rowOrder
        FROM (SELECT id, sea
              FROM shows s2
              WHERE s2.show_id = 14670
              ORDER BY s2.season ASC, s2.episode ASC
             ) s2 CROSS JOIN
             (SELECT @row := 0) as rc
       ) s3
       ON s.id = s3.id 
    SET s.order = s3.rowOrder;