MySQL auto_increment range?

[roswell1329]

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?

[nebulus200]

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 ? )

If yes to the first one, you may be able to use something from :
http://dev.mysql.com/doc/mysql/en/ex...increment.html

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.

[roswell1329]

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.

[nebulus200]

In interest of being quick with reply, you can typedef index ... or at least I am quite sure you can...will try to find something for you shortly.

http://dev.mysql.com/doc/mysql/en/data-size.html


http://dev.mysql.com/doc/mysql/en/numeric-types.html

You probably want a smallint:

TINYINT 1 -128 127
0 255
SMALLINT 2 -32768 32767
0 65535
MEDIUMINT 3 -8388608 8388607
0 16777215
INT 4 -2147483648 2147483647
0 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807
0 18446744073709551615

[roswell1329]

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.