Create ordering in a MySQL table without using a number (because then it's hard to put something in between)

I have a long list of items (say, a few million items) in a mysql table, let's call it mytable and it has the field mytable.itemid.

The items are given an order, and can be re=ordered by the user by drag and drop. If I add a field called mytable.order and just put numbers in them, it creates problems: what if I want to move an item between 2 other items? Then all the order fields have to be updated? That seems like a nightmare.

Is there a (scalable) way to add orde开发者_StackOverflowr to a table that is different from just giving every item a number, order by that, and do loads of SQL queries everytime the order is changed?

You can either create a trigger or stored procedure to resequence the order values appropriately, or drop all the associated records and insert new ones in the new order.

You can use fractions (two integers, i.e. a/b). Always there is at least one rational between two other.