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

mysql

Solution

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:

- Selects random number between 0 - 1 using RAND().

- Amplifies that to be a number between 0 - 99999.

- Only chooses those that do not already exist in table.

- Returns only 1 result.

Problem

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.

Original source