How to pick a random number for a list in MySQL

April 29, 2023

I want to pick a random number from a list of integers in a MySQL Stored Procedure. This list is generated from another function using some calculations.

Example:

[41,69,9,31,10,33,13,73,20,62,21,58,22,39]

I tried the RAND() function but they are dependent on constant min, max, and step values it seems. Any help would be great.

Note:
This list of numbers is not always constant, so they do not have constant min, max, or step values.

>Solution :

You can use the RAND() function along with the COUNT() function to select a random element from your list of integers.

Here is an example Stored Procedure that demonstrates how you can do this:

DELIMITER //
CREATE PROCEDURE select_random_number()
BEGIN
    DECLARE list VARCHAR(255);
    DECLARE count INT;
    DECLARE random_index INT;
    DECLARE random_number INT;

-- Generate the list of integers
    SET list = '41,69,9,31,10,33,13,73,20,62,21,58,22,39';

-- Get the count of integers in the list
    SET count = LENGTH(list) - LENGTH(REPLACE(list, ',', '')) + 1;

-- Generate a random index between 1 and the count of integers
    SET random_index = FLOOR(RAND() * count) + 1;

-- Select the integer at the random index
    SET random_number = SUBSTRING_INDEX(SUBSTRING_INDEX(list, ',', 
    random_index), ',', -1);

-- Return the random number
    SELECT random_number;
END //
DELIMITER ;

You can call this Stored Procedure using the following query:

CALL select_random_number();

This will return a random integer from your list of integers. Note that you will need to update the value of the list variable in the Stored Procedure to match your own list of integers.