Re: Advice on Random Numbers
*] I need to generate a RANDOM UNIQUE number for every new record. Is there an *] easy way to accomplish this inside of mySQL. *] What are the requirements for 'random' and 'unique'? Do you need some sort of cryptographic randomness? Does uniqueness be kept across tables? If you 'only' need a unque id you could you AUTO_INCREMENT. If you want uniqueness to carry over tables etc. you could take a look at the aproach made by the Apache module mod_unique_id. If you need a cryptographic randomness you should look into some litterature about the area so as to get a better understanding of what you truly need. -- Aigars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Advice on Random Numbers
On Thu, 24 May 2001, Aigars Grins wrote: *] I need to generate a RANDOM UNIQUE number for every new record. Is there an *] easy way to accomplish this inside of mySQL. *] What are the requirements for 'random' and 'unique'? Do you need some sort of cryptographic randomness? Does uniqueness be kept across tables? If you 'only' need a unque id you could you AUTO_INCREMENT. If you want uniqueness to carry over tables etc. you could take a look at the aproach made by the Apache module mod_unique_id. If you need a cryptographic randomness you should look into some litterature about the area so as to get a better understanding of what you truly need. Would an MD5 encryption be another option? Use a auto incrementing ID to keep your records sorted, then the MD5 field for your unique randomness. -- === Kip Turkphone: 915.234.5678 Systems Administrator or 800.695.9016 Killer of Spam/Writer of Code/Penguin Proponent West Central Net fax: 915.656.0071 === - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Advice on Random Numbers
*] I need to generate a RANDOM UNIQUE number for every new record. Is there an *] easy way to accomplish this inside of mySQL. *] What are the requirements for 'random' and 'unique'? Do you need some sort of cryptographic randomness? Does uniqueness be kept across tables? If you 'only' need a unque id you could you AUTO_INCREMENT. If you want uniqueness to carry over tables etc. you could take a look at the aproach made by the Apache module mod_unique_id. If you need a cryptographic randomness you should look into some litterature about the area so as to get a better understanding of what you truly need. Would an MD5 encryption be another option? Use a auto incrementing ID to keep your records sorted, then the MD5 field for your unique randomness. Good idea. Practical and easy (there are a lot fast MD5 implementations easy accessable). That should take care of the cryptographic randomness. Uniqueness won't be true across tables but that might not be necessery. If it was a value similar to mod_unique_id could be MD5'ied. -- Aigars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Advice on Random Numbers
I need to generate a RANDOM UNIQUE number for every new record. Is there an easy way to accomplish this inside of mySQL. TIA Dave Carter Chief Web Architect Accelerated Business Technologies, Inc. http://www.abti.cc 717.464.2970 (mail filter bologna: sql,database,query) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Advice on Random Numbers
On Wed, May 23, 2001 at 02:22:02PM -0400, Dave Carter spake thusly: *] I need to generate a RANDOM UNIQUE number for every new record. Is there an *] easy way to accomplish this inside of mySQL. *] The way I do this for session ids is through a perl function that I wrote a long time ago. Essentially I create a really long string of mixed case alpha characters and numeric digits. I suppose you could use the same method from perl for record numbers. -- -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ Peter L. Berghold[EMAIL PROTECTED] Schooner Technology Consulting http://www.berghold.net Unix Professional ServicesPerl Perl/CGI mod_perl - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Advice on Random Numbers
*] I need to generate a RANDOM UNIQUE number for every new record. Is there an
*] easy way to accomplish this inside of mySQL.
*]
The way I do this for session ids is through a perl function that I
wrote a long time ago. Essentially I create a really long string of
mixed case alpha characters and numeric digits.
I suppose you could use the same method from perl for record numbers.
Hmm. As a coincidence, I just rewrote a function kind of like this
about 15 minutes ago.
It basically does (mod_perl):
my $id;
while (1) {
$id = int(rand(40));
my $sth = $dbh-prepare(INSERT INTO tab (id) VALUES ($id));
if ($sth-execute) {
$sth-finish;
last;
}
$sth-finish;
# try again
}
return $sid;
This ONLY works if id is a UNIQUE (ie, PRIMARY) key. It relies on the query
failing to determine whether it's taken or not. This wouldn't scale very
well if you need to create a million records per minute, or plan to store
more than 100,000 records at a time, because at that point, it becomes easier
to brute force guess a valid one. At about 40,000,000, the chances become 1 in 100
that the INSERT will fail. At 400,000,000, they're 1 in 10.
Also, it can't tell the difference between real error and a duplicate key error.
A bail out scheme after say, 10 tries wouldn't be a bad idea.
--
Michael Bacarella [EMAIL PROTECTED]
Technical Staff / System Development,
New York Connect.Net, Ltd.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
