How to generate random numbers in MariaDB

This article discusses how to use the RAND() function to generate random numbers in MariaDB.

Posted on

MariaDB provides the RAND() function to generate random numbers between 0 (inclusive) and 1 (exclusive).

Usually, we need to generate random numbers or random integers between two specified numbers. We can do this with formulas.

Formula

If you want to return a random number between m and n, please use the following formula:

  • Random numbers between m and n: RAND() * (n - m) + m
  • Random integers between m and n: FLOOR(RAND() * (n - m + 1) + m)

In the two formulas, we use another mathematical function FLOOR(), this function returns the largest integer value less than or equal to the specified number.

There are a few examples to generate random numbers using the RAND() function.

Example 1: Generate a random number between 0 and 1

To generate a random number between 0 and 1, use RAND() directly, as follows:

SELECT
  RAND() rand1,
  RAND() rand2,
  RAND() rand3,
  RAND() rand4;

Output:

+--------------------+---------------------+--------------------+--------------------+
| rand1              | rand2               | rand3              | rand4              |
+--------------------+---------------------+--------------------+--------------------+
| 0.6890435821274702 | 0.16652111910890893 | 0.7654748798957792 | 0.3278110728858142 |
+--------------------+---------------------+--------------------+--------------------+
1 row in set (0.000 sec)

Example 2: Generate a random number between 10 and 20

The following statement generates 4 random numbers between 10 and 20:

SELECT
  RAND() * (20 - 10) + 10 rand1,
  RAND() * (20 - 10) + 10 rand2,
  RAND() * (20 - 10) + 10 rand3,
  RAND() * (20 - 10) + 10 rand4;

Output:

+--------------------+--------------------+--------------------+--------------------+
| rand1              | rand2              | rand3              | rand4              |
+--------------------+--------------------+--------------------+--------------------+
| 13.426307554753784 | 17.297205894530943 | 16.207107115729812 | 19.143918202392683 |
+--------------------+--------------------+--------------------+--------------------+
1 row in set (0.002 sec)

Example 3: Generate a random integer between 10 and 20

The following statement generates 4 random integers between 10 and 20:

SELECT
  FLOOR(RAND() * (20 - 10 + 1) + 10) rand1,
  FLOOR(RAND() * (20 - 10 + 1) + 10) rand2,
  FLOOR(RAND() * (20 - 10 + 1) + 10) rand3,
  FLOOR(RAND() * (20 - 10 + 1) + 10) rand4;

Output:

+-------+-------+-------+-------+
| rand1 | rand2 | rand3 | rand4 |
+-------+-------+-------+-------+
|    17 |    18 |    19 |    10 |
+-------+-------+-------+-------+
1 row in set (0.001 sec)

Conclusion

This article discusses how to use the RAND() function to generate random numbers in MariaDB.