How to Reset MySQL Autoincrement

Follow this step by step guide to reset the auto-increment option from mysql. The reset helps to stream line the database as you can type in the number that was previously used.

Don’t forget to check out our site for more free how-to videos!
– our feed
– join us on Facebook
– our group in Google+


In this tutorial, we will show you how to reset the Auto increment field in MySQL.
Mysql Auto-increment allows a unique number to be generated when a new record is inserted into a table. The function allows you to automatically add 1 to the previous record if you enter a new value.

You can always reset Mysql auto increment so that when you delete a record your data does show an uneven picture.

Step 1 — Selecting records

Over here, we will be using the MySQL instance called SQL Yog.

Just consider this table having the names of animals and the serial number, in this example the Mysql auto increment function is in operation.

If we delete any of the record from the table, that particular ID value will not be associated with any other field. This is because the auto number field will not change its order according to the changes made in the table.

Step 2 — Selecting the column

To fix this problem, you can simply drop the column and make it once again.

For that, we will write the alter table query along with the table name and specify the id column with the drop keyword.

When we run the query and fetch all the records from the table, you will notice that the ID field has been deleted.

Now let’s type the Alter Table query and give the table name which in our case is animals. Next, we will include the ADD keyword along with the column name which is ID in this case. Next, we will specify the data type, which would be Medium Integer in this case and after that, let’s add the Not Null and Auto increment Constraints along with the Primary key.

With that done, let’s run the query and fetch all the records from the database.

With that done, you will notice that all Auto increment field has been reset, and all the ID’s are now in order.

And in this manner you can reset Mysql auto increment.


Xem thêm bài viết khác:

  • ❗ Subscribe To Our Main YouTube Channel

    Internet Services and Social Networks Tutorials from HowTech 04/28/2020 1:49 pm Reply
  • very good!!!

    BriggyT Ha 04/28/2020 1:49 pm Reply
  • Thanks to you a lot

    Erol Bircan 04/28/2020 1:49 pm Reply
  • Great video, best way to do it among millions. Simple, effective, quick. Simply one of the best. Thanks HowTech People. Great Job.

    Love&Peace 04/28/2020 1:49 pm Reply
  • It was very useful info, but it's suitable when youa have small amount of data. For example I have tried this with 5000000 records and it took 30 seconds to drop the id and to adding the id it took 2 min 14 sec, because it is doing first a copy to temp table and then create the table again, whole table!!!

    Ahmad Ordikhani 04/28/2020 1:49 pm Reply
  • How to validate the time and date?
    In data grid view using Mysql and visual basic.
    Example if I input Monday 7:00 a.m to 9:00 a.m
    then data save, if I input it again like Monday 7:30 a.m to 9;00 a.m the data do not save because the time of Monday is still running.

    Then if I input Tuesday same time then the data save.
    How I do that?

    Armil Sacil 04/28/2020 1:49 pm Reply
  • Just do: ALTER TABLE tabe_name AUTO_INCREMENT = 1;

    Ranieri Machado 04/28/2020 1:49 pm Reply
  • please share the way to reset in sql

    Qasim Saeed 04/28/2020 1:49 pm Reply
  • Awesome! Thanks man, it worked for me

    Bhongo Sinxo 04/28/2020 1:49 pm Reply
  • what if the id is primary key, how will you reset its auto-increment.

    Rio Garrell 04/28/2020 1:49 pm Reply
  • Can i run the same in php?

    Prayas Bhatnagar 04/28/2020 1:49 pm Reply

Leave a Reply

Your email address will not be published. Required fields are marked *