SQLite :如何对更新查询进行分组并延迟之后的更改？

Question

上下文:

我有一个SQLite数据库，其表如下所示：

CREATE TABLE items (item TEXT, position INTEGER)

此表包含数百万项。对列position进行索引。

我的前端偶尔构建一堆更新以应用于position列。要更新的行不是由其id标识的，因为更新可以在范围很广的项目上进行，并且列出所有相关的id可能非常昂贵。例如，可以更新一些操作，如"add +10 to position >= 500和position <= 10000“-”Ad-3 to position >= 100000和position <= 100003“等等。

问题:一堆更新的条件位置是，所有的都是基于绑定更新之前的位置值的。如果我按顺序执行集群更新的每个更新操作，那么在其中一个更新之后可能会(实际上也会)出现一个“重叠”问题。

举个例子：

item | position
it1  | 1
it2  | 2
it3  | 3
it4  | 4
it5  | 5
it6  | 6
it7  | 7

如果我有以下这些更新：“添加+2到位置>= 5和位置<= 6”-“add-2 to >= 3和<= 4”，并将其转换为以下SQLite查询：

UPDATE items SET position=position-2 WHERE position >= 5 AND position <= 6

UPDATE items SET position=position+2 WHERE position >= 3 AND position <= 4

我会得到这样的结果：

 it1 | 1
 it2 | 2
 it3 | 5
 it4 | 6
 it5 | 5
 it6 | 6
 it7 | 7

不是我想要的，而是说：

 it1 | 1          {  it1 | 1  }
 it2 | 2          {  it2 | 2  }
 it3 | 5          {  it5 | 3  }
 it4 | 6    ===>  {  it6 | 4  }
 it5 | 3          {  it3 | 5  }
 it6 | 4          {  it4 | 6  }
 it7 | 7          {  it7 | 7  }

这是因为从一个操作到另一个操作的“重叠”。

我的第一个想法是用这样的用例：

UPDATE items SET position=CASE WHEN position >= 5 AND position <= 6 THEN position-2 WHEN position >= 3 AND position <= 4 THEN position+2 ELSE position END

此解决方案工作良好，但非常慢，因为SQLite似乎在我表中的数百万项上执行此操作，即使主要方不关心更新。

所以我就这样修改：

UPDATE items SET position=CASE WHEN position >= 5 AND position <= 6 THEN position-2 WHEN position >= 3 AND position <= 4 THEN position+2 ELSE position END WHERE (position >= 5 AND position <= 6) OR (position >= 3 AND position <= 4)

这个解决方案工作得很好，而且非常快速，因为SQLite只在相关的行上执行更新。

的问题:如果我可以在一组更新中执行10，甚至100个更新操作，那么查询就会变得非常大，这看起来不太.“漂亮”/“好”。

你认为有更好的方法来处理这件事吗？SQLite能够通过第一次选择受影响的行来执行一些“更新”查询，然后再有效地更新行吗？

想法？想过吗？

非常感谢！

Answer 1

欢迎来到这里。

我有一种感觉，有一种替代的解决方案，但对于你的问题，我会改变你的模式：

CREATE TABLE items (item TEXT, position INTEGER, position_tmp INTEGER)

像这样运行您的更新：

UPDATE items SET position_tmp = position + 1 WHERE 100 <= position AND position < 200
UPDATE items SET position_tmp = position - 3 WHERE 500 <= position AND position < 1000
...

最后，

UPDATE items SET position = position_tmp

永久地拥有额外的position_tmp列(它是这样的)似乎令人不快，但是SQLite不支持重命名或删除列，即使它支持，保留它也是更好的性能。

编辑：

我相信我现在明白你为什么要这么做了。您有数以百万计的项目需要强制执行订单。如果从列表中间删除一个，则必须调整其余部分以反映新的顺序。

根据您的需要，可以使用浮点数对它们进行排序。例如，0.1、2.5、5.0。如果要插入某项内容作为第二个元素，请插入位置1.3。如果要删除最后一个元素，只需删除它。

否则，您基本上是在处理一个由来已久的问题，即具有快速插入和删除列表的问题。

Answer 2

你必须在算法上看到你的问题：

将A添加到从P1位置到P2的项中

positions in [P1, P2] will become [P1+A, P2+A]
positions in [P2+1, P2+A] will become [P1, P1+A-1] (shift: -(P2-P1+1)=P1-P2-1)

SQL：

UPDATE items SET position=CASE position <= P2 THEN position+A ELSE position-P1-P2-1 WHERE position BETWEEN P1 AND P2+A;

将S从Q1位置减为Q2

positions in [Q1, Q2] will become [Q1-S, Q2-S]
positions in [Q1-S, Q1-1] will become [Q2-S+1, P2] (shift: P2-P1+1)

SQL：

UPDATE items SET position=CASE position >= Q1 THEN position-S ELSE position+P2-P1+1 WHERE position BETWEEN Q1-S AND Q2;

因此，使用这两个查询中的一个，您将能够在一个语句中更新所有相关的位置。只需使用适当的(P1，P2，A)或(Q1，Q2，S)。