MySQL Cookbook, 2nd Edition

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; +------+------------------+------------------+------------------+ ...