The separate table for the IDs is probably best solution, maybe counting on
caching of the table with an index on the id value to speed up the 'where'
clause; this checks what numbers are left instead of what numbers have been
used; the disadvantage is that you have to manage a second table with a
million rows!
You could generate a memory table when you open the session, populate it
with all possible values and then delete all already assigned values.
You would have to do this only once and then all possible unused values
would be available.
It shouldn't get slower with time (in fact it might speed up as the used
rows are progressively deleted).
It has the advantage that the random function is called only once: whereas
using a single table requires looping until a unique random value is found,
and as the table fills this will get really slow.
----- Original Message -----
From: "Jerry Schwartz" <je...@gii.co.jp>
To: "'Andre Matos'" <andrema...@mineirinho.org>; "'Steven Staples'"
<sstap...@mnsi.net>
Cc: <mysql@lists.mysql.com>
Sent: Friday, May 28, 2010 6:51 PM
Subject: RE: Using RAND to get a unique ID that has not been used yet
-----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=kfone...@ymail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
