设计思路-关于树节点结构设计

增加path字段存储树的路径

1.可以通过当前节点追溯到上级所有父节点

2.可以通过当前节点查询所有子节点

比如满足以下需求

参考sql

------------------------------所有设备权限------------------------------
1.获得节点
 explain SELECT `id`,
       `userid`,
       `name`,
       `bgcolor`,
       `remotenum`,
       `orderid`,
       `pid`,
       `path`,
       `depth`,
       `tag_type`
FROM `re_tag`
WHERE userid = 12532915
  AND pid = 0
ORDER BY re_tag.tag_type desc, case when orderid > 0 then orderid else id end asc

2.获得数量 count位动态生成,前端目前分页是80一页
SELECT COUNT(distinct CASE
                          WHEN re_tag.path REGEXP '[[:<:]]317[[:>:]]' OR re_remotetag.tagid = 317
                              THEN re_remotetag.remoteid END) AS count_317,
       COUNT(distinct CASE
                          WHEN re_tag.path REGEXP '[[:<:]]134863[[:>:]]' OR re_remotetag.tagid = 134863
                              THEN re_remotetag.remoteid END) AS count_134863
FROM `re_remotetag`
         inner JOIN `re_tag` ON re_remotetag.tagid = re_tag.id
WHERE re_tag.userid = 12532915

3.是否有下级节点
explain SELECT pid as id, COUNT(1) as count
FROM `re_tag`
WHERE userid = 12532915
  AND pid IN (12532915,317,134863,142473,148154,171334)
GROUP BY pid
4.获取某个节点子节点
ent_tag.id IN (?) 为节点自身  (ent_tag.path REGEXP ?)) 为路径包含
SELECT distinct CASE WHEN ent_tag.path REGEXP '[[:<:]]55840[[:>:]]' OR ent_tag.id = 55840 THEN ent_tag.id END as tag_id
FROM `ent_tag`
WHERE ent_tag.ent_userid = ?
  AND (ent_tag.id IN (?) OR (ent_tag.path REGEXP ?))

------------------------------含有分组和设备场景------------------------------
1.获得节点  re_tag.id为授权的节点,path 也为授权节点拼接 id in为所有授权节点的父节点拼接 分页参数前端传80
explain SELECT `id`,
       `userid`,
       `name`,
       `bgcolor`,
       `remotenum`,
       `orderid`,
       `pid`,
       `path`,
       `depth`,
       `tag_type`
FROM `re_tag`
WHERE userid = 12532915
  AND pid = 0
  AND (((re_tag.id IN (134864,142743) OR (re_tag.path REGEXP '[[:<:]]134864|142473[[:>:]]'))) OR (id IN (134863, 171337,19858496,19858497)))
ORDER BY re_tag.tag_type desc, case when orderid > 0 then orderid else id end asc
LIMIT 100

2.获取数量场景
  re_tag.id为授权节点数量 regexp为授权节点动态生成 remoteid为直接授权主机的ids
explain SELECT COUNT(distinct CASE
                          WHEN re_tag.path REGEXP '-317-' OR re_remotetag.tagid = 317
                              THEN re_remotetag.remoteid END) AS count_317,
       COUNT(distinct CASE
                          WHEN re_tag.path REGEXP '[[:<:]]134863[[:>:]]' OR re_remotetag.tagid = 134863
                              THEN re_remotetag.remoteid END) AS count_134863,
       COUNT(distinct CASE
                          WHEN re_tag.path REGEXP '[[:<:]]142473[[:>:]]' OR re_remotetag.tagid = 142473
                              THEN re_remotetag.remoteid END) AS count_142473,
       COUNT(distinct CASE
                          WHEN re_tag.path REGEXP '[[:<:]]171337[[:>:]]' OR re_remotetag.tagid = 171337
                              THEN re_remotetag.remoteid END) AS count_171337
FROM `re_remotetag`
         inner JOIN `re_tag` ON re_remotetag.tagid = re_tag.id
WHERE re_tag.userid = 12532915
  AND (re_tag.id IN (134864,142473 ) OR (re_tag.path REGEXP ' [[:<:]]134864|142473[[:>:]]') OR re_remotetag.remoteid IN (994475629))

3.是否含有子节点
pid为查询的pid id为授权节点的id集合
explain SELECT pid as id, COUNT(1) as count
FROM `re_tag`
WHERE ((userid = 12532915 AND pid IN (317,134863,142473)) OR (id IN (171337,134863,134863,171337,19858496,19858497,134864,142473)))
GROUP BY pid

4.获取指定节点子节点
regepx为查询父节点id,re_tag.id IN (?, ?)为授权节点id re_tag.path REGEXP ? 为授权节表达式  re_tag.id IN 为授权主机带过来的
SELECT distinct CASE WHEN re_tag.path REGEXP '[[:<:]]134863[[:>:]]' OR re_tag.id = 134863 THEN re_tag.id END as tag_id
FROM `re_tag`
WHERE re_tag.userid = ?
  AND (re_tag.id IN (?, ?) OR (re_tag.path REGEXP ?) OR re_tag.id IN (?)) 

mysql regexp不支持此写法 后面改成 path字段例子:1-2,33-31