If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

MySQL auto_increment range?

Does anyone know of a way to simulate a range for an ID value in MySQL. Basically, I'm creating a database that will store the basic user information found in password files on various systems. I want the user's UID to be the primary key for this table, and I was wondering if there's a way to find a new unused UID by using auto_increment with a range so if it hits the max UID value, it just starts over at the start of the range looking for a free value.

I know Oracle can do something like that, but it looks like MySQL can't do that natively. Is there a way to hack it, or should I really be using code in the interface to find a new appropriate value?

So if I read you right, you are saying if you have a totally new user and just want to assign them a unique id ? Ie, you are not trying to resolve an index collision ? If you have a case where the index may not be unique, you may want to use either a different index or a combination of them to ensure that they are unique (maybe username and uid ? )

There are a couple of APIs there that you could use or you could do something like a max(index) query and add one...the wrapping around could be done, but as far as I could tell you'd have to do it yourself.

There is only one constant, one universal, it is the only real truth: causality. Action. Reaction. Cause and effect...There is no escape from it, we are forever slaves to it. Our only hope, our only peace is to understand it, to understand the 'why'. 'Why' is what separates us from them, you from me. 'Why' is the only real social power, without it you are powerless.

You're on the right track, nebulus200, but AUTO_INCREMENT does not have a defined upper limit. I don't want the UID to exceed a certain value (say 600000), but AUTO_INCREMENT will continue going until it hits the column-type size limit (INT would be more than 2 trillion, I think). I'd like my table to start over at 1 when it hits a defined upper limit. Then, if the number 1 is already taken, it would keep incrementing until it found a unique value.

Oracle does this natively, but I'm thinking I'll have to define a routine for MySQL.

There is only one constant, one universal, it is the only real truth: causality. Action. Reaction. Cause and effect...There is no escape from it, we are forever slaves to it. Our only hope, our only peace is to understand it, to understand the 'why'. 'Why' is what separates us from them, you from me. 'Why' is the only real social power, without it you are powerless.

Thanks neb, but since I have a few UID's above 65535, I'll have to go with the next size up. I've already typed that column to be a mediumint, I just want the internal counter to stop at 600000 rather than 16 million.

I think I'll just write a routine to do it in PHP. Thanks again for your help.