mysql - sql中带参数的order by

Question

我正在尝试在 mysql 查询中使用变量:

SET @resultsPerPage = 10;
SET @offset = 0;

并在查询中:

ORDER BY u.USER_NAME ASC LIMIT @offset, @resultsPerPage 

我收到的错误是:

Error Code: 1064. You have an error in your SQL syntax;

有什么建议吗？

Answer 1

实现分页的一种可能的解决方法是以下列方式使用变量:

SET @resultsPerPage = 10; -- Display 10 records per page
SET @offset = 0;          -- Display the first page

SELECT *
FROM (
   SELECT @rn := @rn + 1 AS rn, u.*
   FROM mytable AS u
   CROSS JOIN (SELECT @rn := 0)
   ORDER BY u.USER_NAME) AS t
WHERE t.rn >= @offset * @resultsPerPage + 1 AND
      t.rn <= (@offset + 1) * @resultsPerPage