Generate random numbers in MySQL without repeat

Question

I have a stored procedure that insert data related to a scooter rent, one of the fields is a "rent code" when i insert the information "rent code" should be a unique random number that don exist in the table "rents"

here is that i tried

SELECT FLOOR(RAND() *9999)+1 AS random_num
FROM reservaciones 
WHERE "random_num" NOT IN (SELECT reservaciones.Codigo_Reservacion FROM reservaciones)
LIMIT 1

in the table i got

1
2
3

when i run the code if i reduce the limit to 4 (example) the query still generates the numbers that i already got in my table

1) You [can't use the alias `random_num` in the WHERE clause](https://stackoverflow.com/questions/34955911/why-cant-i-use-column-aliases-in-the-next-select-expression). Try wrapping the `select` in a subquery 2) Putting quotes around `random_num` means you're comparing the literal string "r-a-n-d-o-m n-u-m" to the values in the Codigo_Reservacion column, not the number generated 3) RAND() also accepts a seed value (or intializer). See the docs for how it impacts the results https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_rand — SOS, Mar 16 '22 at 04:37
`CEIL(RAND() *9999)` is more clear than `FLOOR(RAND() *9999)+1`, imho... — Akina, Mar 16 '22 at 05:23

Akina · Answer 1 · 2022-03-16T05:21:41.810

Create and use user-defined function similar to:

CREATE FUNCTION generate_random_number (upper_limit INT)
RETURNS INT
BEGIN
    IF upper_limit = (SELECT COUNT(*) FROM main_table WHERE id <= upper_limit) THEN
        RETURN NULL;
    END IF;
    IF upper_limit IS NULL THEN
        SELECT REPEAT('9', LENGTH(MAX(id) + 1)) FROM main_table INTO upper_limit;
    END IF;
    RETURN (SELECT t1.id + 1
            FROM (SELECT id FROM main_table
                  UNION ALL
                  SELECT 0) t1
            LEFT JOIN main_table t2 ON t1.id = t2.id - 1
            WHERE t2.id IS NULL
              AND t1.id < upper_limit
            ORDER BY RAND() LIMIT 1);
END

upper_limit parameter specifies the range from which the number should be generated. Can be less than max. existing value. Cannot be greater than 2147483647. Cannot be set to NULL if max. existing value is 999999999 or greater (you may expand this limit by set the parameter and output datatypes to BIGINT).

Function returns NULL if there is no free number in specified range.

fiddle with some comments.

Generate random numbers in MySQL without repeat

1 Answers1