MySQL’s
RAND()
function can be used to randomize the order in which a query returns
its rows. Somewhat paradoxically, this randomization is achieved by
adding an ORDER
BY
clause to the query. The technique is
roughly equivalent to a spreadsheet randomization method. Suppose that
you have a set of values in a spreadsheet that looks like this:
Patrick Penelope Pertinax Polly
To place these in random order, first add another column that contains randomly chosen numbers:
Patrick .73 Penelope .37 Pertinax .16 Polly .48
Then sort the rows according to the values of the random numbers:
Pertinax .16 Penelope .37 Polly .48 Patrick .73
At this point, the original values have been placed in random order, because the effect of sorting the random numbers is to randomize the values associated with them. To re-randomize the values, choose another set of random numbers, and sort the rows again.
In MySQL, you can achieve a similar effect by associating a set
of random numbers with a query result and sorting the result by those
numbers. To do this, add an ORDER
BY
RAND()
clause:
mysql>SELECT name FROM t ORDER BY RAND();
+----------+ | name | +----------+ | Pertinax | | Penelope | | Patrick | | Polly | +----------+ mysql>SELECT name FROM t ORDER BY RAND();
+----------+ | name | +----------+ | Patrick | | Pertinax | | Penelope | | Polly | +----------+
Applications for randomizing a set of rows include any scenario that uses selection without replacement (choosing each item from a set of items until there are no more items left). Some examples of this are:
Determining the starting order for participants in an event. List the participants in a table, and select them in random order.
Assigning starting lanes or gates to participants in a race. List the lanes in a table, and select a random lane order.
Choosing the order in which to present a set of quiz questions.
Shuffling a deck of cards. Represent each card by a row in a table, and shuffle the deck by selecting the rows in random order. Deal them one by one until the deck is exhausted.
To use the last example as an illustration, let’s implement a
card deck-shuffling algorithm. Shuffling and dealing cards is
randomization plus selection without replacement: each card is dealt
once before any is dealt twice; when the deck is used up, it is
reshuffled to rerandomize it for a new dealing order. Within a
program, this task can be performed with MySQL using a table deck
that has 52 rows, assuming a set of
cards with each combination of 13 face values and 4 suits:
Select the entire table, and store it into an array.
Each time a card is needed, take the next element from the array.
When the array is exhausted, all the cards have been dealt. “Reshuffle” the table to generate a new card order.
Setting up the deck
table is
a tedious task if you insert the 52 card records by writing all the
INSERT
statements manually. The
deck
contents can be generated more
easily in combinatorial fashion within a program by generating each
pairing of face value with suit. Here’s some PHP code that creates a
deck
table with face
and suit
columns, and then populates the table
using nested loops to generate the pairings for the INSERT
statements:
$result =& $conn->query (" CREATE TABLE deck ( face ENUM('A', 'K', 'Q', 'J', '10', '9', '8', '7', '6', '5', '4', '3', '2') NOT NULL, suit ENUM('hearts', 'diamonds', 'clubs', 'spades') NOT NULL )"); if (PEAR::isError ($result)) die ("Cannot issue CREATE TABLE statement "); $face_array = array ("A", "K", "Q", "J", "10", "9", "8", "7", "6", "5", "4", "3", "2"); $suit_array = array ("hearts", "diamonds", "clubs", "spades"); # insert a "card" into the deck for each combination of suit and face $stmt =& $conn->prepare ("INSERT INTO deck (face,suit) VALUES(?,?)"); if (PEAR::isError ($stmt)) die ("Cannot insert card into deck "); foreach ($face_array as $index => $face) { foreach ($suit_array as $index2 => $suit) { $result =& $conn->execute ($stmt, array ($face, $suit)); if (PEAR::isError ($result)) die ("Cannot insert card into deck "); } }
Shuffling the cards is a matter of issuing this statement:
SELECT face, suit FROM deck ORDER BY RAND();
To do that and store the results in an array within a script,
write a shuffle_deck()
function that issues the query and returns the resulting values in an
array (again shown in PHP):
function shuffle_deck ($conn) { $result =& $conn->query ("SELECT face, suit FROM deck ORDER BY RAND()"); if (PEAR::isError ($result)) die ("Cannot retrieve cards from deck "); $card = array (); while ($obj =& $result->fetchRow (DB_FETCHMODE_OBJECT)) $card[] = $obj; # add card record to end of $card array $result->free (); return ($card); }
Deal the cards by keeping a counter that ranges from 0 to 51 to indicate which card to select. When the counter reaches 52, the deck is exhausted and should be shuffled again.
3.144.172.223