Advanced Search

Return to Snippet

Revision: 42385
at March 4, 2011 05:38 by kidmizere
Initial Code 
Scenario 1:
If 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.
ALTER TABLE tablename AUTO_INCREMENT = 1
-- change "tablename" to the name of your table.
Essentially, this alter statement will reset the next auto increment value to the existing largest value in the auto increment column + 1
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
Initial URL 
http://www.dbuggr.com/milly/reset-auto-increment-mysql/
Initial Description 

                                

                                                            

                                    

                                        

                                        Initial Title
                                        

                                    

                                    
reset auto-increment in mysql

                                

                                                            

                                    

                                        

                                        Initial Tags
                                        

                                    

                                    

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
MySQL