在MySQL中查找下一行/上一行

Question

在MySQL中查找下一行/上一行

3

我的PHP页面查询一个文件表，用户可以在页面上点击列名按照标题、日期、大小、状态和上传用户姓名进行排序。然后他们可以单击每个文件以在单独的播放器页面中查看它。

我想要做的是在播放器中创建前/后按钮，以按照“文件”页面上的顺序转到前一个或后一个文件，这意味着结果可以按照上述任何参数排序。

对于像日期这样的内容，这很简单：

SELECT * FROM Files WHERE date > curdate ORDER BY date LIMIT 1

然而，其他一些参数给我带来了问题：

- 如何处理上传用户的名称之类的字符串？ - 如何处理排序列中下一个项目具有相同值的情况？例如，状态是介于0和3之间的整数，并且大多数文件的状态为0。如果在“文件”页面上按状态排序，则首先列出所有状态为0的文件，然后是状态1等。因此，如果当前文件位于0状态文件的中间，则如何找到下一个状态也为0的文件？

（附言：我知道这个话题有很多线程，但我没有看到解决上述特定情况的线程。）

- mwoods

有很多关于类似主题的问题，但是很多答案并不真正有效。你已经成功地识别出了在非唯一值上排序的一个关键问题。"诀窍"是按照某些唯一的东西进行排序...首先是你的"major"列(用户说他想按照这个列来排序)，然后是你的秘密"minor"列，它为行提供了唯一的标识符。"诀窍"是使用"last seen"行中的值来开始"next"行。适当的索引将使此过程高效，可能比其他一些流行方法更高效。 - spencer7593

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- spencer7593 · Accepted Answer

问：如何处理上传用户姓名等字符串？

答：与处理日期和数字的方式相同。字符串也可以“排序”。

问：如何处理排序列中下一个项目具有相同值的情况？

答：与处理不唯一日期的重复值相同。除了“主要”排序列外，您需要另一个唯一的“次要”排序列，或者将“主要”和“次要”组合在一起是唯一的。

理想情况下，您应该对非空列拥有 PRIMARY KEY 或 UNIQUE KEY，以用作“次要”排序顺序。

“诀窍”是保存列表中的当前位置，通过保存上一行的主要和次要值，并在查询中使用该信息来获取“下一页”。

    WHERE t.major >= :last_seen_major
      AND (t.major > :last_seen_major OR t.minor > :last_seen_minor)
    ORDER BY t.major ASC, t.minor ASC
    LIMIT 1

从最后一行（在这种情况下，只有一行）中，您需要保存主列和次要列的值，以便可以在同一查询中使用它们来获取“下一”行。

为了获得最佳的查询性能，您需要一个可用的索引，其前导列为（major，minor）。

根据您的查询，假设您有一个 id 列，则应执行以下操作：

SELECT f.*
  FROM Files f 
 WHERE f.date >= :last_seen_date
   AND (f.date > :last_seen_date OR f.id > :last_seen_id)
 ORDER BY f.date ASC, f.id ASC
 LIMIT 1

要按照其他列排序，请将 WHERE 和 ORDER BY 子句中的 f.date 替换为其他内容，例如 f.name。

一个性能较差的替代方案

另一种非常流行的方法是在 LIMIT 子句中使用“偏移量”。

乍一看，这似乎是一种优雅的解决方案，但它确实存在一些问题。

你可以这样做：

ORDER BY major ASC, minor ASC LIMIT 41,1

对于“下一个”行，您需要将偏移量增加1。

ORDER BY major ASC, minor ASC LIMIT 42,1

这种方法的一个问题是，如果在已经检索的行范围内插入一行，则“下一个”查询将返回相同的行。因为原先第41行现在是第42行。如果有人删除了一行，则“下一个”查询会跳过一行。我不想让“获取下一行”的功能出现这种缺陷。而且这种方法仍然需要跟踪列表中的位置，但是通过携带一个额外的偏移量来实现，而这个偏移量并不是行的真正部分。

另一个问题是，数据库必须检索行，然后对它们进行排序，最后应用LIMIT子句，这可能成为大型集合的性能问题。