MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- a table with existing id values
CREATE TABLE main_table (id INT)
SELECT 1 id UNION SELECT 2 UNION SELECT 5 UNION SELECT 7;

Records: 4  Duplicates: 0  Warnings: 0


-- the function which returns random non-existing number
-- within the range from 1 till specified upper limit
-- if upper limit for this number is not specified
-- then it is set automatically within existing numbers range
CREATE FUNCTION generate_random_number (upper_limit INT)
RETURNS INT
BEGIN
-- check does there exists non-used value in specified range
    IF upper_limit = (SELECT COUNT(*) FROM main_table WHERE id <= upper_limit) THEN
        RETURN NULL;
    END IF;
-- generate upper limit if not specified
    IF upper_limit IS NULL THEN
        SELECT REPEAT('9', LENGTH(MAX(id) + 1)) FROM main_table INTO upper_limit;
    END IF;
-- generate and return random non-existent number
    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

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)


-- generate with upper limit set
SELECT generate_random_number(10);

FUNCTION fiddle.generate_random_number does not exist


-- generate with upper limit not set
SELECT generate_random_number(NULL);

FUNCTION fiddle.generate_random_number does not exist


-- generate with upper limit set but no free numbers in it
SELECT generate_random_number(2);

FUNCTION fiddle.generate_random_number does not exist


DELETE FROM main_table WHERE id = 1;
-- generate with non-present id=1
-- (execute a lot of times, 1 will be generated sometimes)
SELECT generate_random_number(3);

FUNCTION fiddle.generate_random_number does not exist