我应该如何在数据库中存储排序后的项目？

Question

在我的应用程序中，用户可以按照任意顺序重新排列他们喜欢的书籍。

在我的数据库中有一个名为“books”的表格，每本书都有一行数据。目前，有一个整数列名为“position”，它存储每本书的位置：第一本书为1，接下来的依次类推。

问题在于，如果有人将一本书从第11000个位置拖到第1个位置，那么我就必须对数据库进行11000次更新。这似乎效率不高。有没有更好的方法来处理这个问题？

我想过一个想法，就是只需创建另一个名为“book_sort_orderings”之类的表格，并为每个用户创建一行数据。其中一列将是一个巨大的文本列，存储书籍id的排序列表。然后，当用户重新排列书籍时，我可以将这个值提取出来到我的代码中，在那里执行重新排列并更新数据库行。当然，每次添加或删除书籍时，我也必须更新此数组。这是正确的做法吗？或者有没有聪明的方法可以在不改变当前设置的情况下加快速度？

- NudeCanalTroll

---

你是说在你描述的那个例子中，你会发送11,000个SQL UPDATE语句到你的数据库吗？因为有更有效的方法来更新表中的多行数据。 - Brian Willis

啊，那个“解决方案”越来越糟了 - 保持数据规范化！无论如何，我会 1）将其设定为每个用户，并且2）研究一种“层次结构”数据类型（类似于 SQL Server 中使用的内容），它可以在近乎无限的情况下维护一个层次结构（这里是指排序）；它基于“分割”节点工作，就像浮点数总是有一个比“稍大”和“稍小”的数字。 - user166390

Answer 1

您会惊讶于一个不错的DBMS以“批量”方式更新11,000行的速度有多快（而不是为每一行进行单独的数据库往返）。

但如果您想避免这种情况，请使用旧的BASIC技巧（自BASIC还有行号的时代起）：留空！ 不要使用位置：1、2、3、4、5等...，而要使用10、20、30、40、50等...。

因此，当您需要将（例如）第一个项目移动到倒数第二个位置时，只需将10修改为41，您就会得到：20、30、40、41、50等...。显然，如果填充了一个间隙，您需要进行一些调整，但这种策略应该能够减少大规模的更新。

---

另一个可能性是实现双向链接列表：不保存顺序，而是保存前一个和下一个项的ID。重排序可以通过简单地“重新链接”ID来完成，就像在内存列表中一样。不幸的是，这也会阻止DBMS直接对项目进行排序（至少没有笨拙且可能效率低下的递归查询）-您必须在应用程序级别进行排序，因此我建议不要这样做。

---

并且一列将是一个存储书籍ID排序列表的大型文本列。

请不要这样做。您将违反第一范式（1NF），而且有极好的理由不要这样做，包括数据一致性和性能（您必须为任何单个更改重写整个字段）。

- Branko Dimitrijevic

Answer 2

你当前的解决方案似乎不能适用于多用户设置。如果书籍顺序在Book表中设置，那么对于所有用户来说它不是永久的吗？

正如其他人所提到的，通常最好保持数据规范化，这将要求您添加另一个类似于您建议的新表。因此，您可以有一个新的BookOrdering表。它将具有一个book_id，一个user_id和一个position列。这样，对于每个用户和每本书都有一个分配的位置。

因此，会有一个默认的排序（不会存储在此表中），但用户将有能力更改顺序。该表仅记录与默认值不同的更改。当您想要加载用户的图书时，您首先需要检查此表以获取特定的user_id，然后相应地移动/调整顺序。

- joofsh

Answer 3

更新你示例中所有那些行并不是很难，只需要几个SQL语句即可。你不需要向DBMS（我假设这就是你想表达的）发起11000次更新。

首先，更新所有需要向前移动一个位置的书籍：

UPDATE book
SET position = position + 1
WHERE position < 11000
AND position >= 1

然后设置您正在移动的书籍的位置：

UPDATE book
SET position = 1
WHERE id = whatever

- Brian Willis