MySQL RAND() function

In this context, we will learn how we can use the MySQL RAND() function with proper syntax and good examples.

Introduction of MySQL RAND() function

Basically, in MySQL the RAND() function is used to return a random floating-point value V in the range 0 <= V < 1.0. We have to use the expression, when we want to derive a random integer R in the range using the following formula:

FLOOR(i + RAND() * (j - i)).

Syntax of the MySQL RAND() function

The syntax of the MySQL RAND() function is given as follows:

RAND(X)

Parameters or arguments used in MySQL RAND() function:

Only one parameter is accepted by the RAND() function in MySQL, which is given as follows:

X: If X is specified, it will return a repeatable sequence of random numbers. If no X is specified, it will return a completely random number. It is optional, and it will work as a seed value.

Returns:

It will return a random floating number between 0 and 1.

Application used for RAND() function:

The RAND() function can be used in the given below MySQL versions.:

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

Examples of MySQL RAND() function:

Now we will look into some MySQL RAND() function examples and will explore how we can use the RAND function in MySQL.

Example-1 :

Here, we will derive the random value between 0 and 1 using RAND Function in MySQL.

SELECT RAND() AS Random_Number;

Output :

Random_Number
0.6332025068189973
1 row in set (0.00 sec)

Example-2 :

Here, we will derive the random value between 0 and 1 using RAND Function with seed value in MySQL.

SELECT RAND(), RAND(5), RAND(5);

Output:

RAND()	RAND(5)	RAND(5)
0.9580194543703452	0.40613253673014313	0.40613876543214313

So, here we can see that if we use the same seed value for generating the random number, we will get the same random number as a result.

1 row in set (0.00 sec)

Example-3 :

Now we will derive the random value between the range [ 5, 10 ) using RAND Function in MySQL. Here, we will use the expression: FLOOR(i + RAND() * (j - i)) to generate the random number. Here, i will be 5, and j will be 10.

SELECT FLOOR(5 + RAND()*(10-5)) AS Random_Number;

Output:

Random_Number
6
1 row in set (0.00 sec)

Example-4 :

Now we will obtain the random value between the range [ 5, 10 ] using RAND Function in MySQL. Here, we will use the expression: FLOOR(i + RAND() * (j - i + 1)) to generate the random number. Here i will be 5, and j will be 10.

SELECT FLOOR(5 + RAND()*(10 - 5 + 1)) AS Random_Number;

Output:

Random_Number
10
1 row in set (0.00 sec)

Example-5 :

Now, from a category table by random order, we will use the RAND Function to return rows. To demonstrate, we have created a table named pupil.

CREATE TABLE Pupil(
Pupil_id INT AUTO_INCREMENT,
Pupil_name VARCHAR(100) NOT NULL,
Pupil_Class VARCHAR(20) NOT NULL,
TotalExamGiven INT NOT NULL,
PRIMARY KEY(Pupil_id )
);

Now add some data into the Pupil table –

INSERT INTO
Pupil(Pupil_name, Pupil_Class, TotalExamGiven)
VALUES
('Sayan,' 'IX,' 8),
('Nitin,' 'X,' 5),
('Aniket,' 'XI,' 6),
('Abdur,' 'X,' 7),
('Riya,' 'IX,' 4),
('Jony,' 'X,' 10),
('Deepak,' 'X,' 7),
('Ankana', 'XII', 5),
('Shreya,' 'X,' 8);

To get all details about Pupil Table, we will use the –

SELECT *
FROM Pupil;

Output:

Pupil_id	Pupil_name	Pupil_Class	TotalExamGiven
1	Sayan	IX	8
2	Nitin	X	5
3	Aniket	XI	6
4	Abdur	X	7
5	Riya	IX	4
6	Jony	X	10
7	Deepak	X	7
8	Ankana	XII	5
9	Shreya	X	8

So, we can see that all rows in the table are given in the right order. To return rows from the Pupil table by a random order, we will use –

SELECT *
FROM Pupil
ORDER BY RAND();

Output:

Pupil_id	Pupil_name	Pupil_Class	TotalExamGiven
6	Jony	X	10
1	Sayan	IX	8
5	Riya	IX	4
2	Nitin	X	5
3	Aniket	XI	6
8	Ankana	XII	5
9	Shreya	X	8
4	Abdur	X	7
7	Deepak	X	7

Summary:

In the above context, we have learned how to use MySQL's RAND() function to return a random floating-point value.