在删除一个或多个子表后更新排序 - mySQL update sort - order from table with unlimited childs after delete one or more

mySQL update sort - order from table with unlimited childs after delete one or more

本文关键字：排序更新一个删除 | 更新日期: 2023-09-27

我有一个这样的表:

id | parent_id | order 
 1 |     0     |   1   
 2 |     1     |   1
 3 |     1     |   2
 4 |     1     |   3
 5 |     0     |   2
 6 |     0     |   3
 7 |     5     |   1
 8 |     5     |   2

通过parent_id在同一个表中获取无限的类别和子类别。我有一个表单与复选框删除一个或多个，我想删除一个或多个行后重新排序组由parent_id。

我写了这样的代码:

mysql_query("SET @rownumber = 0;");
$sql_previous_order = "UPDATE `cms` SET `order` = (@rownumber:=@rownumber+1) ORDER by parent_id, `order` ASC";

您可以引入一个额外的变量- p_id。与parent_id比较，当parent_id改变时，将rownumber重置为0。像这样

 SET @rownumber = 0;
 SET @p_id = 0;
select id, 
       CASE @p_id 
            WHEN parent_id THEN @rownumber:=@rownumber+1 
            ELSE @rownumber:=0 END as new_order,
       @p_id:=parent_id  as p_id
from cms
ORDER by parent_id ASC
;