MySQL RAND() Function

Summary: in this tutorial, you will learn how to use the MySQL RAND() function to get a random number.

Introduction to MySQL RAND() function

The RAND() function returns a random number v with the following value:

0 <= v < 1.0Code language: SQL (Structured Query Language) (sql)

Here’s the syntax of the RAND() function:

RAND(seed)Code language: SQL (Structured Query Language) (sql)

In this syntax, the seed argument is an integer that serves as a seed value. The seed argument is optional.

The following example uses the RAND() function to get a random number between 0 and 1:

SELECT RAND();Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------+
| RAND()             |
+--------------------+
| 0.6090137591669464 |
+--------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

If you use a constant seed value, the RAND() function will return the same value each time.

For example, executing the following statement that uses the RAND() function with the same seed value will return the same random number:

SELECT RAND(1);Code language: SQL (Structured Query Language) (sql)

In practice, you often want to obtain a random integer R where i <= R < j.

To do that, you can combine the RAND() function with the FLOOR() function as follows:

FLOOR(i + RAND() * (j − i))Code language: SQL (Structured Query Language) (sql)

For example, the following returns a random number between 1 and 10:

SELECT FLOOR(1 + RAND() * 9);Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------------+
| FLOOR(1 + RAND() * 9) |
+-----------------------+
|                     5 |
+-----------------------+
1 row in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

MySQL RAND() function with table data example

We’ll use the employees from the sample database for the demonstration:

If the number of rows in a table is small, you can use the RAND() function in the ORDER BY clause to sort the rows of a table randomly:

SELECT 
  firstName 
FROM 
  employees 
ORDER BY 
  RAND();Code language: SQL (Structured Query Language) (sql)

You can also use the LIMIT clause to get a number of random rows from the employees table:

SELECT 
  firstName 
FROM 
  employees 
ORDER BY 
  RAND()
LIMIT 5;Code language: SQL (Structured Query Language) (sql)

The query returns different rows each time you run the query.

Creating a random function that returns a random integer in a range of integers

If you often want to find a random integer between two integers, you can create a stored function like this:

DELIMITER $$

CREATE FUNCTION RAND_BETWEEN(min INT, max INT)
RETURNS INT
DETERMINISTIC
BEGIN
 RETURN FLOOR(min + (RAND() * (max-min)));
END $$

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

The RAND_BETWEEN function has two parameters min and max, and returns a random integer between them ( min <= return_integer < max).

The following shows how to call the RAND_BETWEEN() function to get a random integer between 1 and 10:

SELECT RAND_BETWEEN(1,10);Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------+
| RAND_BETWEEN(1,10) |
+--------------------+
|                  4 |
+--------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

• Use the RAND() function to get a random number between 0 and 1.