When I mentioned having everything in the Query, I was thinking about this. I don't want to have a loop repeating the query until I get a unique Id. This is ridicules and imagine how many queries I might end up running. No way!
Thanks for the warning and feedback! Andre -- Andre Matos andrema...@mineirinho.org On 2010-05-28, at 1:51 PM, Jerry Schwartz wrote: > >> -----Original Message----- >> From: Andre Matos [mailto:andrema...@mineirinho.org] >> Sent: Friday, May 28, 2010 1:44 PM >> To: Steven Staples >> Cc: mysql@lists.mysql.com >> Subject: Re: Using RAND to get a unique ID that has not been used yet >> >> 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. >> > [JS] That would be a VERY bad idea. My predecessor designed our system that > way: it would generate a random key, check to see if that key were in use, > and > either use it or try again. > > As you would expect, the whole process get slower and slower as we "ran out" > of unique keys. Eventually the whole application became unusable. > > Regards, > > Jerry Schwartz > Global Information Incorporated > 195 Farmington Ave. > Farmington, CT 06032 > > 860.674.8796 / FAX: 860.674.8341 > > www.the-infoshop.com > > > > >> 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=je...@gii.co.jp > > > > > > -- > 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
