If I understand your question, the advice from Your Mother might be just what you need -- unless you were actually looking for a numeric-string type of value, such as might be used as a "PIN" (personal identifying number) by the person to whom the number is being assigned, in which case as 128-bit (16-character 16-byte, 22-character if base64-encoded) UUID/GUID kind of value might be inappropriate.

Since you specifically mention wanting a "unique but random" number, I assume the other replies about using an "autoincrement" field would be inappropriate as well, since these are not at all random.

A solution I've used in the past (admittedly a kluge with a minor limitation) is to generate a randomized list of numbers first, store that list in a separate table, then select from it sequentially as needed. Include a "status" field along with the randomized numeric value field, and hand out random but unique numbers like this: 

my ($pin) = $dbh->selectrow_array( "select pin_value from pins where s
+tatus is null limit 1 for update" );

$dbh->do( "update pins set status='used' where pin_value=$pin" );

[download]
The "for update" in the select is supposed to keep some other query from fetching the same row until the update has been done.

The limitation, of course, is that you need to make sure the pins table doesn't run out of available rows. It's easy enough to solve with a cron job or something that checks the number of unused rows at regular intervals and adds more whenever it gets too close to zero. In this case, the "add_pins" process just needs to make sure that only adds numbers that haven't been used yet, and that's pretty easy using a hash. For that matter, it's not hard to make sure your initial sequence of random numbers is big enough.

This approach is really nice if you want to put any sort of constraint on the range of possible numeric values being handed out (e.g. they should all be 6-digit, not begin with 0, not contain "666", or whatever).

In reply to Re: DBI Insert Unique Customer ID by graff
in thread DBI Insert Unique Customer ID by kalchas

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.