[thelist] mysql primary key question..

[Joel Canfield]

> i need to set up an auto_increment field inside mysql. for
> various reasons, the maximum size is 3. but i don't want this
> to be ONLY integers because that limits me until 999 numbers
> only.  since i have all flexibility for these three digits or
> letters, i want to include numbers and some characters into
> it as well. e.g.,
>
>   m78
>   23a
>   1pt
>   1~8
>   !76
>
> etc. can all be valid keys for me.
>
> how can i generate such numbers on the fly? if not inside
> mysql, then inside php? AND...be sure that the "key" so
> generated has not been used before as an id field in my data?

First, you ain't gonna get anything like 'auto' in your increment. You can
create a unique constraint on the field, which will refuse to allow
duplicates. Then, get a table of ASCII sort order and create your own
incrementing script, and do it just like real numbers: 001 thru 00[whatever
the last ASCII char is] then roll over to 010 thru 01[last ASCII] on thru
0[last ASCII][last ASCII]

This should give you 128^3 possible values, or about two million. (Anybody:
128 valid ASCII characters, right?)

But if possible, I'd take a good look at this three-digit limit. So much
easier to just let it auto-increment.

joel