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

Question

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

我想做的是在播放器中创建上一个/下一个按钮以转到上一个或下一个文件在"file"页面上按顺序。这意味着结果可以按上面列出的任何参数排序。

对于像日期这样的东西，这很简单:

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

但是，其他一些参数给我带来了问题:

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

• 如何处理排序列中的下一项具有相同值的情况？例如，status 是一个介于 0 和 3 之间的整数，大多数文件的状态为 0。如果我在"file"页面上按状态排序，它会首先列出所有状态为 0 的文件，然后是状态 1，等等。所以如果我的当前文件位于状态为 0 的文件中间，如何找出下一个状态也为 0 的文件？

(附:我知道有很多关于这个主题的话题，但我还没有看到一个解决上述特定情况的话题。)

Answer 1

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

答:与您处理日期和数字的方式相同。字符串也是“可订购的”。

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

答:这与您处理非唯一日期的重复值的方式相同。除了“主要”排序列外，您还需要另一个唯一的“次要”排序列，或者“主要”和“次要”的组合是唯一的。

理想情况下，您在不可为 null 的列上有一个 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 子句，这可能是大型集合的性能问题。