List:General Discussion« Previous MessageNext Message »
From:Andre Matos Date:May 28 2010 5:56pm
Subject:Re: Using RAND to get a unique ID that has not been used yet
View as plain text  
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
andrematos@stripped




On 2010-05-28, at 1:51 PM, Jerry Schwartz wrote:

> 
>> -----Original Message-----
>> From: Andre Matos [mailto:andrematos@stripped]
>> Sent: Friday, May 28, 2010 1:44 PM
>> To: Steven Staples
>> Cc: mysql@stripped
>> 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
>> andrematos@stripped
>> 
>> 
>> 
>> 
>> 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: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
>>> 
>>> 
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=1
>>> 
>> 
>> 
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 
> 
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 

Thread
Using RAND to get a unique ID that has not been used yetAndre Matos28 May
  • Re: Using RAND to get a unique ID that has not been used yetJim Lyons28 May
    • RE: Using RAND to get a unique ID that has not been used yetSteven Staples28 May
      • Re: Using RAND to get a unique ID that has not been used yetAndre Matos28 May
        • RE: Using RAND to get a unique ID that has not been used yetJerry Schwartz28 May
          • Re: Using RAND to get a unique ID that has not been used yetAndre Matos28 May
    • RE: Using RAND to get a unique ID that has not been used yetJerry Schwartz28 May
  • Re: Using RAND to get a unique ID that has not been used yetPerrin Harkins28 May
  • Re: Using RAND to get a unique ID that has not been used yetKevin \(Gmail\)28 May