Page 1 of 1

MySQL auto-increment 'skipping' numbers after entry removal?

Posted: Thu Nov 27, 2008 10:41 am
by recon
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.

Re: MySQL auto-increment 'skipping' numbers after entry removal?

Posted: Thu Nov 27, 2008 10:52 am
by Syntac
I believe that's the standard behavior. Let's use an example to see why it's a good thing:

You have two tables. One is called "users" ("id" int auto_increment), and the other is called "profiles" ("id" int auto_increment, "user_id" int). The "user_id" field of "profiles" links a given profile to its user.

Now, suppose auto_increment doesn't skip removed entries. If you remove a user but not his profile, that profile will be assigned to the next user you add. Depending on what it contains, the results could be disastrous.

This isn't the best example out there, but you get the idea. Why do you want to reassign ID numbers, anyway?

Re: MySQL auto-increment 'skipping' numbers after entry removal?

Posted: Thu Nov 27, 2008 10:59 am
by recon
Ahh Okay, I think I see why it behaves like this now, makes sense. Thanks.

My ultimate goal is to be able to re-order row data via a php script (like, "Click to move up" "Click to move down"), such that the data in a row can be moved below the data in the row below it. Is this not a good idea?

Re: MySQL auto-increment 'skipping' numbers after entry removal?

Posted: Thu Nov 27, 2008 11:08 am
by mintedjo
I don't get it :-)

Re: MySQL auto-increment 'skipping' numbers after entry removal?

Posted: Thu Nov 27, 2008 11:23 am
by recon
My fault for poor wording, in that case.

I want to be able to use some kind of "move up" and "move down" feature to re-order the data in my database.

Suppose you have a list of "stuff" pulled from a datatable in mysql:

1. boxes
2. cars
3. people
4. trains


I want to make "trains" the third item on the list, and thus make "people" the fourth item on the list, resulting in the list looking like this:

1. boxes
2. cars
3. trains
4. people


Can this be done via php script?

Re: MySQL auto-increment 'skipping' numbers after entry removal?

Posted: Thu Nov 27, 2008 11:26 am
by Eran
The order value should be kept in a separate column. As syntac explained, the auto-incrementing id column is used for integrity and should not be changed.

Re: MySQL auto-increment 'skipping' numbers after entry removal?

Posted: Thu Nov 27, 2008 11:34 am
by recon
So I should make a seperate column in the table that the php script manipulates the values of to get a list order?

Re: MySQL auto-increment 'skipping' numbers after entry removal?

Posted: Thu Nov 27, 2008 11:41 am
by Eran
Correct. Should be an integer column, probably indexed if you want to sort by it.

Re: MySQL auto-increment 'skipping' numbers after entry removal?

Posted: Thu Nov 27, 2008 11:42 am
by recon
I see. Thanks for the reply. Will try to do this...