If you wanted to use/go that route, then why not select a random limit 1
from that table, and then delete that row?
SELECT `column` FROM `table` ORDER BY RAND() LIMIT 1;
On a side note, I would use the auto-inc field still, and store this number
in another field.
Steven Staples
> -----Original Message-----
> From: Jim Lyons [mailto:jlyons4435@stripped]
> Sent: May 28, 2010 11:49 AM
> To: Andre Matos
> Cc: mysql@stripped
> Subject: Re: Using RAND to get a unique ID that has not been used yet
>
> If your specs are that specific (IDs must be between 1 and 999999)
> then you could create a 999999-row table with one integer column and
> prefill it with the numbers 1 to 999999 in random order.
>
> Then you could write a function that would select and return the first
> number in the table, then delete that record so you would not reuse
> it.
>
> Once you've done the work of sorting 999999 numbers in random order
> (which can be done anywhich way) it's easy and you don't have to loop
> an indeterminant number of times. You would be looping an increasing
> number of times as you begin to fill up the table.
>
> Jim
>
> On Fri, May 28, 2010 at 10:38 AM, Andre Matos <andrematos@stripped>
> wrote:
> > 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
> >
> >
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
> >
> >
>
>
>
> --
> Jim Lyons
> Web developer / Database administrator
> http://www.weblyons.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 9.0.819 / Virus Database: 271.1.1/2895 - Release Date: 05/28/10
> 02:25:00
