What is rand () in mysql?

Question

Example

Return a random decimal number (no seed value - so it returns a completely random number >= 0 and <1):

Nội dung chính Show

Definition and Usage
Parameter Values
Technical Details
More Examples
RAND() function
What does rand () function do?
What is Rand in SQL?
Is rand () defined in library?
What is difference between rand () and Srand ()?

SELECT RAND();

Try it Yourself »

Definition and Usage

The RAND() function returns a random number between 0 (inclusive) and 1 (exclusive).

Syntax

RAND(seed)

Parameter Values

Parameter	Description
seed	Optional. If seed is specified, it returns a repeatable sequence of random numbers. If no seed is specified, it returns a completely random number

Technical Details

Works in:	From MySQL 4.0

More Examples

Example

Return a random decimal number (with seed value of 6):

SELECT RAND(6);

Try it Yourself »

Example

Return a random decimal number >= 5 and <10:

SELECT RAND()*(10-5)+5;

Try it Yourself »

Example

Return a random number >= 5 and <=10:

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

Try it Yourself »

Last update on August 19 2022 21:51:22 (UTC/GMT +8 hours)

RAND() function

MySQL RAND() returns a random floating-point value between the range 0 to 1. When a fixed integer value is passed as an argument, the value is treated as a seed value and as a result, a repeatable sequence of column values will be returned.

Syntax:

RAND(), RAND(M);

Argument:

Name	Description
M	A number between 0 to 1.

Pictorial presentation of MySQL RAND() function

Example of MySQL RAND() function

Code:

SELECT RAND();

Explanation:

The above MySQL statement will return a random value between 0 and 1.

Sample Output:

mysql> SELECT RAND();
+-------------------+
| RAND()            |
+-------------------+
| 0.369500624360052 | 
+-------------------+
1 row in set (0.00 sec)

Example: RAND() function with seed value

Code:

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

Explanation:

The above MySQL statement will return a random value (between 0 and 1) and the repeatable value using seed in the argument.

Sample Output:

mysql> SELECT RAND(),RAND(2),RAND(2);
+-------------------+-------------------+-------------------+
| RAND()            | RAND(2)           | RAND(2)           |
+-------------------+-------------------+-------------------+
| 0.964232316207357 | 0.655586646549019 | 0.655586646549019 | 
+-------------------+-------------------+-------------------+
1 row in set (0.00 sec)

Example: RAND() function using table

Code:

SELECT FLOOR(RAND()*10)
FROM category;

Explanation:

The above MySQL statement will return the largest integer value after multiply the randomly generated number by 10 but not greater than the generated number.

Sample Output:

mysql> SELECT FLOOR(RAND()*10)
    ->   FROM category; 
+------------------+
| FLOOR(RAND()*10) |
+------------------+
|                7 | 
|                6 | 
|                2 | 
|                0 | 
|                6 | 
+------------------+
5 rows in set (0.29 sec)

Example: RAND() function with where clause

Code:

SELECT pub_name,country,no_of_branch,
   FLOOR(RAND(2)*20)
      FROM publisher
WHERE no_of_branch>FLOOR(RAND(2)*20);

Explanation:

The above MySQL statement will return those rows from publisher table which have no_of_branchs are more than the greatest number after generating the random number with FLOOR function.

Sample Output:

mysql> SELECT pub_name,country,no_of_branch,
    ->    FLOOR(RAND(2)*20)
    ->       FROM publisher
    ->     WHERE no_of_branch>FLOOR(RAND(2)*20);
+--------------------------+---------+--------------+-------------------+
| pub_name                 | country | no_of_branch | FLOOR(RAND(2)*20) |
+--------------------------+---------+--------------+-------------------+
| Jex Max Publication      | USA     |           15 |                13 | 
| BPP Publication          | India   |           10 |                 2 | 
| Mountain Publication     | USA     |           25 |                12 | 
| Summer Night Publication | USA     |           10 |                17 | 
| Novel Publisher Ltd.     | India   |           10 |                 7 | 
+--------------------------+---------+--------------+-------------------+
5 rows in set (0.13 sec)

PHP script:

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>example-rand-function - php mysql examples | w3resource</title>
<meta name="description" content="example-rand-function - php mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>A list of Publishers, their country and no of branches of that publishing house, where no of branches are more than
the random number generated by using the FLOOR function:</h2>
<table class='table table-bordered'>
<tr>
<th>Publishers name</th><th>Country</th><th>Number of branches</th><th>FLOOR(RAND(2)*20)</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username"; 
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT pub_name,country,no_of_branch,FLOOR(RAND(2)*20) 
           FROM publisher  
           WHERE no_of_branch>FLOOR(RAND(2)*20)') as $row) {
echo "<tr>";
echo "<td>" . $row['pub_name'] . "</td>";
echo "<td>" . $row['country'] . "</td>";
echo "<td>" . $row['no_of_branch'] . "</td>";
echo "<td>" . $row['FLOOR(RAND(2)*20)'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

Example : RAND() function using order by

Code:

 SELECT *  FROM category 
ORDER BY RAND();

Explanation:

The above MySQL statement will return rows from category table by a random order.

Sample Output:

 mysql> SELECT *  FROM category 
    -> ORDER BY RAND();
+---------+--------------+
| cate_id | cate_descrip |
+---------+--------------+
| CA001   | Science      | 
| CA005   | Medical      | 
| CA003   | Computers    | 
| CA004   | Nature       | 
| CA002   | Technology   | 
+---------+--------------+
5 rows in set (0.05 sec)

PHP script:/strong>

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>example-rand-function - php mysql examples | w3resource</title>
<meta name="description" content="example-rand-function - php mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>A list of categories and their descriptions in a random order:</h2>
<table class='table table-bordered'>
<tr>
<th>Category id</th><th>Description</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT * FROM category ORDER BY RAND()') as $row) {
echo "<tr>";
echo "<td>" . $row['cate_id'] . "</td>";
echo "<td>" . $row['cate_descrip'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

Online Practice Editor:

All Mathematical Functions

Previous: RADIANS()
Next: ROUND()

What does rand () function do?

Description. RAND returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned every time the worksheet is calculated.

What is Rand in SQL?

SQL Server RAND() Function The RAND() function returns a random number between 0 (inclusive) and 1 (exclusive).

Is rand () defined in library?

rand() is defined in the standard library. It is declared in the headers <stdlib.

What is difference between rand () and Srand ()?

The srand() function sets the starting point for producing a series of pseudo-random integers. If srand() is not called, the rand() seed is set as if srand(1) were called at the program start. Any other value for seed sets the generator to a different starting point.

programming mysql ROUND SQL ROUND MySQL Current date MySQL MySQL date() function Add date SQL