reorder auto-increment mysql column

[nonis]

I am creating an image blog software. As of right now the table for entries has the following columns:

id, userid, title, date, imageurl, caption, alttext

and entries are displayed by the id. So for now they go in order they are added to the database.

My question is: Can you modify an auto-increment mysql column? Say the user wants to add something between entries with id 2 and 3. Can I move 3 down to 4 and squeeze one in? Or should I add a new column for display order, which will not be auto-incrementing?

 

[Sgraffite]

I would do an order_id column that just stores the display order of the items.

Shifting the auto increment id would screw things up if any other tables reference those ids, as well as wasting the resources to actually change the data.

 

[nonis]

Yeah, that's what I was thinking. And just add one to every order_id after it... I guess that's pretty elegant.

 

[mikeblas]

Why do you want to change them?

 

[ann0yanc3]

use an UNSIGNED BIGINT field for your auto incrementing PRIMARY KEY and don't worry about IDs anymore

 

[mikeblas]

LOL, if only it were so easy!

 

[PrincessFrosty]

If you want the ability for users to re-order the items in the list I wouldn't use what is presumably your key field, i always leave my unique key field just for that purpose alone, to uniquely identify each record.

Instead I'd add a new field specifically for order, something like an int with the name orderid and fill that with a position number when a new record is added.

It's fairly easy to make a space for a new record in the middle of the order list, you need it just add 1 to all orderids that are greater than the position you wnat to insert in, then add that orderid.

I dunno if theres an easier way of doing this?

 

[mikeblas]

It's fairly easy to make a space for a new record in the middle of the order list, you need it just add 1 to all orderids that are greater than the position you wnat to insert in, then add that orderid.

That works, neglecting any foreign key references, caching, and other records persisted outside of the database.

 

[keenan]

Might gain some efficiency for the general case by spacing the orderids by say 100, then moves can often be done by only modifying one row instead of 50% of rows (on average), and the worst case is no worse than the naive approach.

Make sure to index your orderid column.

 

[PrincessFrosty]

That works, neglecting any foreign key references, caching, and other records persisted outside of the database.

Sure if you do any of that stuff you need to make sure you take it into account.

 

[Pwyl_The_Destroyer]

I was bored for a few minutes so I tinkered with some code...
(comes with no warranties or guarantees, just a place to start)
Assumes you add a column used only for sorting called sort_order

/*insert after @insertafterid*/
declare @insertafterid int
set @insertafterid = 13/*replace from input*/
declare @insertafter int
set @insertafter = (select sort_order from so_test where id = @insertafterid)
update so_test
set
sort_order = sort_order + 1
where
sort_order > @insertafter

insert into so_test
values
(@insertafter +1, 'inserted')
/*endsub*/

/*add to end*/
declare @max_so int
set @max_so = (isnull((select max(sort_order) from so_test),0) +1)/*needed a null set case*/
insert into so_test
values
(@max_so,'added to end')
/*endsub*/

/*move record @idtochange to right after @insertafter*/
declare @idtochange int
set @idtochange = 12/*replace from input*/
declare @insertafterid int
set @insertafterid = 17/*replace from input*/
declare @insertafter int
set @insertafter = (select sort_order from so_test where id = @insertafterid)
declare @max_so int
set @max_so = (isnull((select max(sort_order) from so_test),0) +1)/*need a null set case*/

update so_test
set
sort_order = @max_so
where id = @idtochange

update so_test
set
sort_order = sort_order + 1
where
sort_order > @insertafter

update so_test
set sort_order = @insertafter +1
where id = @idtochange
/*endsub*/

 

[berky]

so I only have limited experience with mysql (i've messed with it a bit for some forums and another project that was scrapped), but in my experience, you only create an ID field to make a unique primary key so you can reference it in other tables. if you want to make a unique ID in a certain format that you can change at will, make a new field. you should NEVER change the primary key once it's been defined, which is another reason to use an auto-incrementing id field for the PK. the user should never see that field and using an arbitrary value allows you to change other values without screwing up all your foreign key references.