主页 / dba / 问题 / 203799
Accepted
ThomasK
Asked: 2018-04-13 11:16:25 +0800 CST

更改行时如何更新其他行的“sorting_order”列？

  • 772

添加新类别和更改现有类别时，我需要为类别设置自定义顺序。我相信代码和sql查询在这两种情况下都是一样的。在我的webshop_categories-table 中，我有一个so-column（排序顺序 INT），用于确定应将类别输出到查看器的顺序。

这是我的类别表的简化示例（so =sorting_order）：

id  |   name   |  so
--------------------
1   |   Shoes  |  1
2   |   Hats   |  2
3   |   Bags   |  3
4   |   Coats  |  4
5   |   Rings  |  5

添加新类别时，无需定义排序顺序，我只需提交一个表单，然后运行如下查询：

$so = $dbh->query('SELECT (MAX(so)+1) FROM webshop_categories WHERE so != 0')->fetchColumn();
$ins = $dbh->prepare('INSERT INTO webshop_categories (name, so)')->execute([$_POST['name'], $so]);

（这将添加一个新类别，并将其放在最后。在这种情况下，so将设置为 6）

这是添加新类别的最基本方法。工作正常。但在大多数情况下，在处理类别时，需要将其按特定顺序进行呈现。

但通常当添加一个新类别时，人们会想要指定这个新类别相对于现有类别的位置。就在那时，更新so-column 成为我的脑筋急转弯......

我添加新类别的简化表格：

<form>
  <input type="text" name="name">
  <select name="so">
    <option value="0" selected>At the end</option>
    <option value="1">At the top</option>
    <!-- php foreach loop through exising categories -->
    <option value="<?=so?>">after <?=category_name?></option>
  <select>
  <input type="submit" name="submit_add" value="Add">
</form>

<?php
if($_POST['submit_add']){
  if($_POST['so']==0){  //  put new category at the end
    $so = $dbh->query('SELECT (MAX(so)+1) FROM webshop_categories WHERE so != 0')->fetchColumn();
  } else {
    $so = $_POST['so'];
  }
  $ins = $dbh->prepare('INSERT INTO webshop_categories (name, so)')->execute([$_POST['name'], $so]);
}
?>

我经历过第一个场景：没有指定排序顺序。
但是在使用此功能时，我需要更新so受影响的类别。
如果我在 Shoes 和 Hats 之间添加一个新类别，我会在选择列表中的Shoes 之后使用- 这意味着这个新类别应该获得2so的值。这意味着有必要对帽子和上述所有类别进行处理。 +1so

什么是实现这一目标的好方法？

与此相当的情况是将类别从较高的数字移动到较低的so数字。需要为移动的类别腾出空间，然后将其余的类别向上（+1对他们的做 a so）。但当然只有在这两个位置之间的那些。

反过来，它应该非常相似——将一个类别从较低的数字移到较高的数字。唯一的区别是我们现在需要缩小我们在这两种力量之间创造的差距。
如果我将 Hats 移到after Coats- Hats 将变为 4，其余的将需要向下碰撞（-1对他们的做 a so）。

我试图通过在使用此功能时获取受影响的类别来做到这一点，并在 foreach 循环中更新每个类别。但我无法让它在所有情况下都按预期工作。

制作所有行的副本（到 PHP 数组中），删除它们，创建一个更新的新列表，然后将它们插入回来也是一种解决方案，但我认为仅仅进行这些更改就不是必需的。 .但这是我认为有效的一种方法......

有什么建议吗？

mysql update

2 个回答

  1. Best Answer

    添加新类别：

        $t_cat = 'jeans';//new category to insert
    $so_to_set = 2;// so for new category

    先更新所有so大于等于$so_to_set

        $query = "update categaries set so = so+1 where so >= $so_to_set";

    现在插入新category的$so_to_set

        $query = "insert into categaries(name,so) values($t_cat,$so_to_set)";


    更改so类别：

    假设我们有categaries以下so

        +-------------------------+
    | id  |   name    | so    |
    +-----+-----------+-------+
    | 1   |   shoes   |  1    |
    | 6   |   jeans   |  2    |
    | 2   |   shirts  |  3    |
    | 3   |   pants   |  4    |
    | 4   |   blazzers|  5    |
    | 5   |   bags    |  6    |
    +-------------------------+

    $a = 2;// so of category whose position we want to change
    $b = 5;// new so for category

    // get id of category whose position we want to change
    $temp_id = "select id from categaries where so = $a"; 
    // that would be 6

    $a现在在和之间更新所有内容$b

        if($a < $b){
        // if $a is less than $b then
        $set = "so = so - 1";
        $where = "so > $a and so <= $b";
    }else{
        $set = "so = so + 1";
        $where = "so < $a and so >= $b";
    }

    // update so's
    $query = "update categaries set $set where $where";

    现在更新所以$temp_id

        $query = "update categaries set so = $b where id = $temp_id";

    该过程的最终输出将是

        +-------------------------+
    | id  |   name    | so    |
    +-----+-----------+-------+
    | 1   |   shoes   |  1    |
    | 6   |   jeans   |  5    |
    | 2   |   shirts  |  2    |
    | 3   |   pants   |  3    |
    | 4   |   blazzers|  4    |
    | 5   |   bags    |  6    |
    +-------------------------+
    • 6

  2. 排序应以大于一的步长执行。我更喜欢步长等于 10 以获得更好的视觉表示。主要思想总是可以插入新项目，通常未使用的 SO=N+1。假设你在 30 到 40 之间插入了一些东西，那么你的so序列现在看起来像这样：10-20-30-31-40-50。

    然后你必须UPDATE这样：

    SET @cnt = 0;
UPDATE table AS w
   SET w.so = @cnt := @cnt + 10
 ORDER BY w.so ASC
;

    结果UPDATE是该so字段由均匀分布的数字填充，步长为 10：10-20-30-40-50-60。现在 31 变成 40，40 变成 50 等等。

    START TRANSACTION .. COMMIT;实际上，为了数据的一致性，最好将所有插入/重枚举封装起来。两个同时INSERTS具有相同的so=N+1 可能会导致问题。还UNIQUE建议为so字段使用索引。

    稍后当您从表中获取数据时，您必须将so值除以 10 以获得自然数的普通序列：

    SELECT w.so / 10 AS so
     , ... 
  FROM table AS w
 ORDER BY w.so
;
    • 1

相关问题