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:jlyons4...@gmail.com] > Sent: May 28, 2010 11:49 AM > To: Andre Matos > Cc: mysql@lists.mysql.com > 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 <andrema...@mineirinho.org> > 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 > > andrema...@mineirinho.org > > > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com > > > > > > > > -- > 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=sstap...@mnsi.net > > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
