Autoincrement problem [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

Problem with autoincrememnted “id” column

My db table looks like this pic. http://prntscr.com/22z1n Recently i've created de开发者_开发百科lete.php page. it works properly but when i deleted 21st user next registered user gets 24th id instead of 21. Is it possible to put newly registered users info to first empty row? (In this situation 21st row)

In my registration form, newly registering user can write names of existing users, and be friends with them after registration. For this friendship i have another table that associates id of newly registered user and existing user.For this purpose i'm using mysql_insert_id during registration to get id for new user. But after deletion of 21st row during next registration process mysql_insert_id gave me number 21. but stored in 24th row. And put to associations table 21 for new user. I wanna solve this problem

---

You received the answer the last time you posted this question. MySQL maintains an internal counter that is incremented every time a new row is inserted into a table with an auto-increment column. The increment value does not go down when a row is deleted.

To make things work the way you want, you will need to avoid using MySQL autoincrement, and implement your own solution to create and increment IDs.

---

mysql_insert_id() is a very very bad approach to get your id. You can always set the auto_increment value of your table, but I wouldn't suggest it. You should always do a SELECT of the latest record you insert based on some unique values, and order it by id DESC while limiting it to one result. Don't ever use mysql_insert_id().

---

It makes no difference - mysql_insert_id will return the autoincremented ID of the last inserted row. If the row gets ID 24, mysql_insert_id will return 24.

However, you can change the next value autoincrement will result it. Read up on it here.

---

DROP the field you are auto_incrementing ALTER the table to ADD the field again with the same attributes

All existing rows are renumbered and the next auto_increment number will be equal to the row count plus 1

WARNING:

There really is no reason you would need to do this or should do this! If you have references to these ids anywhere, which I know you do from earlier questions - this can break every single link.