Mysql自定义变量在递归遍历中的妙用

借着在解决一个递归查询父目录的问题，学习了一下mysql变量的用法，在某些场景下这种解法还是比较有特效的，下面具体来聊一下场景，同时也会详细分析下mysql自定义变量的用法

场景：
获取从树的根节点到叶子节点的全路径（表现形式任意，本文以父目录-子目录为一条记录的形式逐条展示）

树节点层次结构具体：
aaaa
|--aaaabbbb
   |--aaaabbbbcccc
       |--aaaabbbbccccdddd

原表结构
 

预期mysql结果：

 

解决方案：

SELECT 
    t2.id, t2.node_name, t2.parent_node_id 
FROM 
(
    SELECT 
        @supId AS tempId,
        (SELECT @supId := parent_node_id FROM test_node WHERE id = tempId) AS supId, 
        @level := @level + 1 AS lvl 
    FROM 
        (SELECT @supId := aaaabbbbccccdddd, @level := 0) vars,test_node 
    WHERE 
        @supId <> 0 OR @supId <> -1
)  t1 
JOIN 
    (SELECT * FROM test_node WHERE deleted = 0) t2 
ON 
    t1.tempId = t2.id 
ORDER BY t1.lvl DESC

说明：
1、首先设定了两个临时变量 @supId,@level用来记录当前查询需要的父节点id和层次数
   对应语句：(SELECT @supId := aaaabbbbccccdddd, @level := 0) vars
2、根据上次结果的变量获取新的记录，拿到id=@supId的记录（条件id=tempId），同时将新的记录的parent_node_id赋值给@supId，@level+1赋值给@level
然后继续查询
3、循环2的过程，直至结束
4、再与外围的t2表join，获取最终结果

总结
使用自定义变量基本就两点
1、在from后初始化
2、select后赋值以及使用，赋值一般都是在select后再来一个select2，使用就是用的这个select2形成的表记录

本文主要参考文章：mysql自定义变量的妙用 - 墨天轮