tip: reset auto_increment number in MySQL databases
Have you ever deleted rows in your MySQL database and created gaps between your highest auto_increment field and the next auto_increment value? Here’s an example, say “user_id” is an auto_incremented field:
| user_id | first_name |
|---|---|
| 1 | John |
| 2 | Sarah |
| 3 | Malcolm |
| 4 | Steven |
| 5 | Elisabeth |
After entering Elisabeth as the fifth row, the next auto_increment number is 6. The problem shows up if you delete Elisabeth — the next auto_increment number is still 6! Even if you delete all of them, the next auto_increment number is still 6! How frustrating!
Fortunately, you can reset the auto_increment number easily with a simple SQL statement:
ALTER TABLE tablename AUTO_INCREMENT = 1
Your auto_increment number will be reset to the highest auto_increment data value + 1.