MySQL: RAND Function

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

Description

The MySQL 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 MySQL 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 >= 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 MySQL:

• MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23

Example of Random Number

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

For example:

mysql> SELECT RAND();
Output: 0.2430297417966926     (no seed value, so your answer will vary)

mysql> SELECT RAND(9);
Output: 0.406868412538309      (with seed value of 9)

mysql> SELECT RAND(-5);
Output: 0.9043048842850187     (with seed value of -5)

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 MySQL 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):

mysql> SELECT RAND()*(10-1)+1;
Output: 3.71321560508871      (no seed value, so your answer will vary)

mysql> SELECT RAND(9)*(10-1)+1;
Output: 4.661815712844781     (with seed value of 9)

mysql> SELECT RAND(-5)*(10-1)+1;
Output: 9.138743958565168     (with seed value of -5)

Example of Random Integer Range

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

For example, the following would generate a random integer value between 20 and 30:

mysql> SELECT FLOOR(RAND()*(30-20+1))+20;
Output: 22                    (no seed value, so your answer will vary)

mysql> SELECT FLOOR(RAND(9)*(30-20+1))+20;
Output: 24                    (with seed value of 9)

mysql> SELECT FLOOR(RAND(-5)*(30-20+1))+20;
Output: 29                    (with seed value of -5)