大师网-带你快速走向大师之路 解决你在学习过程中的疑惑,带你快速进入大师之门。节省时间,提升效率

MySQL中更改树的父节点路径的语句

MySQL数据库中有如下一张表

create table b_class
(
   class_id             bigint(20) not null auto_increment,
   parent_id            bigint(20) default NULL,
   type                 bigint,
   code                 varchar(20) not null,
   name                 varchar(50) not null,
   show_order           int default 0,
   remark               varchar(100),
   status               smallint not null default 0 comment '0待处理1有效9失效',
   on_time              datetime,
   off_time             datetime,
   create_by            bigint,
   create_time          datetime,
   last_edit_by         bigint,
   last_edit_time       datetime,
   tree_path            text,
   primary key (class_id)
)

这张表是树型结构的,parent_id是每个记录的父记录ID,而tree_path是所有父记录ID的集合,用逗号分隔。假如tree_path的值为 “2,3,5”,即该记录的父节点ID为5,祖父节点ID为3,祖先节点ID为2。现在要修改某记录的parent_id,则他的子记录都需要跟随变化,可以用递归来实现,但会比较麻烦(在存储过程中写不好调试,在代码中写的话又会频繁访问数据库)。于是决定用一条Update语句来实现批量更新,但又要防止数据被改错,比如想替换“2,3”为“4,5”,结果把“12,31”为“14,51”了。

最终语句如下:

SET @old='1,2';
SET @new='3,4';
UPDATE b_class SET tree_path=REPLACE(REPLACE(CONCAT('$',tree_path,',#'),CONCAT('$',@old,','),CONCAT(@new,',')),',#','')
  WHERE CONCAT(tree_path,',') LIKE CONCAT(@old,',%');

写DAO代码的时候把@old,@new作为参数传进去就可以了。