[thelist] mysql primary key question..
Erick Papadakis
erick_papadakis at yahoo.com
Mon Jul 8 10:16:01 CDT 2002
thanks joel. yes, that is what i feared, that mysql will not allow such
increments.
1. so, if i were to really do such a thing in php myself, any idea how i
would write the logic for creation of these strings? i mean, for a
moment, if we forget that i need to do anything with the database. let us
say i only want to print out all the possible values in using 128 ascii
characters that can be positioned into 3 slots (128^3 = some millions).
actually, i want to list out all the possible values including just 1
digit strings, then 2 digit strings (all possible) and then 3 digit
strings). sorry if this is a stupid question, but does this involve
recursion in any way? which i am not very good with. would really
aprpeciate any ideas.
2. if i were to generate all these strings beforehand, and put them into
a mysql table with number IDs and then tag the row as "used" whenever i
used one of these, do u think that is a good way of doing it? e.g.,
POSSIBLE_KEYS table
1 aaa used
2 aab used
3 aac -
...etc
do u think this is good? everytime i insert a new row into my own table,
i do a "SELECT MAX FROM POSSIBLE_KEYS" and if used, then i set that as
"used" from the POSSIBLE_KEYS. the only problem is that, according to
your calculation, the POSSIBLE_KEYS table contains a couple of million
rows, so the perfoirmance is going to be miserable.
UNLESS, i can figure out some way to pass the current id value to my
function, which figures out what is the next possible value (without any
POSSIBLE_VALUES table) and returns that value. which in turns brings us
back to my first question: how to do it?
would really really appreciate any thoughts!
thanks/erick
>
> 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
__________________________________________________
Do You Yahoo!?
Sign up for SBC Yahoo! Dial - First Month Free
http://sbc.yahoo.com
More information about the thelist
mailing list