一、表结构

简化的表结构类似

create table nodes (
  id int primary key auto_increment,
  name varchar(255) not null default '' comment '节点名称',
  parent_id int not null default 0 comment '上级节点',

  index nodes_parent_id_index (parent_id),
  index nodes_name_index (name)
);

二、当前解决方案

-- 添加字段
alter table nodes add column path text not null comment '节点路径';

-- 创建索引
create index nodes_path_index on nodes(path);

-- 更新历史数据
update nodes current
left join nodes parent on current.parent_id = parent.id
set path = ifnull(concat(parent.path, ',', current.parent_id), '0');

-- 插入更新后执行
update nodes current
left join nodes parent on current.parent_id = parent.id
set path = ifnull(concat(parent.path, ',', current.parent_id), '0');
where current.id = 198;

-- 级联删除
delete from nodes where id = 198;
delete from nodes where (path like '0,5,198,%' and parent_id = 198);

1. 查询ID为“5”的节点的所有子级、孙子级中name包含“搜索词”的记录

更新表后的查询方式：

-- 查询父级节点记录，获取到父级的path
select * from nodes where id = 5;

-- 通过父级path进行模糊查询
select * from nodes where (parent_id = 5 or path like '0,5,%') and name like '%搜索词%';

可以创建一个触发器，在插入、修改数据时，更新子级的path。

2. 查询ID为“5”的节点的所有父级

-- 获取当前节点
select * from nodes where id = 5;

-- 使用当前节点的path查询所有父级
select * from nodes where find_in_set(id, '0,5');

-- 或者也可以使用in
select * from nodes where id in (5);