RAND

Syntax

RAND(), RAND(N)

Description

Returns a random DOUBLE precision floating point value v in the range 0 <= v < 1.0. If a constant integer argument N is specified, it is used as the seed value, which produces a repeatable sequence of column values. In the example below, note that the sequences of values produced by RAND(3) is the same both places where it occurs.

In a WHERE clause, RAND() is evaluated each time the WHERE is executed.

Statements using the RAND() function are not safe for statement-based replication.

Practical uses

The expression to get a random integer from a given range is the following:

FLOOR(min_value + RAND() * (max_value - min_value +1))

RAND() is often used to read random rows from a table, as follows:

SELECT * FROM my_table ORDER BY RAND() LIMIT 10;

Note, however, that this technique should never be used on a large table as it will be extremely slow. MariaDB will read all rows in the table, generate a random value for each of them, order them, and finally will apply the LIMIT clause.

Examples

CREATE TABLE t (i INT);

INSERT INTO t VALUES(1),(2),(3);

SELECT i, RAND() FROM t;
+------+-------------------+
| i    | RAND()            |
+------+-------------------+
|    1 | 0.255651095188829 |
|    2 | 0.833920199269355 |
|    3 |  0.40264774151393 |
+------+-------------------+

SELECT i, RAND(3) FROM t;
+------+-------------------+
| i    | RAND(3)           |
+------+-------------------+
|    1 |  0.90576975597606 |
|    2 | 0.373079058130345 |
|    3 | 0.148086053457191 |
+------+-------------------+

SELECT i, RAND() FROM t;
+------+-------------------+
| i    | RAND()            |
+------+-------------------+
|    1 | 0.511478140495232 |
|    2 | 0.349447508668012 |
|    3 | 0.212803152588013 |
+------+-------------------+

Using the same seed, the same sequence will be returned:

SELECT i, RAND(3) FROM t;
+------+-------------------+
| i    | RAND(3)           |
+------+-------------------+
|    1 |  0.90576975597606 |
|    2 | 0.373079058130345 |
|    3 | 0.148086053457191 |
+------+-------------------+

Generating a random number from 5 to 15:

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

See Also

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

© 2021 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/rand/