“洗牌"的最佳方法是什么?数据库记录表? [英] What's the best way to "shuffle" a table of database records?

问题描述

说我有一个带有一堆记录的表，我想将这些记录随机呈现给用户.我还希望用户能够来回分页，所以我必须至少保留一段时间的某种顺序.

Say that I have a table with a bunch of records, which I want to randomly present to users. I also want users to be able to paginate back and forth, so I have to perserve some sort of order, at least for a while.

该应用程序基本上只是AJAX，它对已访问的页面使用缓存，因此即使我总是提供随机结果，当用户尝试返回时，他也会获得上一页，因为它将从本地缓存加载

The application is basically only AJAX and it uses cache for already visited pages, so even if I always served random results, when the user tries to go back, he will get the previous page, because it will load from the local cache.

问题是，如果我只返回随机结果，则可能会有一些重复.每个页面包含6个结果，因此，为避免这种情况，我必须做类似WHERE id NOT IN (1,2,3,4 ...)的操作，在其中放置所有以前加载的ID.

The problem is, that if I return only random results, there might be some duplicates. Each page contains 6 results, so to prevent this, I'd have to do something like WHERE id NOT IN (1,2,3,4 ...) where I'd put all the previously loaded IDs.

该解决方案的巨大缺点是，由于每个用户都将请求不同的数据，因此无法在服务器端缓存任何内容.

Huge downside of that solution is that it won't be possible to cache anything on the server side, as every user will request different data.

另一种解决方案可能是创建另一列以对记录进行排序，然后在每个插入时间单位中对其进行随机播放.这里的问题是，我需要为表中的每个记录从序列中设置随机数，这将需要与记录一样多的查询.

Alternate solution might be to create another column for ordering the records, and shuffle it every insert time unit here. The problem here is, I'd need to set random number out of a sequence to every record in the table, which would take as many queries as there are records.

如果有任何相关性，我正在使用Rails和MySQL.

I'm using Rails and MySQL if that's of any relevance.

推荐答案

尝试一下:

mysql> create table t (i int);
mysql> insert into t values (1),(2),(3),(4),(5),(6);
mysql> select * from t order by rand(123) limit 2 offset 0;
+------+
| i    |
+------+
|    6 | 
|    4 | 
+------+
mysql> select * from t order by rand(123) limit 2 offset 2;
+------+
| i    |
+------+
|    2 | 
|    3 | 
+------+
mysql> select * from t order by rand(123) limit 2 offset 4;
+------+
| i    |
+------+
|    5 | 
|    1 | 
+------+

请注意，rand()函数具有种子值(123).还要注意，如果重复最后三个查询，则每次都会得到相同的结果.

Note that the rand() function has a seed value (123). Note also that if you repeat the last three queries you'll get the same result every time.

这篇关于“洗牌"的最佳方法是什么?数据库记录表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！