更新排序索引列以移动项目 [英] Update a sorting index column to move items

问题描述

如果我有下表&data 允许我们使用 sort_index 进行排序:

If I have the following table & data to allow us to use the sort_index for sorting:

CREATE TABLE `foo` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `bar_id` INT(11) DEFAULT NULL,
  `sort_index` INT(11) DEFAULT NULL,
  PRIMARY KEY  (`id`)
);

INSERT INTO `foo` (`bar_id`, `sort_index`) VALUES
 (1,1),(1,2),(1,3),(1,4),
 (2,1),(2,2),(2,3),(2,4),(2,5);

我希望能够以最有效的方式执行以下操作:

I want to be able to do the following in the most efficient manner:

1. 将 foo 条目移动到给定位置(由 bar_id 限定)
   • 确保 sort_index 始终为 1 索引并且没有间隙
   • 您应该能够将项目移动到列表的开头和结尾，并且仍应应用规则 #2
   • 它应该完全在查询中完成并且尽可能少(因为集合可能非常大并且循环遍历它们执行单独的 UPDATEs 并不理想)

1. Move a foo entry to a given position (scoped by the bar_id)
   • Ensure that the sort_index is always 1 indexed and has no gaps
   • You should be able to move items to the beginning and end of the list and rule #2 should still be applied
   • It should be done entirely in queries and as few as possible (as the sets could be very large and looping over them doing individual UPDATEs is not ideal)

为了澄清我想要做什么，让我们假设表格是空的，所以我们有以下数据:

To clarify what I'm trying to do, lets assume the table was empty so we have the following data:

id | bar_id | sort_index
1  | 1      | 1
2  | 1      | 2
3  | 1      | 3
4  | 1      | 4    
5  | 2      | 1
6  | 2      | 2
7  | 2      | 3
8  | 2      | 4
9  | 2      | 5

那么如果我们要进行以下动作

Then if we were to do the following moves

• Foo 1 到 sort_index 3
• Foo 7 到 sort_index 1
• Foo 5 到 sort_index 5

我们应该得到以下数据:

We should get the following data:

id | bar_id | sort_index
1  | 1      | 3
2  | 1      | 1
3  | 1      | 2
4  | 1      | 4    
5  | 2      | 5
6  | 2      | 2
7  | 2      | 1
8  | 2      | 3
9  | 2      | 4

和 SELECT * FROM foo ORDER BY bar_id, sort_index; 给我们:

id | bar_id | sort_index
2  | 1      | 1
3  | 1      | 2
1  | 1      | 3
4  | 1      | 4  
7  | 2      | 1
6  | 2      | 2
8  | 2      | 3
9  | 2      | 4
5  | 2      | 5

推荐答案

您应该能够在单个查询中完成此操作:类似于 UPDATE foo SET sort_index = sort_index + 1 WHERE bar_id == bAND sort_index ，其中b是要移动的行的bar_id，s1是当前<该行的 code>sort_index，而 s2 是您要将其移动到的 sort_index.然后，您只需更改行的 sort_index.

You should be able to do this in a single query: something along the lines of UPDATE foo SET sort_index = sort_index + 1 WHERE bar_id == b AND sort_index < s1 AND sort_index >= s2, where b is the bar_id of the row to be moved, s1 is the current sort_index of that row, and s2 is the the sort_index you want to move it to. Then, you'd just change the sort_index of the row.

您可能希望在事务中执行这两个查询.此外，如果您使用类似 CREATE INDEX foo_index ON foo (sort_index) 在 sort_index 上创建索引，它可能会加快速度.

You'd probably want to do the two queries inside a transaction. Also, it might speed things up if you created an index on the sort_index using something like CREATE INDEX foo_index ON foo (sort_index).

(顺便说一下，这里我假设您不希望在给定的 bar_id 中出现重复的 sort_index 值，并且行的相对顺序不应该除非明确更改.如果您不需要这个，解决方案就更简单了.)

(By the way, here I'm assuming that you don't want duplicate sort_index values within a given bar_id, and that the relative order of rows should never be changed except explicitly. If you don't need this, the solution is even simpler.)

这篇关于更新排序索引列以移动项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！