MySQL generate random number not IN table

In an application I made last week, I needed a random id value that was not registered in the database.

I found a solution for the problem I had on the Overflowstack site.

I’m sharing the code here as a note to myself.


SELECT FLOOR(RAND() * 99999) AS random_num
FROM numbers_mst 
WHERE "random_num" NOT IN (SELECT my_number FROM numbers_mst)
LIMIT 1

SELECT FLOOR(RAND() * 99999) AS random_num

FROM numbers_mst

WHERE "random_num" NOT IN (SELECT my_number FROM numbers_mst)

LIMIT 1

Output:

4521

MySQL generate random number not IN table1 min read