It seems to be a good approach, although I was trying to get this by querying the table without creating another table to keep the Ids.
Thanks, Andre -- Andre Matos andrema...@mineirinho.org On 2010-05-28, at 12:15 PM, Steven Staples wrote: > 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=andrema...@mineirinho.org > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
