milly
7:08 pm on February 1, 2010 |
Answers: 1
Tags: auto (13), database (51), increment, mysql (35), reset (5) 
(3 votes)
Loading ...How to Reset Auto Increment in MySQL
I have a database table with a auto increment column for primary key. Over time the rows being add and delete many times, and the auto increment value became scattered. If you want to reset the auto increment counter, follow the instruction below.
Unable to Connect to MySQL Database Via PHPMyadmin After Changing PasswordIn SQL, how can you select and match more than one column in a sub-select?
Mysql Database Backup and Restore Commands1and1 Hosting PHPMyAdmin MySQL Database Import Issue
Two Simple Commands to Export and Import (backup and Restore) mysql Database
milly 7:09 pm on February 1, 2010
Scenario 1:
Essentially, this alter statement will reset the next auto increment value to the existing largest value in the auto increment column + 1If you have 100 rows, and deleted the 100th row. The next auto increment value is supposed to be 101. If you want the next auto increment value to be 100 instead, you can do the following.
Scenario 2:
If you have 100 rows, and deleted the 20th row. The next auto increment value is supposed to be 101. However, if you want your next insert to be inserted as #20, you can use the follow “set” command immediately before your INSERT statement.
SET insert_id = 20;Scenario 3:
At last, if you simply want to wipe out the data then reset the auto increment back to start at 1, simply run:
TRUNCATE TABLE tablename;This will delete all the data and reset Auto Increment back to 1