Advanced Search

reset auto-increment in mysql

/ Published in: MySQL
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. Scenario 1:
  2. 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.
  3. ALTER TABLE tablename AUTO_INCREMENT = 1
  4. -- change "tablename" to the name of your table.
  5. Essentially, this alter statement will reset the next auto increment value to the existing largest value in the auto increment column + 1
  6. Scenario 2:
  7. 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.
  8. SET insert_id = 20;
  9.  
  10. Scenario 3:
  11. At last, if you simply want to wipe out the data then reset the auto increment back to start at 1, simply run:
  12. TRUNCATE TABLE tablename;
  13. This will delete all the data and reset Auto Increment back to 1

URL: http://www.dbuggr.com/milly/reset-auto-increment-mysql/

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.