In my database I first filled with 50 record of dummy data just to check if my site is working properly.Then I deleted the unwanted records and in place of the I added new ones and now I have 20 records in it.When I register a user from site data goes to the database.But now everytime a new user is registered to the site my ID field which is a PRIMARY KEY and Auto Increment,shows the new user's ID as 51 and not 21.Next user 52 not 22.Why is this and how to make the next Id be 23.
Asked
Active
Viewed 69 times
3 Answers
0
You have to reset you increment:
try ALTER TABLE Table_name AUTO_INCREMENT = 21; if your next number is 21
Liam Sorsby
- 2,912
- 3
- 28
- 51
-
see the following manual: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html – Liam Sorsby Jul 29 '13 at 15:43
-
If you are using a mysql control panel like php my admin you can reset the field as follows: http://stackoverflow.com/questions/3019698/reset-id-autoincrement-phpmyadmin (see the second answer) – Liam Sorsby Jul 29 '13 at 15:46
0
auto increment is a counter in the table definition, not just "the next available number", so when you added your 50 records, you set the counter to 51, and when you delete them, the counter stays at 51 until you reset it or add more records.
You can reset the counter using: How to reset AUTO_INCREMENT in MySQL?
0
auto increment fields work independently of the "count" of data in the table, it keeps increasing with new records but do not decrease on deletions.
You need to reset your field manually.
Tamim Al Manaseer
- 3,554
- 3
- 24
- 33