In MariaDB, RAND() is a built-in function that returns a random DOUBLE precision floating point value v in the range 0 <= v < 1.0.
Syntax
RAND() can be used in the following two ways:
RAND()
RAND(N)Where N is a constant integer to be used as a seed value.
When a seed value is used, RAND() produces a repeatable sequence of column values.
Example
Here’s an example to demonstrate RAND() without specifying a seed value:
SELECT RAND();Result:
+---------------------+ | RAND() | +---------------------+ | 0.14470310708945908 | +---------------------+
Example – With a Seed
Here’s an example to demonstrate RAND() with a seed value:
SELECT RAND(3);Result:
+--------------------+ | RAND(3) | +--------------------+ | 0.9057697559760601 | +--------------------+
We can’t actually tell the difference when using the above example. To see the difference, we need to make multiple function calls using the same seed.
Like this:
SELECT
RAND(3),
RAND(3),
RAND(3);Result:
+--------------------+--------------------+--------------------+ | RAND(3) | RAND(3) | RAND(3) | +--------------------+--------------------+--------------------+ | 0.9057697559760601 | 0.9057697559760601 | 0.9057697559760601 | +--------------------+--------------------+--------------------+
We can see that all three calls resulted in the same value.
Here’s what happens when we omit the seed value:
SELECT
RAND(),
RAND(),
RAND();Result:
+--------------------+---------------------+---------------------+ | RAND() | RAND() | RAND() | +--------------------+---------------------+---------------------+ | 0.7037061310407763 | 0.08442136466914915 | 0.31098846095706195 | +--------------------+---------------------+---------------------+
Each call returns a different value.
Random Integer within a Range
We can combine RAND() with FLOOR(), along with a few calculations, to return a random integer within a range.
The syntax for doing this goes like this:
FLOOR(min_value + RAND() * (max_value - min_value +1))So, we could do the following to return a random integer between 1 and 10:
SELECT FLOOR(1 + RAND() * (10 - 1 +1));Result:
+---------------------------------+ | FLOOR(1 + RAND() * (10 - 1 +1)) | +---------------------------------+ | 6 | +---------------------------------+
Let’s call it some more to see the random effect:
SELECT
FLOOR(1 + RAND() * (10 - 1 +1)) AS r1,
FLOOR(1 + RAND() * (10 - 1 +1)) AS r2,
FLOOR(1 + RAND() * (10 - 1 +1)) AS r3,
FLOOR(1 + RAND() * (10 - 1 +1)) AS r4,
FLOOR(1 + RAND() * (10 - 1 +1)) AS r5,
FLOOR(1 + RAND() * (10 - 1 +1)) AS r6,
FLOOR(1 + RAND() * (10 - 1 +1)) AS r7,
FLOOR(1 + RAND() * (10 - 1 +1)) AS r8;Result:
+----+----+----+----+----+----+----+----+ | r1 | r2 | r3 | r4 | r5 | r6 | r7 | r8 | +----+----+----+----+----+----+----+----+ | 3 | 6 | 10 | 4 | 6 | 10 | 1 | 6 | +----+----+----+----+----+----+----+----+
Return Random Rows from a Table
RAND() can be used in conjunction with an ORDER BY clause and the LIMIT keyword to return random rows from a database table:
SELECT
PetId,
PetName
FROM Pets
ORDER BY RAND()
LIMIT 5;Example result:
+-------+---------+ | PetId | PetName | +-------+---------+ | 5 | Tweet | | 7 | Bark | | 1 | Fluffy | | 8 | Meow | | 3 | Scratch | +-------+---------+
And if I run it again, I get this:
+-------+---------+ | PetId | PetName | +-------+---------+ | 3 | Scratch | | 8 | Meow | | 4 | Wag | | 7 | Bark | | 6 | Fluffy | +-------+---------+
And so on…
Although, bear in mind that this is quite intensive, and should not be used on larger tables. See Data Sampling: Techniques for Efficiently Finding a Random Row on the MariaDB website for techniques more suited to larger tables.
Non-Numeric Seed
Here’s an example of what happens when we provide a non-numeric seed value:
SELECT RAND('five');Result:
+---------------------+
| RAND('five') |
+---------------------+
| 0.15522042769493574 |
+---------------------+
1 row in set, 1 warning (0.000 sec)
Let’s see the warning:
SHOW WARNINGS;Result:
+---------+------+-------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------+ | Warning | 1292 | Truncated incorrect INTEGER value: 'five' | +---------+------+-------------------------------------------+
Also, this tends to return the same result, regardless of which non-numeric seed is used. For example, if I throw different non-numeric seeds at it, I get the same result:
SELECT
RAND('one'),
RAND('two'),
RAND('three');Result:
+---------------------+---------------------+---------------------+
| RAND('one') | RAND('two') | RAND('three') |
+---------------------+---------------------+---------------------+
| 0.15522042769493574 | 0.15522042769493574 | 0.15522042769493574 |
+---------------------+---------------------+---------------------+
1 row in set, 3 warnings (0.000 sec)
Check the warnings:
SHOW WARNINGS;Result:
+---------+------+--------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------+ | Warning | 1292 | Truncated incorrect INTEGER value: 'one' | | Warning | 1292 | Truncated incorrect INTEGER value: 'two' | | Warning | 1292 | Truncated incorrect INTEGER value: 'three' | +---------+------+--------------------------------------------+
Null Arguments
RAND() returns the same value when the seed is null:
SELECT
RAND(null),
RAND(null),
RAND(null);Result:
+---------------------+---------------------+---------------------+ | RAND(null) | RAND(null) | RAND(null) | +---------------------+---------------------+---------------------+ | 0.15522042769493574 | 0.15522042769493574 | 0.15522042769493574 | +---------------------+---------------------+---------------------+
Although, in this case, there are no warnings.
Too Many Arguments
Calling RAND() with too many arguments results in an error:
SELECT RAND(1, 2);Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'RAND'