无限层级mysql数据表结构

# 无限层级mysql数据表结构
# 创建表
-- auto-generated definition
create table target_resource
(
  id               int not null
    primary key,
  resource_id      int null,
  resource_team_id int null
);
# 创建关系表
-- auto-generated definition
create table agent_tree_path
(
  parents_team_id int             not null,
  son_team_id     int             not null,
  path_length     int default '0' null,
  primary key (parents_team_id, son_team_id),
  constraint agent_tree_path_ibfk_1
  foreign key (son_team_id) references target_resource (id)
);

create index son_team_id
  on agent_tree_path (son_team_id);

# 插入到关系表
INSERT INTO agent_tree_path (parents_team_id, son_team_id, path_length)
SELECT t.parents_team_id, 7, t.path_length + 1
FROM agent_tree_path AS t
WHERE t.son_team_id = 6
UNION ALL
SELECT 7, 7, 0;
# 删除子节点
DELETE
FROM agent_tree_path
WHERE son_team_id = 7;
# 要删除一颗完整的子树，parents_team_id  和他的后代
DELETE
FROM agent_tree_path
WHERE son_team_id IN (SELECT son_team_id FROM agent_tree_path WHERE parents_team_id = 4);
# 查询子节点的所有父节点
SELECT t.*
FROM target_resource AS t
       INNER JOIN agent_tree_path a on t.id = a.parents_team_id
WHERE a.son_team_id = 6;
# 查询父节点所有的子节点
SELECT t.*
FROM target_resource AS t
       INNER JOIN agent_tree_path a on t.id = a.son_team_id
WHERE a.parents_team_id = 6