1
victorc 2023-08-31 09:38:01 +08:00
搞毛的父子层级,不要折腾 mysql 了,加冗余字段,扁平表结构,查一次,在内存中重建树结构
|
5
cnzmz OP DROP PROCEDURE IF EXISTS getResAlisa;
DELIMITER $$ CREATE PROCEDURE getResAlisa(IN resid varchar(255), OUT path TEXT) BEGIN DECLARE catname VARCHAR(255); DECLARE temppath TEXT; DECLARE tempparent VARCHAR(255); SET max_sp_recursion_depth = 255; SELECT c_resalias, c_pid FROM t_restree WHERE c_resid=resid INTO catname, tempparent; IF tempparent IS NULL THEN SET path = catname; ELSE CALL getResAlisa(tempparent, temppath); SET path = CONCAT(temppath, '/', catname); END IF; END$$ DELIMITER ; DROP FUNCTION IF EXISTS getResAlisa; DELIMITER $$ CREATE FUNCTION getResAlisa(cat_id VARCHAR(255)) RETURNS TEXT DETERMINISTIC BEGIN DECLARE res TEXT; CALL getResAlisa(cat_id, res); RETURN res; END$$ DELIMITER ; 弄好了 ,写了一个存储过程,写了一个函数,终于查出来了,参考的是 https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query |
6
Navee 2023-08-31 12:08:40 +08:00 via iPhone
刚看到 mysql8 的支持: https://mp.weixin.qq.com/s/dywg4DEYUl751IooxFohUg
|
7
dr4gon02142 2023-08-31 17:33:43 +08:00
@victorc 冗余字段会不会新增数据不一致问题
|
8
iosyyy 2023-08-31 18:03:51 +08:00
这种活并不是数据库应该做的 建议放在业务代码中
|