13.7 Generating Random Numbers
13.7.1 Problem
You need a source of random
numbers.
13.7.2 Solution
Invoke MySQL's RAND( ) function.
13.7.3 Discussion
MySQL has a RAND( ) function that can be invoked to produce
random numbers between 0 and 1:
mysql> SELECT RAND( ), RAND( ), RAND( );
+------------------+------------------+------------------+
| RAND( ) | RAND( ) | RAND( ) |
+------------------+------------------+------------------+
| 0.31466114177803 | 0.89354679723601 | 0.52375059157959 |
+------------------+------------------+------------------+
When invoked with an integer argument, RAND( )
uses that value to seed the random number generator. Each time you
seed the generator with a given value, RAND( )
will produce a repeatable series of numbers:
mysql> SELECT RAND(1), RAND( ), RAND( );
+------------------+------------------+------------------+
| RAND(1) | RAND( ) | RAND( ) |
+------------------+------------------+------------------+
| 0.18109050223705 | 0.75023211143001 | 0.20788908117254 |
+------------------+------------------+------------------+
mysql> SELECT RAND(20000000), RAND( ), RAND( );
+------------------+-------------------+------------------+
| RAND(20000000) | RAND( ) | RAND( ) |
+------------------+-------------------+------------------+
| 0.24628307879556 | 0.020315642487552 | 0.36272900678472 |
+------------------+-------------------+------------------+
mysql> SELECT RAND(1), RAND( ), RAND( );
+------------------+------------------+------------------+
| RAND(1) | RAND( ) | RAND( ) |
+------------------+------------------+------------------+
| 0.18109050223705 | 0.75023211143001 | 0.20788908117254 |
+------------------+------------------+------------------+
mysql> SELECT RAND(20000000), RAND( ), RAND( );
+------------------+-------------------+------------------+
| RAND(20000000) | RAND( ) | RAND( ) |
+------------------+-------------------+------------------+
| 0.24628307879556 | 0.020315642487552 | 0.36272900678472 |
+------------------+-------------------+------------------+
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:
mysql> SELECT RAND(UNIX_TIMESTAMP( )) AS rand1,
-> RAND(CONNECTION_ID( )) AS rand2,
-> RAND(UNIX_TIMESTAMP( )+CONNECTION_ID( )) AS rand3;
+------------------+------------------+------------------+
| rand1 | rand2 | rand3 |
+------------------+------------------+------------------+
| 0.50452774158169 | 0.18113064782799 | 0.50456789089792 |
+------------------+------------------+------------------+
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( ).
| 
No comments:
Post a Comment