mysql - 使用sql limit从表中反向检索行

Question

我在 MySQL 中使用 SQL 限制 实现分页 db.Records 需要获取

从最后一行向后检索。表中有几千条记录

我想一次检索 500 条记录以显示在页面中。

SELECT * FROM tbl_name ORDER BY some_col DESC

将检索所有记录。

但我不想要所有记录，因为它们有数千个。

如果我用

SELECT * FROM tbl_name ORDER BY some_col DESC LIMIT 500

它将按降序返回最后 500 条记录。

但我找不到从第一个 block 离开的点开始反向检索下一个 500 block 的方法。

Answer 1

SELECT
    <column list since I never use *>
FROM
    My_Table
ORDER BY
    some_column DESC
LIMIT 500, 500

编辑:关于您对 Robert 的回答的评论...性能会随着偏移变大而降低，但明显降低的点通常非常大。来自一两天前我在 paging 上对类似问题的回答:

I am of the strong opinion that a UI should NEVER allow a user to retrieve a set of records that let them go to (for example) page 5000. That's simply too much data for a human mind to find useful all at once and should require further filtering. Maybe let them see the first 100 pages (or some other number), but otherwise they have to constrain the results better. Just my opinion though.