mysql存储过程（交换父级的位置，父级子集改变其排序）

本文链接：https://blog.csdn.net/xfy18317776108/article/details/104019393

表结构
在这里插入图片描述
储存过程：

BEGIN
#创建临时表用于存放修改前的domain信息
DROP TABLE if  exists tb_dir_before;
CREATE TEMPORARY TABLE if not exists tb_dir_before(id	VARCHAR(64) primary key, be_order VARCHAR(64));


#发起替换的id
set @id = did;

#被替换的id
set @replaceId = replaceId;

#查询发起替换的parent_order和self_order
select parent_order ,self_order into @oldparentOrder,@oldSelfOrder  from tb_data_dir where id = @id;

#查询被替换的parent_order和self_order
select parent_order ,self_order into @newparentOrder, @newSelfOrder from tb_data_dir where id = @replaceId;

#将被替换的子集插入临时表
insert into tb_dir_before  (select id,(CONCAT((CONCAT(IFNULL(@oldParentOrder,''),IFNULL(@oldSelfOrder,''))),substring((IFNULL(parent_order,'')),(LENGTH(CONCAT(IFNULL(@newParentOrder,''),IFNULL(@newSelfOrder,'')))+1)))) as be_order from tb_data_dir where parent_order like CONCAT(CONCAT(IFNULL(@newParentOrder,''),IFNULL(@newSelfOrder,'')),'%'));

#更换本级的self_order

update tb_data_dir set  self_order = @newSelfOrder where id = @id;
#更换被替换的self_order
update tb_data_dir set self_order = @oldSelfOrder where id = @replaceId;

#更换本级子集的parent_order
update tb_data_dir td JOIN (select id,(CONCAT((CONCAT(IFNULL(@newParentOrder,''),IFNULL(@newSelfOrder,''))),substring(IFNULL(parent_order,''),(LENGTH(CONCAT(IFNULL(@oldParentOrder,''),IFNULL(@oldSelfOrder,'')))+1)))) as aa from tb_data_dir where parent_order like CONCAT(CONCAT(IFNULL(@oldParentOrder,''),IFNULL(@oldSelfOrder,'')),'%')) c ON c.id = td.id set td.parent_order = c.aa;
#更换被替换的子集的parent_order

update tb_data_dir tdd join tb_dir_before tbf on tbf.id = tdd.id set tdd.parent_order = tbf.be_order;
END