mysql - 如何仅通过单个查询修改卡片在表中的位置？

Question

假设我的数据库中有三张卡(“第一”、“第二”、“第三”)，位置为 1(“foo”)、2(“bar”)和 3(“fubar”)。然后我想把fubar移到前面。那么它们的顺序将是 1(“fubar”)、2(“foo”)、3(“bar”)。

现在每张卡片都属于一个列表，每个列表的每张卡片都在这个数据库中。这意味着只有属于同一列表的卡片才需要更新其位置。

在单个查询中执行此操作的最佳方法是什么？

Answer 1

如果存在涉及“位置”的唯一约束，则在单个查询中很难(如果不是不可能)做到这一点，但如果没有......

UPDATE theTable 
SET position = IF(otherField='fubar'
                  , [new_position]
                  , IF([new_position] < [old_position]
                       , position + 1
                       , position - 1
                      )
                 ) 
WHERE [condition indicating list] 
      AND (
            ([new_position] < [old_position] 
             AND position BETWEEN [new_position] AND [old_position]
            )
           OR 
            ([new_position] > [old_position] 
             AND position BETWEEN [old_position] AND [new_position]
            )
          ) 
;

如果存在唯一约束，通常可以通过执行两个查询来绕过它；一个将它们发送到“偏僻”的范围，另一个(相应调整)将它们带回新位置。

编辑:这只适用于将“fubar”移动到较低的位置；如果走高，您将需要 <= ，和position - 1 .

Edit2:哦，嗯，实际上这只适用于“最后到第一个”。您需要知道“fubar”的原始位置，以防止其后面的位置进一步向后移动，留下间隙。

更新:通过已知的“old_position”，我们可以使用相同的查询向前或向后移动。