Statistical Techniques - MySQL - page 525

15.6. Generating Random Numbers
Problem
You need a source of random numbers.
Solution
Use the 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.37415416573561183 | 0.9068914557871329 | 0.41199481246247405 |
+---------------------+--------------------+---------------------+
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.00708185882035816 | 0.6570515219653505 | 0.15888261251047497 |
| 2 | 0.5417692908474889 | 0.12820613023657923 | 0.6355305003333189 |
| 3 | 0.6876009085100152 | 0.6698761160204896 | 0.7010046948688149 |
| 4 | 0.8126967007412544 | 0.9647622201263553 | 0.5984320040777623 |
+------+---------------------+---------------------+---------------------+
mysql> SELECT i, RAND(), RAND(10), RAND(20) FROM t;
+------+----------------------+---------------------+---------------------+
| i | RAND() | RAND(10) | RAND(20) |
+------+----------------------+---------------------+---------------------+
| 1 | 0.059957268703689115 | 0.6570515219653505 | 0.15888261251047497 |
| 2 | 0.9068000166740269 | 0.12820613023657923 | 0.6355305003333189 |
| 3 | 0.35412830799271194 | 0.6698761160204896 | 0.7010046948688149 |
| 4 | 0.050241520675124156 | 0.9647622201263553 | 0.5984320040777623 |
+------+----------------------+---------------------+---------------------+
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 com‐
bination: