MySQL auto-increment 'skipping' numbers after entry removal?
Posted: Thu Nov 27, 2008 10:41 am
Hello, this is my first post here and if I leave something out or am not clear just let me know.
I am inquiring as to what can be done, if anything, to get the auto-increment 'extra' function to maintain a no-skip order if entries are removed.
Example:
I have a table called "accounts" where field name 'id' is set to 'auto-increment' as follows:
uname | email | id
User1 | email@net.com | 1
User2 | email2@net.com | 2
User3 | email3@net.com | 3
If I remove the row where uname='User2', the 'id' field for the second row remains set to 3, instead of dropping back to 2. Likewise, the next entry added is given id of 4, as if there were 4 rows, but there are actually 3 because one was removed. (I'm sure most people reading this know how auto-increment works, I'm just not too familiar with it).
I would like to be able to remove a row in a table and have the 'id' field not 'skip numbers', so to speak. (Don't want results like id = 1,2,3,4,6,7,8,12,13,14).
Should I be doing this using a php script to assign id's based on the row count (like mysql_numrows) and not auto-increment?
This question could probably have been worded better, so if it isn't clear just let me know.
Thanks in advance for any who take the time to read this.
I am inquiring as to what can be done, if anything, to get the auto-increment 'extra' function to maintain a no-skip order if entries are removed.
Example:
I have a table called "accounts" where field name 'id' is set to 'auto-increment' as follows:
uname | email | id
User1 | email@net.com | 1
User2 | email2@net.com | 2
User3 | email3@net.com | 3
If I remove the row where uname='User2', the 'id' field for the second row remains set to 3, instead of dropping back to 2. Likewise, the next entry added is given id of 4, as if there were 4 rows, but there are actually 3 because one was removed. (I'm sure most people reading this know how auto-increment works, I'm just not too familiar with it).
I would like to be able to remove a row in a table and have the 'id' field not 'skip numbers', so to speak. (Don't want results like id = 1,2,3,4,6,7,8,12,13,14).
Should I be doing this using a php script to assign id's based on the row count (like mysql_numrows) and not auto-increment?
This question could probably have been worded better, so if it isn't clear just let me know.
Thanks in advance for any who take the time to read this.