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.

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.

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.

Share this:
  • spoonfork

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

  • LcF

    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.

  • tkh

    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..

  • LcF

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

  • tkh

    SQL not a problem to me… thks for ur advice 🙂

  • moo_t

    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.

  • Ray

    I have the same problem…

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

  • 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 😉

  • Marvin

    Thanks ALOT! this helped me sooo much !

  • Eugene

    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.

  • T2_T2

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

  • Bach

    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


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


  • william

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

  • pablo

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

    Seriously though,

    Thanks so much.

  • sandeep

    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 !!

  • anhtrue

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