MySQL RAND() Function – Easily Generate Random Numbers in MySQL

By /
RAND Function

In this tutorial, we will learn about the MySQL RAND() function. While working with MySQL, you will come across situations in which you would need a random number for certain operations. For this purpose, MySQL provides us with the RAND() function.

The MySQL RAND() function is used to return a random floating-point number between 0 (inclusive) and 1 (exclusive). We can also pass an argument to the function, known as the seed value to produce a repeatable sequence of random numbers. We will see more about RAND() and seed values later but first, let us take a look at the syntax.

Syntax of MySQL RAND()

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

Where ‘seed’ is an optional parameter that if specified, can return a repeatable sequence of random numbers. If ‘seed’ is not mentioned, then a different random number is returned for every execution.

Examples of MySQL RAND()

Let us look at a basic example of RAND(). Using the SELECT statement and aliases, let us display two random numbers between 0 to 1.

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

And the output is,

Rand Basic Example

RAND() Using a Seed Value

Now as you saw in the previous example, we executed RAND() twice and we got a different random number both times. What if you want the same random number again? This is where the ‘seed’ parameter comes into play. Adding a ‘seed’ value makes RAND() repeat the random number again if the succeeding RAND() function is passed the same ‘seed’ value. Let us see this using the below example.

We want 4 random numbers. However, the first random number must be equal to the last random number. We do this using the below query.

SELECT RAND(2), RAND(10), RAND(-15), RAND(2);Code language: SQL (Structured Query Language) (sql)

So we write 4 RAND() functions, each one of them with a ‘seed’ value such that the ‘seed’ value of the first RAND() function matches the ‘seed’ value of the last RAND() function. And we get the output as,

Rand Seed Example

As you can see, the first and the last random number are equal because their ‘seed’ value is the same.

Expressions with MySQL RAND()

We can also use RAND() as a part of an expression. Let us see this using the below examples.

SELECT RAND()*10 AS RandomNumber; 
SELECT RAND()*100 AS RandomNumber;Code language: SQL (Structured Query Language) (sql)

And the output is,

MySQL Rand Expression Example

MySQL RAND() To Find Random Numbers Between Any Range

RAND() returns random numbers from 0 to 1. However, we can use the FLOOR() function and some mathematical expressions to find random numbers between a given range. Let us find random numbers between 5 and 10 (inclusive). We use the below query.

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

And the output is,

MySQL Rand Range Example 1

Let us now find two numbers between 10 and 25 (exclusive). We do this using the below query.

SELECT FLOOR(RAND()*(25-10)+10) AS Number_1, FLOOR(RAND()*(25-10)+10) AS Number_2;Code language: SQL (Structured Query Language) (sql)

And the output is,

Rand Range Example 4

MySQL RAND() With Tables

Consider the below ‘Students’ table.

Rand Students Table
Students Table

Let us display the ID, first name, last name and the number of assignments written by a student as a random number till 10. We use the below query.

SELECT ID, FirstName, LastName, FLOOR(RAND()*10) AS AssignmentsWritten FROM Students;Code language: SQL (Structured Query Language) (sql)

And the output is,

Rand Table Example 1

RAND() With UPDATE

Let us now create a column named AssignmentsWritten in the above Students table using ALTER and UPDATE statements and fill that column with random integer values up to 10. The queries are-

ALTER TABLE Students ADD AssignmentsWritten int; 
UPDATE Students SET AssignmentsWritten=FLOOR(RAND()*10); 
SELECT * FROM Students;Code language: SQL (Structured Query Language) (sql)

And we get the output as,

MySQL Rand Table Example 3

Displaying Random Rows

We can also use RAND() to display random rows in a table if we pair it with the ORDER BY clause. Let us also use the LIMIT clause to display two random rows (only ID, FirstName and LastName columns0 from the Students table.

SELECT ID, FirstName, LastName FROM Students ORDER BY RAND() LIMIT 2;Code language: SQL (Structured Query Language) (sql)

And we get the output as,

Rand Table Example 2

Conclusion

MySQL RAND() is used to generate random numbers and can also be used to return random rows from a table. I would highly recommend you to check out the below references.

References