mysql - How to Generate Random number without repeat in database using PHP?

Question

I would like to generate a 5 digit number which do not repeat inside the database. Say I have a table named numbers_mst with field named my_number.

I want to generate the number the way that it do not repeat in this my_number field. And preceding zeros are allowed in this. So numbers like 00001 are allowed. Another thing is it should be between 00001 to 99999. How can I do that?

One thing I can guess here is I may have to create a recursive function to check number into table and generate.

Answer 1

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

What this does:

1. Selects random number between 0 - 1 using RAND().
2. Amplifies that to be a number between 0 - 99999.
3. Only chooses those that do not already exist in table.
4. Returns only 1 result.