How to Use RAND() Function in MySQL for Random Numbers

In MySQL, use the RAND() function to generate a random floating-point number between 0 and 1. You can also pass a seed value like RAND(seed) to get repeatable random numbers.

Syntax

The RAND() function returns a random floating-point number between 0 (inclusive) and 1 (exclusive). You can optionally provide a seed value to generate repeatable sequences.

• RAND(): Returns a random number between 0 and 1.
• RAND(seed): Returns a repeatable random number based on the seed.

sql

RAND()
RAND(seed)

Example

This example shows how to generate random numbers using RAND() and how to use it to select random rows from a table.

sql

SELECT RAND() AS random_number;

-- Using RAND() to get a random number between 0 and 100
SELECT FLOOR(RAND() * 101) AS random_int_0_to_100;

-- Selecting 3 random rows from a sample table named 'users'
SELECT * FROM users ORDER BY RAND() LIMIT 3;

Output

random_number
0.726384

random_int_0_to_100
72

-- Example output depends on 'users' table data, but 3 random rows will be shown

Common Pitfalls

Common mistakes when using RAND() include:

• Expecting RAND() to return integers directly; it returns a decimal between 0 and 1.
• Using ORDER BY RAND() on large tables can be slow because it calculates a random value for every row.
• Not using a seed when repeatable random numbers are needed.

sql

/* Wrong: expecting integer directly */
SELECT RAND() * 10;

/* Right: use FLOOR() or ROUND() to get integer */
SELECT FLOOR(RAND() * 10);

/* Slow on large tables */
SELECT * FROM big_table ORDER BY RAND() LIMIT 5;

/* Better approach for large tables: use a random id or other method */

Quick Reference

Usage	Description
RAND()	Returns a random float between 0 and 1
RAND(seed)	Returns a repeatable random float based on seed
FLOOR(RAND() * n)	Generates a random integer from 0 to n-1
ORDER BY RAND()	Randomizes row order (slow on large tables)

Key Takeaways

Use RAND() to get a random decimal number between 0 and 1 in MySQL.

Multiply RAND() by a number and use FLOOR() to get random integers.

Provide a seed to RAND(seed) for repeatable random sequences.

Avoid ORDER BY RAND() on large tables due to performance issues.

Use alternative methods for random row selection on big datasets.