RAND([N])

MySQL 8.4

Returns a random floating-point value v in the range 0

Syntax

MYSQL

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

Examples

Example

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

MYSQL

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

Example 2

With a constant initializer argument, the seed is initialized once when the statement is prepared, prior to execution.

MYSQL

CREATE TABLE t (i INT);
Query OK, 0 rows affected (0.42 sec)

INSERT INTO t VALUES(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

SELECT i, RAND() FROM t;
+------+------------------+
| i    | RAND()           |
+------+------------------+
|    1 | 0.61914388706828 |
|    2 | 0.93845168309142 |
|    3 | 0.83482678498591 |
+------+------------------+
3 rows in set (0.00 sec)

SELECT i, RAND(3) FROM t;
+------+------------------+
| i    | RAND(3)          |
+------+------------------+
|    1 | 0.90576975597606 |
|    2 | 0.37307905813035 |
|    3 | 0.14808605345719 |
+------+------------------+
3 rows in set (0.00 sec)

SELECT i, RAND() FROM t;
+------+------------------+
| i    | RAND()           |
+------+------------------+
|    1 | 0.35877890638893 |
|    2 | 0.28941420772058 |
|    3 | 0.37073435016976 |
+------+------------------+
3 rows in set (0.00 sec)

SELECT i, RAND(3) FROM t;
+------+------------------+
| i    | RAND(3)          |
+------+------------------+
|    1 | 0.90576975597606 |
|    2 | 0.37307905813035 |
|    3 | 0.14808605345719 |
+------+------------------+
3 rows in set (0.01 sec)

Example 3

With a nonconstant initializer argument (such as a column name), the seed is initialized with the value for each invocation of RAND().

MYSQL

SELECT * FROM tbl_name ORDER BY RAND();

Example 4

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 same both places it occurs.

MYSQL

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