问个小问题， mysql 查询所有父节点的一点小疑惑？

[whooami]

SELECT
@r AS _id,
	(SELECT @r := parent_id FROM t_category WHERE id = _id) AS parent_id,
	@l := @l + 1 AS lvl
	FROM (SELECT @r := 89, @l := 0) vars, t_category t
WHERE @r <> 0 AND parent_id >0;

如果 @r = 89 在库中是存在的，这时候结果正常 如果 @r = 89 在库中是不存在的，就会出现以下记录

_id          parent_id   lvl
89   		NULL        1
89   		NULL        2
...

再添加 and parent_id is not null 也一样输出上面的纪录。 但是如果在外面再包上一层 再判断 parent_id is not null 结果就正常了。 请教一下各位大佬这是什么问题？

还有像这种查询所有父 /子节点的需求最佳方案一般是在代码层解决，还是像这样在 sql 层解决？还有没有更好的方案？

CREATE TABLE `t_category` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'pk',
`uid` bigint(20) unsigned DEFAULT '0' COMMENT '用户 ID',
`name` varchar(32) NOT NULL COMMENT '名称',
`parent_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '0 无父级',
PRIMARY KEY (`id`),
KEY `IDX_UID` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=utf8mb4 COMMENT='总分类表';

[b821025551b]

（再添加 and parent_id is not null 也一样输出上面的纪录。 但是如果在外面再包上一层 再判断 parent_id is not null 结果就正常了）这是因为操作的数据集不同；直接添加的话，原记录中 parent_id 就是非空，所以加不加没效果；包一层后针对的是上层筛选过的结果集做数据集，parent_id 就是有 null ，可以正常过滤；

后面的问题，看起来不是很清晰，最好给一下表结构。

[whooami]

@b821025551b 抱歉，我忽略了。表中的 parent_id>=0 的非空字段。
```
CREATE TABLE `t_category` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'pk',
`uid` bigint(20) unsigned DEFAULT '0' COMMENT '用户 ID',
`name` varchar(32) NOT NULL COMMENT '名称',
`parent_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '0 无父级',
PRIMARY KEY (`id`),
KEY `IDX_UID` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=utf8mb4 COMMENT='总分类表';
```

[byty18768]

mysql 这个递归…给我留下了深刻的印象

[beijinglowb]

我们一般用 with recursive

[Mumu2580]

如果层级不高的话，直接加一个字段维护 root 到本节点路线就 ok 了。如果是固定不变的层级关系，一般全查了（涉及到的全查了），在应用层整理层级然后缓存。

[whooami]

@byty18768 我也是在网上查的，还要进一步理解
@beijinglowb
@Mumu2580
两位给出了另外两种方法，之前我们采用的是在应用层递归，这次新服务，想来问问那种方法比较好，看能不能精进一下？

[offswitch]

1.保存此节点的所有父节点到一个数组中
2.另外维持一张节点父节点关系表
3.加载到内存中，维持好结构，避免频繁查，一条 select 查出所有节点信息即可，修改了就重新加载
1 、2 差不多

[whooami]

@offswitch 嗯，这个也是现在较常用的作法。因为是多级，可以通俗理解成树形。所以如果这样分可能效果不大。比如一级节点只有 20 个，二级节点可能有 500 ，三级可能更多。变更也是根据数据热度变更的。所以在想有没有更好的办法。当然加载到内存，避免频繁查就很好。

[wxf666]

@whooami 如果你不介意额外占用 10 倍空间，你可以用《闭包表》。

一条 SELECT 就能查出某个节点的所有祖先节点，而且是顺序查询，速度非常快。

我以前发过一个[帖子](/t/889443)，讨论过这种数据库表结构。

[beijinglowb]

@whooami 能在 sql 内解决效率最高，应用里递归慢不说，额外资源开销也很大。

[whooami]

@wxf666 谢谢，也是一种思路。对于有限数据来说可能闭包表是一个方案。对于目前本身数据量在膨胀并且还没有限制的一个业务来讲可能还要进一步的考量
@beijinglowb 嗯，我也是这么想的。

[Shamiko]

层级不高直接 with recursive ，高了用物化路径

[whooami]

@Shamiko 简单明了， 感谢大佬