使用@row_number/rank进行mySQL分页

Question

我正在试着找出我在哪里出了错。这在过去对我很有效，但这可能是因为我使用的是MariaDB，而现在是mySQL 8。我正在尝试使用排名分页。这个SQL在mySQL 8上不再有效吗？有没有更好的方法来做这件事？

SELECT
    * 
FROM
    (
    SELECT
        (@row_number:=@row_number + 1) as 'rank',
        a.* 
    FROM
        (
        SELECT
            deals.id
        FROM
            deals
        ) AS a 
    ORDER BY
        utc_created DESC 
    ) AS x,
    ( SELECT @row := 0 ) AS r 
WHERE
    1 = 1 
    AND rank >= 1 
    AND rank < 51 
ORDER BY
    rank ASC

Answer 1

rank是一个保留字，我认为您的表r放错了位置

SELECT
    * 
FROM
    (
    SELECT
        @rownum := @rownum + 1 'rank',
        a.* 
    FROM
        (
        SELECT
            foo.id
        FROM
            foo
        ) AS a ,
    ( SELECT @rownum := 0 ) AS r 
    ORDER BY
        utc_created DESC 
    ) AS x
WHERE
    1 = 1 
    AND `rank` >= 1 
    AND `rank` < 51 
ORDER BY
    `rank` ASC