MySQL 8.0含保留字多查询语句报错，请求语法排查帮助

错误根源 #

rank是MySQL的保留关键字，直接将其作为字段名使用时，MySQL无法正确解析语法，这就是报错的核心原因。

修正方案 #

将所有用作字段名的rank用反引号（`）包裹，同时注意原SQL中的>是HTML转义字符，实际执行需替换为>，否则会引发新的语法错误。

修正后的完整SQL #

SELECT
`rank`
FROM
(
SELECT
    @rn := @rn +1 AS `rank`,
    t1.*
FROM
    (
    SELECT
        *
    FROM
        playdata
    WHERE
        game_id = 629 AND recordtime > '2023-02-06 01:44:01' AND domain_id = 46
    ORDER BY
        score DESC,
        recordtime ASC
    LIMIT 20
) t1,
(
SELECT @rn := 0
) t2
) xx WHERE  field_5='8247db4f-0760-4301-8fcb-272808fde877' AND 
YEARWEEK(recordtime)=YEARWEEK(NOW());

额外优化（MySQL 8.0+适用） #

如果你的MySQL版本是8.0及以上，可以改用窗口函数ROW_NUMBER()替代变量赋值的方式实现排名，写法更简洁规范：

SELECT `rank`
FROM (
    SELECT
        ROW_NUMBER() OVER(ORDER BY score DESC, recordtime ASC) AS `rank`,
        t1.*
    FROM (
        SELECT *
        FROM playdata
        WHERE game_id = 629 AND recordtime > '2023-02-06 01:44:01' AND domain_id = 46
        ORDER BY score DESC, recordtime ASC
        LIMIT 20
    ) t1
) xx 
WHERE field_5='8247db4f-0760-4301-8fcb-272808fde877' AND YEARWEEK(recordtime)=YEARWEEK(NOW());

内容的提问来源于stack exchange，提问作者wdyz