mysql中列数据的重新排序 [英] Reordering of column data in mysql
问题描述
我有一张这样的桌子:
categoryID categoryName
----------------------------
1 A
2 B
3 C
现在，我希望用户能够根据他的意愿订购此数据.我想记住他对未来的偏爱.所以我想我将在上表中添加列order并将其设置为INT和AUTO_INCREMENT类型.所以现在我得到一个这样的表:
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
我的问题是-用户现在决定将订单4的categoryName(在上面的示例中为D)提高到2(在上面的示例中为B之上)，以使表格如下所示:
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
我的问题是-发生重新排序时，应该如何为order列分配新值.有没有一种方法可以在不更新表中所有行的情况下执行此操作?
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?
想到的一种方法是，将列设为FLOAT，如果我想将其置于1,2顺序的列之间，则将其赋予1.5的顺序.在这种情况下，我在重新订购商品时会一直失去精度.
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.
另一方法是更新(m，n)之间的所有行，其中m，n分别是源顺序和目标顺序.但这意味着运行(m-n)个单独的查询不是吗?
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?
假设我采用FLOAT方法，我想出了这个sql来计算需要在id = 2的项目之后插入的项目的订单值(例如).
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;
这给了我3.5这是我想要达到的目标.
This gives me 3.5 which is what I wanted to achieve.
有没有更好的方法来写这个?请注意，select order as targetOrder from categories where categoryID=9被执行了两次.
Is there a better way to write this? Notice that select order as targetOrder from categories where categoryID=9 is executed twice.
推荐答案
如果更改的数量很小，并且您知道所涉及项目的ID，则可以生成笨拙但高效的UPDATE语句:
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;
或(我不太喜欢):
UPDATE categories
SET `order` = ELT (FIND_IN_SET (categoryID, '2,3,4'),
3, 4, 2)
WHERE categoryID in (2,3,4);
UPD :
假设您知道类别(或其名称)的当前ID，其旧位置和新位置，则可以使用以下查询将类别向下移动到列表中(向上移动时，您必须更改between条件和new_rank计算到rank+1):
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;
这篇关于mysql中列数据的重新排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
