RAND

Syntax

RAND([N])

Purpose

RAND([N]) accepts zero or one argument (N is called the random number seed) and returns a random floating-point number within the range of [0, 1.0). To obtain a random integer in the range of [i, j), use the FLOOR(I + RAND() * (j - i)) expression.

If the argument N is not specified, random number seed initialization is performed before the function is executed. Then, RAND() generates a random number based on this initial value. RAND() generates a different random number sequence each time.

If the argument N is specified, N is used as a seed to generate random numbers. Random values are generated based on whether N is a constant:

• If N is a constant, N it is initialized as a random number seed before execution. Then, RAND(N) generates a random number based on this initial value. The same N value will generate the same random number sequence.

• If N is a variable (for example, a column value), N is used as a random number seed to generate random numbers in each execution. The same N value will generate the same random number.

In addition to the SECLET statement, RAND([N]) is also used in the WHERE, ORDER BY, and GROUP BY clauses, and is executed based on the preceding rules. For example, if you want to sort rows of a table by a random number, you can execute the SELECT FROM T1 ORDER BY RAND() statement. If you want to randomly sample 100 rows from a table, you can execute the SELECT FROM T1 ORDER BY RAND() LIMIT 100 statement.

Examples

obclient> SELECT A, B, RAND() FROM T3;
+------+------+---------------------+
| A    | B    | RAND()              |
+------+------+---------------------+
|    1 |    1 |   0.641815407799385 |
|    2 |    2 | 0.16825051248841966 |
|    3 |    3 |  0.9158063697775886 |
+------+------+---------------------+
3 rows in set