Consistent random ordering in a MySQL query

问题:

I have a database of pictures and I want to let visitors browse the pictures. I have one "next" and one "previous" link.

But what I want is to show every visitor anther order of the pictures. How can I do that? If I will use ORDER BY RANDOM() I will show sometimes duplicate images.

Can someone help me please? Thank you!

回答1:

You can try to use seed in random function:

SELECT something FROM somewhere ORDER BY rand(123)

123 is a seed. Random should return the same values.

回答2:

The problem arises from the fact that each page will run RAND() again and has no way of knowing if the returned pictures have already been returned before. You would have to compose your query in such a way that you can filter out the pictures already presented on the previous pages, so that RAND() will have fewer options to choose from.

An idea would be to randomize the pictures, select the IDs, store the IDs in the session, then SELECT using those IDs. This way, each user will have the pictures randomized, but they will be able to paginate through them without re-randomizing them on each page.

So, something like:

1. SELECT id FROM pictures ORDER BY RAND() LIMIT x if you don't have the IDs in the session already
2. Store the IDs in the session
3. SELECT ... FROM pictures WHERE id IN (IDs from session) LIMIT x

Another idea is to store in session the IDs that the user already saw and filter them out. For example:

1. SELECT ... FROM pictures ORDER BY RAND() LIMIT x if the session doesn't contain any ID
2. Append the IDs from the current query to the session
3. SELECT ... FROM pictures WHERE id NOT IN (IDs from session) ORDER BY RAND() LIMIT x

Another way seems to be to use a seed, as izi points out. I have to say I didn't know about the seed, but it seems to return the exact same results for the exact same value of the seed. So, run your usual query and use RAND(seed) instead of RAND(), where "seed" is a unique string or number. You can use the session ID as a seed, because it's guaranteed to be unique for each visitor.

回答3:

You can seed the random function as suggested by izi, or keep track of visited images vs non-visited images as suggested by rdineiu.

I'd like to stress that neither option will perform well, however. Either will lead you to sorting your entire table (or the part of it of interest) using an arbitrary criteria and extracting the top n rows, possibly with an offset. It'll be dreadfully slow.

Thus, consider for a moment how important it is that every visitor should get a different image order. Probably, it'll be not that important, as long as things look random. Assuming this is the case, consider this alternative...

Add an extra float field to your table, call it sort_ord. Add an index on it. On every insert or update, assign it a random value. The point here is to end up with a seemingly random order (from the visitor's standpoint) without compromising performance.

Such a setup will allow you to grab the top n rows and paginate your images using an index, rather than by sorting your entire table.

At your option, have a cron job periodically set a new value:

update yourtable
set sort_ord = rand();

Also at your option, create several such fields and assign one to visitors when they visit your site (cookie or session).

回答4:

This will solve:

SELECT DISTINCT RAND() as rnd, [rest of your query] ORDER BY rnd;

回答5:

Use RAND(SEED). From the docs: "If a constant integer argument N is specified, it is used as the seed value." (http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand).

In the example above the result order is always the same. You simply change the seed (351) and you get a new random order.

SELECT * FROM your_table ORDER BY RAND(351);

You can to change the seed every time the user hits the first page.

回答6:

Without seeing the SQL I'd guess you could try SELECT DISTINCT...