I'm looking to add a sort INT UNSIGNED field to my table so I can sort the items depending on their order without touching their id. Is it possible to give this column a value much like auto_increment but instead of incrementing by 1, MySQL increments by 10 or 20 or even 30? The large gaps allow me to shuffle their order.
I may be thinking about this the wrong way so please tell me what you think.
In fact, there is absolutely no need in "large gaps".
You can always swap 2 numbers - that's enough for the user-defined sorting.
Just update your sorting field to the same value with id initially and then move it to whatever position.
sort values then. The default of which would be their id.You can do that by changing auto_increment_increment:
SET @@auto_increment_increment=10;
There is one issue: typically, for each table there can be only one column with auto_increment - if you have an id column that's also auto_increment - which really have much better reason to be auto_increment, this whole idea won't cut.
Secondly, it probably does not make much sense any way, since there is no reason for the latest inserted row (by default) to have the highest sorting order.
appart from setting the autoincrement to 10 like suggested you should also consider make it a unique key to speed up sorting.
