Auto increment start from the biggest value not from 1

Discussion in 'MySQL' started by xampeyan, Jun 13, 2012.

0
  1. #1
    Hi,

    I have a case with mysql (version 5.5.8). i have a table which is id column set as auto increment. in the first process of inserting 30 data (using PHP 5), it's running well. i use "while" for looping process. the data recorded in table increase as 1,2,3 and the last is 30.

    And then, i try to delete all record using query in PHP. and it's work. all data in mysql deleted

    But when i retry to insert the batch of data for the second one (30 data). Id column is starting like this: 60,59,58,57 and the last one is 31.
    It should start from 31, 32,33, until 60

    I don't want to truncate/empty the table. Because in the next process, not all of the data will be deleted, some data will be kept.

    My question is:
    1. Please, tell me about this case. why it happen. (it start from the biggest value)
    2. What should i do, so the next insertion process (after deletion) auto increment start from the last value recorded

    Thank you
     
    xampeyan, Jun 13, 2012 IP
  2. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    Somehow the auto increment got set to a higher value. You can reset it with this query:

    ALTER TABLE my_table_name AUTO_INCREMENT = value;

    Best best is to figure out why the value is higher than expected. Also, at some point did you just empty the table and not truncate it? Delete or empty will not reset the auto increment.
     
    jestep, Jun 14, 2012 IP
  3. xampeyan

    xampeyan Greenhorn

    Messages:
    4
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #3
    Thank you Jestep.
    is it meaning that i have to put

    ALTER TABLE my_table_name AUTO_INCREMENT = value;

    below my deletion code?? So every time i delete the record, i have to reset the table?
     
    xampeyan, Jun 15, 2012 IP
  4. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #4
    If you delete a record it will not reset the auto increment value.

    I would leave the auto increment as is. There shouldn't be any problem with allowing it to continue to increase after you delete records.
     
    jestep, Jun 15, 2012 IP
  5. Rukbat

    Rukbat Well-Known Member

    Messages:
    2,908
    Likes Received:
    37
    Best Answers:
    51
    Trophy Points:
    125
    #5
    An autoincrement field is used as a marker, it should not be used for anything else. So the actual value of the field in any given record is totally irrelevant - it's just a unique value. Any unique value.

    If you want a field that has certain guaranteed values, create such a field and maintain it.
     
    Rukbat, Jun 16, 2012 IP