树型结构数据存储-左右值编码法

说明

• lft：左值
• rgt：右值
• lv：层级深度
• 优点：平行查询，无限分类，查询效率高
• 缺点：增删改，涉及到多节点调整，效率低

新增

1. 取出上级的左值、右值、深度，命名为lft_p、rgt_p、lv_p

   • 上级存在：取出直属下级数据（左值大于lft_p，右值小于rgt_p，深度等于lv_p+1）中右值的最大值，命名为lft_i

     if (lft_i == null)
     	lft_i = lft_p + 1
     else 
     	lft_i += 1
     

   • 上级不存在：取出顶层数据（lv=1）中右值的最大值，命名为lft_i

     if (lft_i == null)
     	lft_i = 1
     else 
     	lft_i += 1
     

2. 更新现有节点

   -- 左值大于lft_i的数据，左值全部+2
   update xx set lft = lft +2 where lft > lft_i
   -- 右值大于等于lft_i的数据，右值全部+2
   update xx set rgt = rgt +2 where rgt >= lft_i
   

3. 插入新节点

   insert into xx (lft, rgt, lv) values(lft_i, lft_i + 1, lv_p + 1)
   

删除

1. 取出待删除节点的左值、右值，命名为d_lft、d_rgt

2. 计算待删除节点左右值差值大小

   set num = d_rgt - d_lft + 1
   

3. 删除节点及子节点

   delete from xx where lft >= d_lft and rgt <= d_rgt
   

4. 更新现有节点

   -- 左值大于d_lft的节点，左值全部减num
   update xx set lft = lft - num where lft > d_lft
   -- 右值大于d_rgt的节点，右值全部减num
   update xx set rgt = rgt - num where rgt > d_rgt
   

移动

右移

1. 取出目标节点左值、右值、深度，命名为t_lft、t_rgt、t_lv

2. 取出待移动节点左值、右值、深度，命名为m_lgt、m_rgt、m_lv

3. 取出待移动节点及其子节点的主键，命名为ids

4. 进行相关校验，不能向子孙节点移动

5. 计算待移动节点左右值差值大小

   set num = m_rgt - m_lft + 1
   

6. 更新除移动节点及其子节点外的节点

   -- 左值大于m_lft且小于t_lft的节点，左值全部减num
   update xx set lft = lft - num where lft > m_rgt and lft < t_rgt
   -- 右值大于m_rgt且小于t_rgt的节点，右值全部减num
   update xx set rgt = rgt - num where rgt > m_rgt and rgt < t_rgt
   

7. 更新移动节点及其子节点

   update xx set lft = lft + (t_rgt - m_rgt) - 1, rgt = rgt + (t_rgt - m_rgt) - 1, lv = lv + (t_lv - m_lv) + 1 where find_in_set(id, ids)
   

左移

1. 取出目标节点左值、右值、深度，命名为t_lft、t_rgt、t_lv

2. 取出待移动节点左值、右值、深度，命名为m_lgt、m_rgt、m_lv

3. 取出待移动节点及其子节点的主键，命名为ids

4. 进行相关校验，不能向子孙节点移动

5. 计算待移动节点左右值差值大小

   set num = m_rgt - m_lft + 1
   

6. 更新除移动节点及其子节点外的节点

   -- 左值大于m_lft且小于t_lft的节点，左值全部减num
   update xx set lft = lft - num where lft > t_rgt and lft < b_lft
   -- 右值大于m_rgt且小于t_rgt的节点，右值全部减num
   update xx set rgt = rgt - num where rgt > t_rgt and rgt < b_lft
   

7. 更新移动节点及其子节点

   update xx set lft = lft + (t_rgt - m_lft), rgt = rgt + (t_rgt - m_lft), lv = lv + (t_lv - m_lv) + 1 where find_in_set(id, ids)
   

查询

• 查询某一节点下子节点

  select a.* from xx a inner join xx b on a.lft > b.lft and a.rgt < b.rgt and a.lv = b.lv + 1 where b.id = 3
  

• 查询某一节点下子孙节点

  select a.* from xx a inner join xx b on a.lft > b.lft and a.rgt < b.rgt where b.id = 3
  

• 查询父节点

  select a.* from xx a inner join xx b on a.lft < b.lft and a.rgt > b.rgt and a.lv = b.lv - 1 where b.id = 3