Using RAND to get a unique ID that has not been used yet

[Andre Matos]

Hi All,

I have a table that uses auto_increment to generate the Id automatically working fine.
However, I need to create a new table where the Id must be a number generated randomly,
so I cannot use the auto_increment. 

MySQL has a function RAND. So I could use something like this:

SELECT FLOOR(RAND() * COUNT(*)) AS RandId FROM mytable

But, let's suppose that the RandId is a number that was already used in the table. Then I
need to run the SELECT again and again until I find a number that hasn't been used.

Is there a way to have this SELECT to loop until it finds a number that hasn't been used?

The RandId must be only numbers and length of 6 (from 1 to 999999). No other character is
allowed.

Thanks for any help!

Andre

--
Andre Matos
andrematos@stripped