ID Shifting and Pushing in MySQL

tundra

Say I have 20 records, each record subsequently ID'd 01, 02, 03, 04 ... 20.

If I were to remove, say, record 15, what would be the easiest (or most practical) way of shifting all the IDs higher than 15, down one, so that 16 became 15 and so on.

On that same note, what if I wanted to replace 01 with a new record, but instead of just writing over 01, I wanted to Push records 01 and higher up one so that 01 became 02, 02 became 03, and so forth.

I have a few ideas, but they seem waaaay too complicated for something I imagine has been done time and time again. So I'd love to see if others have already tackled this one and I'm just wasting my time. =)

TIA

Sxooter

The short answer is that you SHOULD NOT RENUMBER AUTONUMBERED FIELDS EVER... NEVER EVER EVER.

The long answer is that you are trying to use an autoincrementing field as though it were a row number, and that is not it's job. The job of an autoincrementing field is to supply a unique identifier, period. When you build a relational database, an autoincrementing field may get used by a subordinate table to identify the record it relates to in the master table. On some setups with foreign keys, the database will silently delete the dependant rows when you renumber the paren table. Not a good thing.

If you need to know what row number you're on, there are other ways of doing it that work quite well. Use limit and offset or a cursor to do that.

thom

I agree on Sxooter's short answer.

Anyways, if you don't have the field as an auto_increment (well, this should work either way) you can try the following:

update table set id = id-1 where id > 15;

this shift id's of all rows with id > 15 down by 1.

same if you want to shift up:

update table set id = id+1;

but keep in mind that changing autonumbered fields is not smart. (you should've already got that part by now 🙂 )

bastien

then they are hell to screw with, playing with them can have all sorts of unintended results...personally i never allow deletion of anything from a live DB...i create an extra field called active and set its default to on (usually a checkbox in an admin form) and can turn that off when the item is no longer valid...

tundra

Well, this has been elightening, to say the least, thanks for all the responses. I've moved away from autonumbered fields to use as a sorter field, things are looking better now, thanks again. This has been driving me crazy! I knew a new set of eyes would help see things more clearly.

Kris_McCraw

I did a bad thing I've found out now. I altered the primary unique key on a record a few days ago
and now every time my php scripts try to insert a new row it is not inserting the correct auto increment value
that it should. I guess this would qualify as a unexpected result for goofing with it.
I will now heed the short answer advice NEVER.

But I have a problem

it should insert a value like 15674 and it is inserting the same value consistently 2147483647
I'm baffled by the same value being returned every time.

from what I understand now could this be an issue with the primary index for the table ?

If it is would it be best to drop the index and recreate it ?

Or maybe it may be best to copy the table , delete the old one and rename the new one ?

Any advice would be greatly appreciated.

abda53

you can always start your database over.. :oP

Kris_McCraw

maybe so but I'm sure there are other options that I don't know about.
Maybe you could elaborate on the purpose of this action ?
I don't understand why throwing away good functional tables would help with a problem with a single column
in 1 table.

Sxooter

Since sequences and such are NON ANSI standards, you'll have to look at how to set a sequence in your particular DBMS up in it's own docs./

On Postgresql, you use setval like so:

select setval('seqname',int);

And the nextvat selected will be int+1

If you're running MySQL look into it's docs, it should be there. I don't use it so I don't know.

bastien

and add it back in - it should autofill the numbers for the records you have and then the autoincrement in taken care for the next go around

one hint here is to just create a table with one value in it and add one to that value eveytime you add a new record, like autoincrement but with out the added fuss if ya gotta mess with it...

[deleted]

"it should insert a value like 15674 and it is inserting the same value consistently 2147483647
I'm baffled by the same value being returned every time. "

Looks like MySQL's datafiles are corrupted.

MySQL has a reputation of giving weird behaviour when it's datafiles become corrupted. Instead of letting you know that there is something wrong, it just starts f*cking up your data.

Try fixing them using myisamchk (read the mysql manual for more information)

Kris_McCraw

Yes I think this was the case that the table was corrupted. Using myisamchk did the trick :-) Thanks for the advice