MySQL - RAND() Function

Quiz

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

Print Page