> *] 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(4000000000));
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
