Database Reference
In-Depth Information
Discussion
If a set of items is stored in MySQL, choose one at random as follows:
1. Select the items in the set in random order, using
ORDER
BY
RAND()
as described in
Recipe 15.7
.
2. Add
LIMIT
1
to the query to pick the first item.
For example, to perform a simple simulation of tossing a die, create a
die
table con‐
taining rows with values from 1 to 6 corresponding to the six faces of a die cube:
CREATE
TABLE
die
(
n
INT
);
Then pick rows from the table 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
