MySQL: Reset AUTO_INCREMENT value of column
Question by Guest | 2014-06-05 at 15:22
Unfortunately, a little mishap happened to my MySQL database. Mistakenly, I have added about 10,000 data records to the table which I had to delete afterwards.
Sadly, among other things, this action results in an extreme high AUTO_INCREMENT value of the appropriate table so that new data records now gets an ID of five digits although there are altogether only about 100 data sets in the table.
With pleasure I would like to reset the AUTO_INCREMENT value of the table so that new records will have a smaller ID. Is possible in any way?
Related Topics
MySQL: Add new Column to a Table
Tutorial | 0 Comments
MySQL: Write current Date or Time into Column
Tutorial | 0 Comments
MySQL: Delete Data from Table - Difference between TRUNCATE, DELETE and DROP
Tutorial | 0 Comments
MySQL: 3 Ways of showing Table Definition and Structure
Info | 0 Comments
MySQL/PHP: How to retrieve the last INSERT ID
Info | 0 Comments
Quicksort: Sort two Columns
Question | 1 Answer
MySQL: Search and Replace directly in MySQL database
Tip | 1 Comment
Important Note
Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.
Participate
Ask your own question or write your own article on askingbox.com. That’s how it’s done.
You can reset the AUTO_INCREMENT value using the following command:
When trying to set a value smaller than the highest value that is already in use in the appropriate column, that is no problem in MyISAM tables, because MyISAM simply takes the lowest value possible for the column. For example in the case that the highest value is 100 and you are using the command "ALTER TABLE tab AUTO_INCREMENT = 10", the result would be that the AUTO_INCREMENT value of the column is set to 101 which is the lowest possible value here. So, using "AUTO_INCREMENT = 1" would always result in setting up the lowest possible value for a MyISAM table.
With InnoDB this is not possible. Here it is not allowed to set the counter to a value lower than in use.
Please note: using this statement, your whole table is re-created. With high amounts of data, this can cost a lot of time. But in your case, when having only 100 records in the table, this should not be a problem.
2014-06-05 at 16:51