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
>
