维护/更新mysql中的记录顺序

Question

我在 mySql 中有一个记录表。 我需要按照用户指定的方式维护它们的订单。 所以我添加了一个“位置”列。

当我移动特定记录时更新所有记录的 SQL 语句是什么？ 我有这样的想法：

UPDATE items SET position = '2' WHERE id ='4';
UPDATE items SET position = position+1 WHERE position >= '2' AND id != '4';

但是如果记录向下移动，则大于将小于。 有什么窍门呢？ 谢谢！

Answer 1

像这样的事情会做吗？

UPDATE items 
SET position = CASE position 
  WHEN $oldpos THEN $newpos 
  ELSE position + SIGN($oldpos-$newpos)
 END
WHERE position BETWEEN LEAST( $newpos, $oldpos ) 
                AND GREATEST( $newpos, $oldpos );

我测试了几次，似乎有效。

Answer 2

对于例如具有由用户维护的行号的销售​​订单执行此类操作，我发现最好在 BL 或 UI 中的数组中处理它。 通常他们会想要调整几条记录，有时想说“算了”。 因此，最简单的方法可能是等到他们点击“确定”按钮（或类似的按钮），然后用当前的顺序将它们全部写回。

您最终也可能会处理删除，这是您可以用相同方式处理的另一个问题。

Answer 3

这是我对此的看法：我的 row_index 列的数字步长为 10（例如：0、10、20，等等）。 当用户更新其中一行时，您需要知道它是上升还是下降。 如果必须将第 2 行移至第 4 行（向下，所需 row_index = 30），请将 row_index 设置为 35； 否则（如果向上）将其设置为 25。设置此值：

UPDATE your_table SET row_index = 35 WHERE your_id = 1234

现在您的顺序正确，但 row_indexes 混乱。 要解决此问题，请执行以下两个查询：

SET @pos:=-10;
UPDATE your_table SET row_index = @pos:=@pos+10 WHERE ...

这将更新所有行（或使用 where 语句选择的行）并设置无间隙的 row_index 列（0、10、20、30 等）。 当然，这种方式会增加写入操作的负载，但我认为这是在读取操作期间获取顺序的最快方法。 如果删除一行，您只需重新运行固定订单查询即可。

Answer 4

我建议至少在项目之间使用较大的增量（例如 10000）。 然后只需执行订单即可。 如果您需要将 11000 移动到 9000 到 10000 之间，则只需将其设置为 9500 即可。

这并不能消除重新排序的问题，但可以大大减少它。 在某些时候，您最好删除所有行，然后以正确的顺序读取它们。 恕我直言，像您这样进行更新太容易出错。

Answer 5

您可能会考虑使用链表类型的方法，带有指向列表中下一个项目（也可能是前一个项目）的键，那么您只需更新一些记录，而不是全部记录。

Answer 6

有趣的！ 好吧，所以没有简单的答案。 我以为我只是错过了一些东西。 我想过维护一个外部阵列。 这听起来是最好的主意。

Answer 7

也许是这样的？

$item_id = $_GET['item_id']; 
$position = $_GET['new_position']; 
$old_position = $_GET['old_position'];

if($new_position < $old_position) {
    SQL: UPDATE items SET position = position+1 WHERE position >= $new_position AND position < $old_position  
} else {  
SQL: UPDATE items SET position = position-1 WHERE position <= $new_position AND position > $old_position
}  

SQL: UPDATE items SET position = $new_position WHERE item_id = $item_id