How To Reset MySQL Autoincrement Column - MySQL Developer Tutorial

How To Reset MySQL Auto- increment Column

Auto Increment is feature in MySQL that enabled the column in the table to increment automatically. Once you create the column with Auto increment,the value starts with 1 and on each new insert it increases by 1 automatically.

Often we get into situation where we need to reset the value. There are different ways we can reset the value of Auto Increment.

1) Truncate Table : 

If you don't need the data in the table, then you can simple Truncate the table and it will reset the Auto Increment value for Column.  If you use Delete statement, that does not reset Auto Increment Column.

To truncate table , you can use below syntax.


Truncate table YourTableName;



2) Drop and Recreate Table :

If you really don't care about the data, you can simple script out the table definition, drop the table and recreate the table. that will reset the auto increment column value.


3) Reset the Auto Increment Directly :

You can set the Auto Increment value for the table directly. Remember that you can not reset the value lower than the max value which is already used in your auto increment table.



Let's say if I have a table customer and it has 5 rows. The last value for auto increment is 5. I can run the below statement, it will run fine but will not reset the value for Auto increment.


alter table customer auto_increment=1;


When I will insert the next row, the value will start with 6.
If I would like to reset the value higher than the max value in auto increment ( that is 5 in my case)  , I can do that.Let's say if I want to skip and start with 100. I can use below statement for customer.


alter table customer auto_increment=100;


Reset Auto Increment Column in MySQL Table - MySQL Developer Tutorial

9 comments:

  1. Department of Secondary Education, Sylhet Division also conducted an evaluation process to the valuation of subject wise marks through JSC Result 2022 Sylhet answer sheet correction and the process of evaluation will be complete in before 2nd week of December 2022 and the education board will update answer scripts with student wise mark sheets in subject wise with total GPA grade points to Secondary and Higher Secondary Education Board Bangladesh.

    ReplyDelete
  2. Thanks for sharing the detailed steps to reset the auto increment in MySQL. I am not good at programming and the same in databases. To avoid low grades in these subjects, I always prefer to hire cheap assignment writing services to complete my computer science assignments. It helps me focus on interesting subjects like data analysis and marketing.

    ReplyDelete
  3. I appreciate you providing in-depth instructions for resetting the MySQL auto-increment. I don't know much about databases or programming. I always prefer to hire inexpensive Assignment Writing Servicesto complete my computer science assignments to avoid low grades in these subjects. It assists me with zeroing in on fascinating subjects like information examination and showcasing.

    ReplyDelete
  4. Technology education plays a significant role in equipping individuals with essential skills for the modern world. It enables us to understand and harness the power of technology to solve complex problems and drive innovation. To delve deeper into this topic, a way buy management discussion board post online can provide valuable insights from diverse perspectives. Exploring the intersection of technology and education can help us navigate the ever-changing digital landscape and prepare future generations for success.

    ReplyDelete
  5. Thanks for sharing.I found a lot of interesting information here. A really good post, very thankful and hopeful that you will write many more posts like this one.Online thesis writer help

    ReplyDelete
  6. The letter of business is often used as a guide for potential dealings between people and organizations. We will be going through the business letter layout UK to get a fundamental peek at how to write a NEBOSH standards

    ReplyDelete
  7. "For students in the UK, navigating homework assignments can be challenging, and finding the 'Best homework writer in the UK' is paramount. Your commitment to delivering top-notch homework writing services is truly commendable. Best homework writer uk

    ReplyDelete
  8. Demystify the world of MySQL Autoincrement with a budget-friendly twist, thanks to the expertise of cheap ghost writers. Transforming technical details into an accessible and engaging narrative, these wordsmiths make database intricacies both informative and entertaining. Dive into the cost-effective realm of knowledge with the help of cheap ghost writers.

    ReplyDelete
  9. Hi I am so happy I found your blog, I truly found you unintentionally, while I was watching on google for something different, At any rate I'm here now and could very much prefer to express thank for a colossal post and an overall engaging site. Kindly keep up the extraordinary work. Giverny Residences

    ReplyDelete