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
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
>
