Creating a random number using MySQL

Controlling the range

Want to pick a random integer within a specific range, say between min and max? This is your formula:

# "Feeling lucky? Pick a number, any number...just make sure it's between min and max!" ;)
SELECT FLOOR(RAND() * (max - min + 1)) + min;


Heads up: this method doesn't prevent repeating numbers. For that, consider using a temporary table to track the numbers already drawn.

Keeping the database clean

In case you're wondering, no, you don't need to save these random values in your database unless you have a specific reason to. Opt for creating and using them in your SELECT queries directly to maintain a lean database.

Custom random number generator

For complex ranges or if you want to re-use some logic, you can define a function:

# "Be the master of randomness!"
CREATE FUNCTION GetRandomNumber(min INT, max INT) 
RETURNS INT DETERMINISTIC
BEGIN
  RETURN FLOOR(RAND() * (max - min + 1)) + min;
END;


To extract a random number within a given range, summon your function:

# "Oh mighty function, I summon thee!"
SELECT GetRandomNumber(100, 500) as RandomNumber;


Non-repeating random numbers

Here's a strategy when repeats are a no-no: use temporary tables. Store the already drawn random numbers and make sure they don't appear again. Sound fair, right?

# "Ever felt like you're having deja-vu? Not today!"
CREATE TEMPORARY TABLE UsedNumbers (number INT);
SELECT FLOOR(RAND() * (max - min + 1)) + min AS UniqueRandom
WHERE UniqueRandom NOT IN (SELECT number FROM UsedNumbers);


Performance awareness

Is performance a concern when dealing with large datasets? Then beware: using RAND() in an SQL ORDER BY clause can bring performance down. Explore alternative ways to select random records are advisable.