mysql 数据重新排序吗,mysql中列数据的重新排序

I have a table like so:

categoryID categoryName

----------------------------

1 A

2 B

3 C

Now I want the user to be able to order this data according to his will. I want to remember his preferred order for future. So I thought I'd add a column order to the table above and make it of type INT and AUTO_INCREMENT. So now I get a table like this:

categoryID categoryName order

-------------------------------------

1 A 1

2 B 2

3 C 3

4 D 4

My problem is - the user now decides, to bring categoryName with order 4 (D in example above) up to 2 (above B in example above) such that the table would now look like:

categoryID categoryName order

-------------------------------------

1 A 1

2 B 3

3 C 4

4 D 2

My question is - How should I go about assigning new values to the order column when a reordering happens. Is there a way to do this without updating all rows in the table?

One approach that comes to mind is to make the column a FLOAT and give it an order of 1.5 if I want to bring it between columns with order 1,2. In this case I keep loosing precision as I reorder items.

EDIT:

Another is to update all rows between (m, n) where m, n are the source and destination orders respectively. But this would mean running (m-n) separate queries wouldn't it?

Edit 2:

Assuming I take the FLOAT approach, I came up with this sql to compute the order value for an item that needs to be inserted after item with id = 2 (for example).

select ((

select `order` as nextHighestOrder

from `categories`

where `order` > (

select `order` as targetOrder

from `categories`

where `categoryID`=2)

limit 1) + (

select `order` as targetOrder

from `categories`

where `categoryID`=2)) / 2;

This gives me 3.5 which is what I wanted to achieve.

Is there a better way to write this? Notice that select order as targetOrder from categories where categoryID=9 is executed twice.

解决方案

If the number of changes is rather small you can generate a clumsy but rather efficient UPDATE statement if the you know the ids of the involved items:

UPDATE categories

JOIN (

SELECT 2 as categoryID, 3 as new_order

UNION ALL

SELECT 3 as categoryID, 4 as new_order

UNION ALL

SELECT 4 as categoryID, 2 as new_order) orders

USING (categoryId)

SET `order` = new_order;

or (which I like less):

UPDATE categories

SET `order` = ELT (FIND_IN_SET (categoryID, '2,3,4'),

3, 4, 2)

WHERE categoryID in (2,3,4);

UPD:

Assuming that you know the current id of the category (or its name), its old position, and its new position you can use the following query for moving a category down the list (for moving up you will have to change the between condition and new_rank computation to rank+1):

SET @id:=2, @cur_rank:=2, @new_rank:=4;

UPDATE t1

JOIN (

SELECT categoryID, (rank - 1) as new_rank

FROM t1

WHERE rank between @cur_rank + 1 AND @new_rank

UNION ALL

SELECT @id as categoryID, @new_rank as new_rank

) as r

USING (categoryID)

SET rank = new_rank;