PHP MySQL分页,随机排序 [英] PHP MySQL pagination with random ordering
问题描述
这是在我的网站上订购搜索结果时出现的问题
This is a problem with a ordering search results on my website,
进行搜索时,内容页面上会显示随机结果,该页面也包含分页.我将以下用户用作我的SQL查询.
When a search is made, random results appear on the content page, this page includes pagination too. I user following as my SQL query.
SELECT * FROM table ORDER BY RAND() LIMIT 0,10;
所以我的问题是
-
我需要确保每次用户访问下一页时,他们已经看到的结果都不会再次出现(以内存高效的方式将它们排除在下一个查询中,但仍按rand()排序)
I need to make sure that everytime user visits the next page, results they already seen not to appear again (exclude them in the next query, in a memory efficient way but still order by rand() )
每次访问者访问第一页时,都会得到不同的结果集,是否可以对此使用分页,或者排序始终是随机的.
everytime the visitor goes to the 1st page there is a different sets of results, Is it possible to use pagination with this, or will the ordering always be random.
我可以在MYSQL中使用种子,但是我不确定如何实际使用它..
I can use seed in the MYSQL, however i am not sure how to use that practically ..
推荐答案
在MySQL中,随机排序是一个棘手的问题.过去,我通常选择尽可能解决该问题.通常,用户返回到这样的一组页面的次数不会超过一次或两次.因此,这使您有机会避免使用各种简单的但不是完全100%的随机解决方案来避免所有令人讨厌的随机顺序实现.
Random ordering in MySQL is as sticky a problem as they come. In the past, I've usually chosen to go around the problem whenever possible. Typically, a user won't ever come back to a set of pages like this more than once or twice. So this gives you the opportunity to avoid all of the various disgusting implementations of random order in favor of a couple simple, but not quite 100% random solutions.
从许多已经建立索引以进行排序的现有列中进行选择.这可以包括创建时间",修改的时间戳记"或您可以作为排序依据的任何其他列.当用户第一次来到该站点时,将它们放在一个数组中,随机选择一个,然后随机选择ASC
或DESC
.
Pick from a number of existing columns that already indexed for being sorted on. This can include created on, modified timestamps, or any other column you may sort by. When a user first comes to the site, have these handy in an array, pick one at random, and then randomly pick ASC
or DESC
.
在您的情况下,每次用户返回第1页时,请选择新内容并将其存储在会话中.在随后的每个页面中,您都可以使用这种排序方式来生成一致的页面集.
In your case, every time a user comes back to page 1, pick something new, store it in session. Every subsequent page, you can use that sort to generate a consistent set of paging.
您可以有一个附加列,用于存储随机数以进行排序.显然,应该对其进行索引.定期运行以下查询;
You could have an additional column that stores a random number for sorting. It should be indexed, obviously. Periodically, run the following query;
更新表SET rand_col = RAND();
UPDATE table SET rand_col = RAND();
这可能不适用于您的规范,因为您似乎要求每个用户每次访问第1页时都必须看到不同的内容.
This may not work for your specs, as you seem to require every user to see something different every time they hit page 1.
这篇关于PHP MySQL分页,随机排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!