totn MariaDB Functions

MariaDB: RAND Function

This MariaDB tutorial explains how to use the MariaDB RAND function with syntax and examples.

Description

The MariaDB RAND function can be used to return a random number or a random number within a range.

Syntax

The syntax for the RAND function in MariaDB is:

RAND( [seed] )

Parameters or Arguments

seed
Optional. If specified, it will produce a repeatable sequence of random numbers each time that seed value is provided.

Note

  • The RAND function will return a value between 0 (inclusive) and 1 (exclusive), so value >= 0 and value < 1.
  • The RAND function will return a completely random number if no seed is provided.
  • The RAND function will return a repeatable sequence of random numbers each time a particular seed value is used.

Random Decimal Range

To create a random decimal number between two values (range), you can use the following formula:

SELECT RAND()*(b-a)+a;

Where a is the smallest number and b is the largest number that you want to generate a random number for.

SELECT RAND()*(25-10)+10;

The formula above would generate a random decimal number between 10 and 25. (Note: this formula will never return a value of 25 because the random function will never return 1.)

Random Integer Range

To create a random integer number between two values (inclusive range), you can use the following formula:

SELECT FLOOR(RAND()*(b-a+1))+a;

Where a is the smallest number and b is the largest number that you want to generate a random number for.

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

The formula above would generate a random integer number between 10 and 25, inclusive.

Applies To

The RAND function can be used in the following versions of MariaDB:

  • MariaDB 10

Example of Random Number

Let's explore how to use the RAND function in MariaDB to generate a random number >= 0 and < 1.

For example:

SELECT RAND();
Result: 0.6850685735094069     (no seed value, so your answer will vary)

SELECT RAND(8);
Result: 0.15668530311126755    (with seed value of 8)

SELECT RAND(-6);
Result: 0.6541217748579772     (with seed value of -6)

Although the RAND function will return a value of 0, it will never return a value of 1. It will always return a value smaller than 1.

Example of Random Decimal Range

Let's explore how to use the RAND function in MariaDB to generate a random decimal number between two numbers (ie: range).

For example, the following would generate a random decimal value that is >= 1 and < 10 (Note: it will never return a value of 10):

SELECT RAND()*(10-1)+1;
Result: 1.124445336986748      (no seed value, so your answer will vary)

SELECT RAND(8)*(10-1)+1;
Result: 2.410167728001408     (with seed value of 8)

SELECT RAND(-6)*(10-1)+1;
Result: 6.887095973721794     (with seed value of -6)

Example of Random Integer Range

Let's explore how to use the RAND function in MariaDB to generate a random integer number between two numbers (ie: inclusive range).

For example, the following would generate a random integer value between 50 and 75:

SELECT FLOOR(RAND()*(75-50+1))+50;
Result: 53                   (no seed value, so your answer will vary)

SELECT FLOOR(RAND(8)*(75-50+1))+50;
Result: 54                   (with seed value of 8)

SELECT FLOOR(RAND(-6)*(75-50+1))+50;
Result: 67                   (with seed value of -6)
Share on: