MySQL auto_increment range?
Results 1 to 5 of 5

Thread: MySQL auto_increment range?

  1. #1
    Senior Member roswell1329's Avatar
    Join Date
    Jan 2002
    Posts
    670

    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?
    /* You are not expected to understand this. */

  2. #2
    Jaded Network Admin nebulus200's Avatar
    Join Date
    Jun 2002
    Posts
    1,356
    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.
    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.

    (Merovingian - Matrix Reloaded)

  3. #3
    Senior Member roswell1329's Avatar
    Join Date
    Jan 2002
    Posts
    670
    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.
    /* You are not expected to understand this. */

  4. #4
    Jaded Network Admin nebulus200's Avatar
    Join Date
    Jun 2002
    Posts
    1,356
    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
    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.

    (Merovingian - Matrix Reloaded)

  5. #5
    Senior Member roswell1329's Avatar
    Join Date
    Jan 2002
    Posts
    670
    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.
    /* You are not expected to understand this. */

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •