mysql表中的条目排名

Question

我有一个包含很多行的 MySQL 表。该表有一个受欢迎度列。如果我按受欢迎程度排序，我可以获得每个项目的排名。是否可以在不对整个表进行排序的情况下检索特定项目的排名？我不这么认为。这是正确的吗？

另一种方法是创建一个新列来存储排名，对整个表进行排序，然后循环所有行并更新排名。这是极其低效的。有没有办法在单个查询中做到这一点？

Answer 1

如果不首先对表进行排序或存储排名，则无法计算某些内容的顺序（即所谓的排名）。

但是，如果您的表已正确索引（流行度索引），那么数据库对其进行排序就很容易获得排名。我建议如下：

全选，包括排名

SET @rank := 0;
SELECT t.*, @rank := @rank + 1
FROM table t
ORDER BY t.popularity;

要获取具有特定“id”的项目，那么您可以简单地使用子查询，如下所示：

选择一个，包括排名

SET @rank := 0;
SELECT * FROM (
  SELECT t.*, @rank := @rank + 1
  FROM table t
  ORDER BY t.popularity
) t2
WHERE t2.id = 1;

Answer 2

如果在每次读取表时更新排名列，那么第二种方法效率低下，这是正确的。但是，根据数据库的更新数量，您可以计算每个更新的排名，并将其存储 - 这是一种缓存形式。然后，您将计算字段转换为固定值字段。

该视频介绍了 mysql 中的缓存，尽管它是特定于 Rails 的，并且一种略有不同的缓存形式，是一种非常相似的缓存策略。

Answer 3

如果您使用的是 InnoDb 表，那么您可以考虑在流行度列上构建聚集索引。 （仅当流行度排序是频繁查询时）。该决定还取决于受欢迎程度列的变化程度（0 - 3 不太好）。

您可以查看聚集索引上的此信息，看看这是否适合您的情况： http://msdn.microsoft.com/en-us/library/ms190639.aspx

这是指SQL Server，但概念是相同的，也可以查找有关此的mysql文档。

Answer 4

如果您使用 PDO 执行此操作，则需要将查询修改为全部位于单个语句内，以便使其正常工作。请参阅 PHP/PDO/MySQL：将多个查询转换为单个查询< /a>

所以 hobodave 的答案变成了这样：

SELECT t.*, (@count := @count + 1) as rank
FROM table t 
CROSS JOIN (SELECT @count := 0) CONST
ORDER BY t.popularity;

Answer 5

hobodave的解决方案非常好。或者，您可以添加一个单独的排名列，然后，每当行的受欢迎程度发生更新时，查询以确定该受欢迎程度更新是否更改了相对于其上方和下方的行的排名，然后 更新受影响的 3 行。您必须进行分析才能确定哪种方法更有效。