Randomize the values, and then pick the first one (or the first few, if you need more than one).
When a set of items is stored in MySQL, you can choose one at random as follows:
Select the items in the set in random order, using
ORDER
BY
RAND()
as described in Randomizing a Set of Rows.
Add LIMIT
1
to the query to pick the first
item.
For example, a simple simulation of tossing a die can be
performed by creating a die
table
containing rows with values from 1 to 6 corresponding to the six faces
of a die cube, and then picking rows from it at random:
mysql>SELECT n FROM die ORDER BY RAND() LIMIT 1;
+------+ | n | +------+ | 6 | +------+ mysql>SELECT n FROM die ORDER BY RAND() LIMIT 1;
+------+ | n | +------+ | 4 | +------+ mysql>SELECT n FROM die ORDER BY RAND() LIMIT 1;
+------+ | n | +------+ | 5 | +------+ mysql>SELECT n FROM die ORDER BY RAND() LIMIT 1;
+------+ | n | +------+ | 4 | +------+
As you repeat this operation, you pick a random sequence of items from the set. This is a form of selection with replacement: an item is chosen from a pool of items and then returned to the pool for the next pick. Because items are replaced, it’s possible to pick the same item multiple times when making successive choices this way. Other examples of selection with replacement include:
Selecting a banner ad to display on a web page
Picking a row for a “quote of the day” application
“Pick a card, any card” magic tricks that begin with a full deck of cards each time
If you want to pick more than one item, change the LIMIT
argument. For example, to draw five
winning entries at random from a table named drawing
that contains contest entries,
use
RAND()
in
combination with LIMIT
:
SELECT * FROM drawing ORDER BY RAND() LIMIT 5;
A special case occurs when you’re picking a single row from a
table that you know contains a column with values in the range from 1
to n
in unbroken sequence. Under these
circumstances, it’s possible to avoid performing an ORDER
BY
operation on the entire table by picking a random number in that range
and selecting the matching row:
SET @id = FLOOR(RAND()*n
)+1; SELECT ... FROMtbl_name
WHERE id = @id;
This will be much quicker than ORDER
BY
RAND()
LIMIT
1
as the table size increases.
18.219.182.76