MySQL RAND()

RAND([N])

 

Returns a random floating-point value v in the range 0 <= v < 1.0.

To obtain a random integer R in the range i <= R < j, use the expression FLOOR(i +RAND() * (j - i)). For example, to obtain a random integer in the range the range 7 <= R < 12, use following statement:

 

SELECT FLOOR(7 + RAND() * 5);

 

If an integer argument N is sepcified, it is used as the seed value:

• With a constant initializer argument, the seed is initialized once when the statement is prepared, prior to execution.
• With a nonconstand initializer argument(such as a column name), the seed is initialized with the value for each invocation of RAND().

One implication of this behavior is that for equal argument values, RAND(N) returns the same value each time, and thus produces a repeatable sequence of column values. In the following example, the sequence of values produced by RAND(3) is the sam both places it occurs.

 

RAND() in a WHERE caluse is evaluated for every row(when selecting from one table) or combination of rows (when selecting form a multiple-table join). Thus, for optimizer purposes, RAND() is not constand value and cannot be used for index optimizations. 

 

 

Use of a column with RAND() values in an ORDER BY or GROUP BY clause may yield unexpeted results because for either clausea RAND() expression can be evaluated multiple times for the same row, each time returning a different result. If the goal is to retrieve rows in random order, you can use a statement like this

SELECT * FROM tbl_name ORDER BY RAND():

 

To select a random sample a set of rows, combine ORDER BY RAND() with LIMIT:

 

SELECT * FROM table1, table2 WHERE a=b and c<d ORDER BY RAND() LIMIT 1000;

 

RAND() is not meant to be a perfect random generator. It is a fast way to generate random numbers on demand that is protable between platforms for the same MySQL version.

 

This function is unsafe for statement-based replication. A warning is logged if you use this function when binlog_format is set to STATEMENT.

 

 

mysql> SELECT RAND(), RAND(555), FLOOR(7 + RAND() * 5);
+--------------------+----------------------+-----------------------+
| RAND()             | RAND(555)            | FLOOR(7 + RAND() * 5) |
+--------------------+----------------------+-----------------------+
| 0.4454458760520871 | 0.006846033043084697 |                     9 |
+--------------------+----------------------+-----------------------+
1 row in set (0.00 sec)

 

 

* random 숫자를 만들기 좋은 것 같은데.. 결과는 그리 좋지 않았던 기억이 있다. 이유는 정확히 기억이 당연히 나지 않고. 그래서 다른 방법으로 random을 구현했던 것으로 기억한다.

 

다만  order by 절에  RAND() 를 써도 된다는 건 좋은 것 같다. 가끔 필요하다.