>-----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=arch...@jab.org
