List:General Discussion« Previous MessageNext Message »
From:Jim Lyons Date:May 28 2010 3:49pm
Subject:Re: Using RAND to get a unique ID that has not been used yet
View as plain text  
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
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