MySQL: Reset Auto Increament Number

 

I have a database table with a auto increment column for primary key. As the records being add and delete many times, the auto increment value will keep increasing.

Problem One:
If I have entered 10 records, and deleted 9th, 10th records. The next auto increment value will be 11, not 9.

Solution:
Run a query: ALTER TABLE tablename AUTO_INCREMENT = 1

This will reset the next auto increment value to current largest value in the auto increment column + 1. So, the auto increment value of next inserted record will start from 9.

Problem Two:
If I have entered 10 records, and deleted center records – 4th, 5th. I want to insert next record as 4th not 11th.

Solution:
Run the following query:
SET insert_id = 4;
INSERT INTO tablename VALUES ('blah', '...');

This will add the next record into record 4th.

The SET insert_id = #(where # is the next auto increment value you want to use) will reset the next auto increament value, the next query(INSERT) you run will use your choice of value.

Note: only effective for the immediate next query, one time.

 

Connect with LiewCF

Enter your email address to receive free updates. Find LiewCF on Twitter, Facebook, and Google+

32 Responses to MySQL: Reset Auto Increament Number

  1. spoonfork April 15, 2004 at 4:21 pm #

    yeah, but how would you automatically locate the gaps in insert_id if the records are deleted randomly?

  2. LcF April 15, 2004 at 4:47 pm #

    the first condition to use insert_id is you must provide the auto increament value that you want to use.
    Do you mean that whenever you insert a new record, the new record will fill in the gap(s)? I have no idea about it yet and I don’t think it is good idea to do so. In what situation you need insert record like this?

    Or, do you mean sort the records accordingly? Maybe you can do like this: Get the total number of records. Run a for loop, check the auto increament column value against loop seed. Alter the column value if not match.

  3. tkh April 16, 2004 at 11:56 am #

    Havent learn mysql yet. going to start after my final. always take extra subject in every sem, after minus all my assignment + project.. dont have much space for me to learn other knowledge..

  4. LcF April 16, 2004 at 1:48 pm #

    if you know SQL then MySQL is not a problem to you. :)
    just sometimes need to refer to its specified commands..

  5. tkh April 18, 2004 at 11:56 am #

    SQL not a problem to me… thks for ur advice :)

  6. moo_t October 6, 2004 at 8:13 pm #

    Just don’t understand in what circumstances you want to reuse the Primary key.

    And for sorting, btree table is good enough for daily usage.

  7. Ray September 26, 2005 at 4:19 pm #

    I have the same problem…

    I want to do this because I will run out of records after a few months/years.

  8. Peter October 29, 2005 at 8:43 pm #

    Thanks a lot for writing this down and publishing it.

    I found you via Google with a search of
    mysql auto_increment reset
    and it was precisely the piece of information I needed.

    @moo_t – why reset primary key?
    In my NucleusCMS I would like to publish a new blog (the fourth one). The blogid is visible in the url as index.php?blogid=x.

    As I had setup new blogs and deleted them, my supposed blog nr. 4 had blogid=7. It may be a cosmetic change, but from a users point of view (and from mine too) it’s just not logical if blog nr. 4 has id nr. 7 ;-)

  9. Marvin February 3, 2006 at 4:26 am #

    Thanks ALOT! this helped me sooo much !

  10. Eugene May 23, 2006 at 7:35 am #

    Hey Liew, thanks for that piece of infomation, but i am looking at how to renumber the ID instead.

    Example, i have 7 records and i deleted ID 4 and ID 5, so i would like to renumber all the ID and so that the last auto number is 5.

  11. T2_T2 May 24, 2006 at 3:13 pm #

    If my primary key is Auto increament then what happens when it crosses its upper limit ?
    How to find the upper limit ?

  12. Bach January 24, 2007 at 5:46 am #

    To Eugene:

    To re number the ID instead you can do this magic trick:

    SET @var_name = 0;
    UPDATE Tablename SET ID = (@var_name := @var_name +1);

    You can change @var_name to start from 80, and your ID will be:
    81, 82,83, …

    then combine it with

    ALTER TABLE Tablename AUTO_INCREMENT = 6;

    So the next auto_increment will be 6 , and all your ID is 1,2,3,4,5.
    Hope this helps.

    Thanks.

  13. william January 26, 2007 at 7:10 pm #

    OMW… you just saved me having to re-enter like 400 records this afternoon. you’re the best Bach

  14. pablo February 10, 2007 at 9:16 am #

    “OMW” does that mean “Oh my Woz”? because Wozzie’s kinda like a god… to some.

    Seriously though,
    ALTER TABLE Tablename AUTO_INCREMENT = 6;
    Genius!

    Thanks so much.

  15. sandeep July 24, 2009 at 2:45 pm #

    SET @var_name = 0;
    UPDATE gmid SET ID = (@var_name := @var_name +1);

    Wow Bach You Are God Man i have a huge problem 100000 records and in first 500 i had a huge gap like after id30 next record starts from id 167 i run that and voilla my ids are starts again from 1 and all sorted man u make my day thank you genius !!

  16. anhtrue July 26, 2009 at 5:53 pm #

    thanks a lot! it is very helpful. I have the same problem with my database.

Add a comment