MySQL临时变量存储多条记录的实现方法及GROUP_CONCAT查询异常解决

MySQL的用户自定义变量本身是标量类型，默认只能存储单个值，无法直接存储多行结果集。你遇到的GROUP_CONCAT后匹配失效问题，本质是因为@Leaf1存储的是逗号拼接的字符串，直接用P = @Leaf1做等值判断时，MySQL会自动将字符串隐式转换为数值，只会取第一个逗号前的数值参与匹配，所以仅能匹配到第一个值对应的节点。

修复原有写法的方案 #

如果要沿用你现有变量+GROUP_CONCAT的逻辑，把等值判断替换为FIND_IN_SET函数即可，修改后的查询如下：

set @Root = (select N from pract_db.Tree where P is Null);
set @Leaf1 = (select GROUP_CONCAT(N) from pract_db.Tree where P=@Root);
set @Leaf2 = (select GROUP_CONCAT(N) from pract_db.Tree where FIND_IN_SET(P, @Leaf1));

select N, P,
    CASE when (P is NULL) then "Root"
         when FIND_IN_SET(P, @Root) then "Leaf"
         when FIND_IN_SET(P, @Leaf1) then "Leaf"
         when FIND_IN_SET(P, @Leaf2) then "Leaf"
         else "Inner"
    end as Value
from pract_db.Tree;

注意FIND_IN_SET的参数顺序是(要查找的值, 逗号分隔的字符串)，不要写反。

更推荐的实现方式（无需变量） #

如果不想处理变量拼接的问题，可以直接用子查询完成匹配，逻辑更清晰也不会有隐式转换的坑：

select N, P,
    CASE when P is NULL then 'Root'
         when P in (select N from Tree where P is NULL) then 'Leaf'
         when P in (select N from Tree where P in (select N from Tree where P is NULL)) then 'Leaf'
         when P in (select N from Tree where P in (select N from Tree where P in (select N from Tree where P is NULL))) then 'Leaf'
         else 'Inner'
    end as Value
from pract_db.Tree;

MySQL 8.0+ 递归CTE方案（适配任意层级树结构） #

如果你的树层级不固定，用递归公共表表达式可以一次性遍历所有层级，不需要手动写多层判断：

with recursive cte as (
    select N, P, 'Root' as Value from Tree where P is NULL
    union all
    select t.N, t.P, 'Leaf' as Value 
    from Tree t join cte on t.P = cte.N
    where cte.Value = 'Root' or cte.Value = 'Leaf'
)
select t.N, t.P, ifnull(c.Value, 'Inner') as Value
from Tree t left join cte c on t.N = c.N;

内容的提问来源于stack exchange，提问作者shiv_90