MariaDB includes a RAND() function that returns a random number. More precisely, it returns a DOUBLE precision floating point value v in the range 0 <= v < 1.0.

This is great, but what if you want to generate an integer within a larger range – and a range that you specify?

Fortunately, there’s an easy technique for doing this.

The Technique

The key doing returning a random integer within a specific range is in the following syntax:

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

This uses the FLOOR() function to return the largest integer value not greater than its argument. Seeing as our RAND() function is part of the argument, all we need to do is apply a few more calculations in order to specify our required minimum and maximum random values.

Generate a Random Integer Between 1 and 10

So, using the above syntax, here’s an example of generating a random integer between 1 and 10:

SELECT FLOOR(1 + RAND() * (10 - 1 +1));

Result:

+---------------------------------+
| FLOOR(1 + RAND() * (10 - 1 +1)) |
+---------------------------------+
|                               7 |
+---------------------------------+

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 |
+----+----+----+----+----+----+----+----+

Generate a Random Integer Between 1 and 100

In order to generate a random number between 1 and 100, all we need to do is replace 10 with 100:

SELECT FLOOR(1 + RAND() * (100 - 1 +1));

Result:

+----------------------------------+
| FLOOR(1 + RAND() * (100 - 1 +1)) |
+----------------------------------+
|                               87 |
+----------------------------------+

Let’s call it some more to see the random effect:

SELECT 
    FLOOR(1 + RAND() * (100 - 1 +1)) AS r1,
    FLOOR(1 + RAND() * (100 - 1 +1)) AS r2,
    FLOOR(1 + RAND() * (100 - 1 +1)) AS r3,
    FLOOR(1 + RAND() * (100 - 1 +1)) AS r4,
    FLOOR(1 + RAND() * (100 - 1 +1)) AS r5,
    FLOOR(1 + RAND() * (100 - 1 +1)) AS r6,
    FLOOR(1 + RAND() * (100 - 1 +1)) AS r7,
    FLOOR(1 + RAND() * (100 - 1 +1)) AS r8;

Result:

+----+----+----+----+----+----+----+----+
| r1 | r2 | r3 | r4 | r5 | r6 | r7 | r8 |
+----+----+----+----+----+----+----+----+
| 61 | 45 | 41 | 68 | 19 | 92 | 99 | 18 |
+----+----+----+----+----+----+----+----+

Generate a Random Integer Between 10000 and 50000

Let’s do one more, this time generating a random number between 10000 and 50000:

SELECT FLOOR(10000 + RAND() * (50000 - 10000 +1));

Result:

+--------------------------------------------+
| FLOOR(10000 + RAND() * (50000 - 10000 +1)) |
+--------------------------------------------+
|                                      46884 |
+--------------------------------------------+

And some more calls to see the random effect:

SELECT 
    FLOOR(10000 + RAND() * (50000 - 10000 +1)) AS r1,
    FLOOR(10000 + RAND() * (50000 - 10000 +1)) AS r2,
    FLOOR(10000 + RAND() * (50000 - 10000 +1)) AS r3,
    FLOOR(10000 + RAND() * (50000 - 10000 +1)) AS r4,
    FLOOR(10000 + RAND() * (50000 - 10000 +1)) AS r5,
    FLOOR(10000 + RAND() * (50000 - 10000 +1)) AS r6,
    FLOOR(10000 + RAND() * (50000 - 10000 +1)) AS r7,
    FLOOR(10000 + RAND() * (50000 - 10000 +1)) AS r8;

Result:

+-------+-------+-------+-------+-------+-------+-------+-------+
| r1    | r2    | r3    | r4    | r5    | r6    | r7    | r8    |
+-------+-------+-------+-------+-------+-------+-------+-------+
| 13688 | 37790 | 17884 | 46052 | 46608 | 44880 | 34578 | 28249 |
+-------+-------+-------+-------+-------+-------+-------+-------+