MySQL - RAND() Function

The MySQL RAND() function of MySQL usually returns a random floating-point value with in the range 0 to 1.0. But, to obtain a random integer R in the range i to j, use the following expression:

FLOOR(i + RAND() * (j − i))

For example, to obtain a random integer in the range the range 7 to 12, use the following statement: 

SELECT FLOOR(7 + (RAND() * 5));

The RAND() function also accepts an optional argument [N], used as a seed value.

Syntax

Following is the syntax of MySQL RAND() function −

RAND([N])

Parameters

This function takes an optional parameter. It can be an integer or an expression that is used as the "seed" value for the random number generator. If you use the same seed, you will get the same sequence of random numbers.

  • With a constant initializer argument, the seed is initialized once when the statement is prepared, prior to execution.

  • With a nonconstant initializer argument (such as a column name), the seed is initialized with the value for each invocation of RAND().

Return Value

This function returns a random floating-point value between 0 and 1.

Example

In the following example, we are using the MySQL RAND() function to generate a random floating-point number between 0 and 1 −

SELECT RAND() As Result;

Output

The output for the query above is produced as given below −

Result
0.6566204965641913

Example

We can also pass expressions as parameters to this function as shown below −

SELECT RAND(225+(RAND()*9)) As Result;

Output

This will produce the following result −

Result
0.6966031060522451

Example

Following is another example of this function −

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

Output

This will produce the following result −

RAND( ) RAND( ) RAND( )
0.7602040020378343 0.38593764545948955 0.6490762519175897

Example

To obtain a random integer in a range 10 to 20, use the following query:

SELECT FLOOR(10 + (RAND() * 10)) As Result;

Output

The output is produced as follows −

Result
18

Example

We can use ORDER BY RAND() to randomize a set of rows or values. To do so, let us create a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The below query adds 7 records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

To verify whether the records are inserted, execute the following query −

Select * From CUSTOMERS;

Following is the CUSTOMERS table −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

Now, we are going to use RAND() function with ORDER BY to randomize the order of the table −

SELECT * FROM CUSTOMERS ORDER BY RAND();

On executing the given query, the output is displayed as follows −

ID NAME AGE ADDRESS SALARY
3 Kaushik 23 Kota 2000.00
1 Ramesh 32 Ahmedabad 2000.00
5 Hardik 27 Bhopal 8500.00
2 Khilan 25 Delhi 1500.00
4 Chaitali 25 Mumbai 6500.00
7 Muffy 24 Indore 10000.00
6 Komal 22 Hyderabad 4500.00
Advertisements