MySQL Arithmetic Random Numbers (RAND)

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

Example

Generate a random number

To generate a pseudorandom floating point number between 0 and 1, use the RAND() function

Suppose you have the following query

SELECT i, RAND() FROM t;

This will return something like this

iRAND()
10.6191438870682
20.93845168309142
30.83482678498591

Random Number in a range

To generate a random number in the range a <= n <= b, you can use the following formula

FLOOR(a + RAND() * (b - a + 1))

For example, this will generate a random number between 7 and 12

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

A simple way to randomly return the rows in a table:

SELECT * FROM tbl ORDER BY RAND();

These are pseudorandom numbers.

The pseudorandom number generator in MySQL is not cryptographically secure. That is, if you use MySQL to generate random numbers to be used as secrets, a determined adversary who knows you used MySQL will be able to guess your secrets more easily than you might believe.



Got any MySQL Question?