Recipe 13.6. Generating Random Numbers

Problem

You need a source of random numbers.

Solution

Invoke MySQL's RAND⁠(⁠ ⁠ ⁠) function.

Discussion

MySQL has a RAND⁠(⁠ ⁠ ⁠) function that produces random numbers between 0 and 1:

mysql> SELECT RAND(), RAND(), RAND(); +------------------+--------------------+------------------+ | RAND()           | RAND()             | RAND()           | +------------------+--------------------+------------------+ | 0.18768198246852 | 0.0052350517411111 | 0.46312934203365 | +------------------+--------------------+------------------+ 

When invoked with an integer argument, RAND⁠(⁠ ⁠ ⁠) uses that value to seed the random number generator. You can use this feature to produce a repeatable series of numbers for a column of a query result. The following example shows that RAND⁠(⁠ ⁠ ⁠) without an argument produces a different column of values per query, whereas RAND( N ) produces a repeatable column:

mysql> SELECT i, RAND(), RAND(10), RAND(20) FROM t; +------+------------------+------------------+------------------+ | i    | RAND()           | RAND(10)         | RAND(20)         | +------+------------------+------------------+------------------+ |    1 | 0.60170396939079 | 0.65705152196535 | 0.15888261251047 | |    2 | 0.10435410784963 | 0.12820613023658 | 0.63553050033332 | |    3 | 0.71665866180943 | 0.66987611602049 | 0.70100469486881 | |    4 | 0.27023101623192 | 0.96476222012636 | 0.59843200407776 | +------+------------------+------------------+------------------+ mysql> SELECT i, RAND(), RAND(10), RAND(20) FROM t; +------+------------------+------------------+------------------+ | i    | RAND()           | RAND(10)         | RAND(20)         | +------+------------------+------------------+------------------+ |    1 | 0.55794027034001 | 0.65705152196535 | 0.15888261251047 | |    2 | 0.22995210460383 | 0.12820613023658 | 0.63553050033332 | |    3 | 0.47593974273274 | 0.66987611602049 | 0.70100469486881 | |    4 | 0.68984243058585 | 0.96476222012636 | 0.59843200407776 | +------+------------------+------------------+------------------+ 

If you want to seed RAND⁠(⁠ ⁠ ⁠) randomly, pick a seed value based on a source of entropy. Possible sources are the current timestamp or connection identifier, alone or perhaps in combination:

RAND(UNIX_TIMESTAMP()) RAND(CONNECTION_ID()) RAND(UNIX_TIMESTAMP()+CONNECTION_ID()) 

How Random Is RAND⁠(⁠ ⁠ ⁠)?

Does the RAND⁠(⁠ ⁠ ⁠) function generate evenly distributed numbers? Check it out for yourself with the following Python script, rand_test.py, from the stats directory of the recipes distribution. It uses RAND⁠(⁠ ⁠ ⁠) to generate random numbers and constructs a frequency distribution from them, using .1-sized categories. This provides a means of assessing how evenly distributed the values are:

#!/usr/bin/python # rand_test.pl - create a frequency distribution of RAND() values. # This provides a test of the randomness of RAND(). # Method: Draw random numbers in the range from 0 to 1.0, # and count how many of them occur in .1-sized intervals import MySQLdb import Cookbook npicks = 1000   # number of times to pick a number bucket = [0] * 10 # buckets for counting picks in each interval conn = Cookbook.connect () cursor = conn.cursor () for i in range (0, npicks):   cursor.execute ("SELECT RAND()")   (val,) = cursor.fetchone ()   slot = int (val * 10)   if slot > 9:     slot = 9    # put 1.0 in last slot   bucket[slot] = bucket[slot] + 1 cursor.close () conn.close () # Print the resulting frequency distribution for slot in range (0, 9):   print "%2d  %d" % (slot+1, bucket[slot]) 

The stats directory also contains equivalent scripts in other languages.

However, it's probably better to use other seed value sources if you have them. For example, if your system has a /dev/random or /dev/urandom device, you can read the device and use it to generate a value for seeding RAND⁠(⁠ ⁠ ⁠).