{"id":11659,"date":"2023-10-24T17:37:11","date_gmt":"2023-10-25T00:37:11","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/mysql-math-functions\/mysql-rand\/"},"modified":"2023-10-24T18:27:35","modified_gmt":"2023-10-25T01:27:35","slug":"mysql-rand","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-math-functions\/mysql-rand\/","title":{"rendered":"MySQL RAND() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL RAND()<\/code> function to get a random number.<\/p>\n\n\n\n

Introduction to MySQL RAND() function<\/h2>\n\n\n\n

The RAND()<\/code> function returns a random number v<\/code> with the following value:<\/p>\n\n\n

0 <= v < 1.0<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here’s the syntax of the RAND()<\/code> function:<\/p>\n\n\n
RAND(seed)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the seed<\/code> argument is an integer<\/a> that serves as a seed value. The seed<\/code> argument is optional.<\/p>\n\n\n\n
The following example uses the RAND()<\/code> function to get a random number between 0 and 1:<\/p>\n\n\n
SELECT<\/span> RAND<\/span>();<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+--------------------+<\/span>\n| RAND()             |\n+--------------------+<\/span>\n| 0.6090137591669464 |\n+--------------------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you use a constant seed value, the RAND()<\/code> function will return the same value each time.<\/p>\n\n\n\n
For example, executing the following statement that uses the RAND()<\/code> function with the same seed value will return the same random number:<\/p>\n\n\n
SELECT<\/span> RAND<\/span>(1<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In practice, you often want to obtain a random integer R  where i <= R < j. <\/p>\n\n\n\n
FLOOR(i + RAND() * (j \u2212 i))<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following returns a random number between 1 and 10:<\/p>\n\n\n
SELECT<\/span> FLOOR<\/span>(1<\/span> + RAND<\/span>() * 9<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+-----------------------+<\/span>\n| FLOOR(1 + RAND() * 9) |\n+-----------------------+<\/span>\n|                     5 |\n+-----------------------+<\/span>\n1 row in set<\/span> (0.01<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL RAND() function with table data example<\/h2>\n\n\n\n
We’ll use the employees<\/code> from the sample database<\/a> for the demonstration:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
If the number of rows in a table is small, you can use the RAND()<\/code> function in the ORDER BY<\/a><\/code> clause to sort the rows of a table randomly:<\/p>\n\n\n
SELECT<\/span> \n  firstName \nFROM<\/span> \n  employees \nORDER<\/span> BY<\/span> \n  RAND<\/span>();<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
You can also use the LIMIT<\/code> clause to get a number of random rows from the employees<\/code> table:<\/p>\n\n\n
SELECT<\/span> \n  firstName \nFROM<\/span> \n  employees \nORDER<\/span> BY<\/span> \n  RAND<\/span>()\nLIMIT<\/span> 5<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The query returns different rows each time you run the query.<\/p>\n\n\n\n
Creating a random function that returns a random integer in a range of integers<\/h2>\n\n\n\n
If you often want to find a random integer between two integers, you can create a stored function<\/a> like this:<\/p>\n\n\n
DELIMITER $$\n\nCREATE<\/span> FUNCTION<\/span> RAND_BETWEEN(min<\/span> INT<\/span>, max<\/span> INT<\/span>)\nRETURNS<\/span> INT<\/span>\nDETERMINISTIC<\/span>\nBEGIN<\/span>\n RETURN<\/span> FLOOR<\/span>(min<\/span> + (RAND<\/span>() * (max<\/span>-min<\/span>)));\nEND<\/span> $$\n\nDELIMITER ;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The RAND_BETWEEN<\/code> function has two parameters min<\/code> and max<\/code>, and returns a random integer between them ( min <= return_integer < max<\/code>).<\/p>\n\n\n\n
The following shows how to call the RAND_BETWEEN()<\/code> function to get a random integer between 1 and 10:<\/p>\n\n\n
SELECT<\/span> RAND_BETWEEN(1<\/span>,10<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+--------------------+<\/span>\n| RAND_BETWEEN(1,10) |\n+--------------------+<\/span>\n|                  4 |\n+--------------------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the RAND()<\/code> function to get a random number between 0 and 1.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
To do that, you can combine the RAND()<\/code> function with the FLOOR()<\/a> function as follows:<\/p>\n\n\n