A function that can randomly pick an integer from a given range can be used by applications that need a number dice roll and random generation of strings, numbers, and even random generation of complex passwords. Let's say you would like to randomly pick a number from 1 to 10. This gives us 10 choices to randomly pick, not 9 as can be mistakenly thought of since 10 minus 1 equals 9. The same is with the range of choices from 0 to 10. This gives 11 possibilities, not 10. To illustrate:
- The range of choices should include the value of the lower end of the range
- It should also include the value of the higher end of the range
To generate a random number from a given lower value and a higher value, use the formula below, where minRange is the lower end of the range and maxRange is the higher end of the range:
minRange + FLOOR(RAND() * (maxRange - minRange + 1))
The formula gives minRange a random integer to add with, where the return from the RAND() function is passed to the math flooring function. The return from RAND() is multiplied with the difference between minRange and maxRange to set the decimal range that can be randomly created. The + 1 added to the difference between the two values is needed so that the range includes the maxRange number itself. This addition of an extra 1 can easily be missed which results in erroneous results especially when the lower value for the range is a negative number.
To illustrate the common mistakes on how a formula for a simple random range picker often leads to a wrong analysis, let us first fill the values with minRange = 1 and maxRange = 10. This gives us:
SELECT 1 + FLOOR(RAND() * 10);
The common mistake here is that some devs will think a range from 1 to 10 should result in a formula that would include "RAND() * 9" when they use a range formula of 10 - 1 = 9.
And filling the values with minRange = -10 and maxRange = 10 gives us:
SELECT -10 + FLOOR(RAND() * 21);
The common mistake happens when a quick mental analysis of a range formula is made, some devs think that the solution for the second formula will result to "RAND() * 20".
The given formula is used in the function below. The integers for minRange and maxRange can be positive or negative, including zero. The range of numbers returned will correctly include the values of minRange and maxRange as was explained above.
DELIMITER $$ DROP FUNCTION IF EXISTS `randomRangePicker` $$ CREATE DEFINER=`root`@`localhost` FUNCTION `randomRangePicker`( minRange INT, maxRange INT) RETURNS int(11) BEGIN DECLARE pick INT; SET pick = minRange + FLOOR(RAND() * (maxRange - minRange + 1)); RETURN pick; END $$ DELIMITER ;
To use the function, simply pass the two needed parameters like:
SELECT randomRangePicker(-1, 1);
This will return either -1, 0, or 1.
See the following user defined functions for samples where randomRangePicker() can be used:
Updated: 10/14/2012
