mysql - 来自 SELECT 的 SQL UPDATE

Question

为什么这个查询:

UPDATE
  (SELECT A.id trophy_id
   FROM usertrophys A,
        userinfo B
   WHERE A.user_id = B.id
     AND B.cidade_new_id = 25755
     AND ban = 0
     AND A.platform = 'vita'
   ORDER BY points DESC, platinum DESC, gold DESC, silver DESC, bronze DESC, total DESC)      A
LEFT JOIN rankgeralcidade B USING (trophy_id)
SET B.rank = @r:= (@r+1)
WHERE platform = 'vita'
  AND meninas = 0
  AND cidade_id = '25755'
  AND trophy_id = B.trophy_id;</code></pre>

以这个结果结束

select * from rankgeralcidade where cidade_id = 25755 and platform = 'vita' ;
+--------+-----------+----------+---------+------+-----------+-----------+---------------------+---------+---------------------+-----------+
| id     | trophy_id | platform | user_id | rank | last_rank | best_rank | best_rank_date      | meninas | date_updated        | cidade_id |
+--------+-----------+----------+---------+------+-----------+-----------+---------------------+---------+---------------------+-----------+
| 138300 |     86412 | vita     |    2774 |    1 |         1 |         1 | 2013-02-09 18:07:25 |       0 | 2012-12-25 05:20:30 |     25755 |
| 182075 |    120401 | vita     |    3546 |    2 |         0 |         0 | 2014-01-25 19:04:55 |       0 | 2014-01-25 19:04:55 |     25755 |
+--------+-----------+----------+---------+------+-----------+-----------+---------------------+---------+---------------------+-----------+
2 rows in set (0.00 sec)

当后面的select有这个返回

mysql> SELECT @r:= (@r+1), B.*
FROM
  (SELECT A.id trophy_id
   FROM usertrophys A,
        userinfo B
   WHERE A.user_id = B.id
     AND B.cidade_new_id = 25755
     AND ban = 0
     AND A.platform = 'vita'
   ORDER BY points DESC, platinum DESC, gold DESC, silver DESC, bronze DESC, total DESC) A
LEFT JOIN rankgeralcidade B USING (trophy_id);
+-------------+--------+-----------+----------+---------+------+-----------+-----------+---------------------+---------+---------------------+-----------+
| @r:= (@r+1) | id     | trophy_id | platform | user_id | rank | last_rank | best_rank | best_rank_date      | meninas | date_updated        | cidade_id |
+-------------+--------+-----------+----------+---------+------+-----------+-----------+---------------------+---------+---------------------+-----------+
|           1 | 182075 |    120401 | vita     |    3546 |    2 |         0 |         0 | 2014-01-25 19:04:55 |       0 | 2014-01-25 19:04:55 |     25755 |
|           2 | 138300 |     86412 | vita     |    2774 |    1 |         1 |         1 | 2013-02-09 18:07:25 |       0 | 2012-12-25 05:20:30 |     25755 |
+-------------+--------+-----------+----------+---------+------+-----------+-----------+---------------------+---------+---------------------+-----------+
2 rows in set (0.01 sec)

主要问题是，为什么 rankgeralcidade 的 id="182075"更新为 rank=2？在同一个查询中，只需将 UPDATE 替换为 SELECT 结果就可以了。

Answer 1

这是select查询:

SELECT @r:= (@r+1) as rank, B.*
FROM (SELECT A.id trophy_id
      FROM usertrophys A join
           userinfo B
           on A.user_id = B.id
     WHERE B.cidade_new_id = 25755 AND ban = 0 AND A.platform = 'vita'
     ORDER BY points DESC, platinum DESC, gold DESC, silver DESC, bronze DESC, total DESC
    ) A LEFT JOIN
    rankgeralcidade B
    USING (trophy_id);

外部查询没有order by 子句。这意味着未定义结果的排序。在子查询中有一个 order by 子句并没有什么不同。 即使保留了顺序(不保证一定如此)，join 也可能导致数据以不同的方式排序。

update 和 select 会产生不同的顺序，这对我来说非常有意义。

如果您想要稳定的值分配，则在最外层的查询上使用 order by 子句，用于 select 和 order by。

编辑:

我没有意识到 MySQL 不支持 join 的 order by(因为我不使用 order by非常关注更新)。您仍然可以使用子查询执行此操作:

update rankgeralcidade b join
       (<the select query here>
       ) toupdate
       on b.trophy_id = toupdate.trophy_id
    set B.rank = toupdate.rank;