请教一个 mysql 递归获取父级字段+子级字段的问题

[cnzmz]

基本环境是 mysql 5.7 ，不支持 CTE 语法。

表结构如下：

-- auto-generated definition
create table t_restree
(
c_resid varchar(255) collate gbk_bin not null
primary key,
c_resname varchar(255) collate gbk_bin null,
c_resalias varchar(255) collate gbk_bin null,
c_pid varchar(255) collate gbk_bin null,
c_restype varchar(255) collate gbk_bin null,
c_order int null,
c_perm longtext null,
c_resdesc varchar(255) collate gbk_bin null,
c_created datetime null,
c_lastmodified datetime null,
c_status varchar(255) collate gbk_bin null,
c_extended longtext null
);

create index c_restree_pid
on t_restree (c_pid);

create index c_restree_restype
on t_restree (c_restype);


没搞明白怎么上传图片和附件，贴两个主要的数据
03c8d6fde1685d179d792527af31c340,指标看板,指标看板,Iff808081017e71047104f9ad017e712d171c0181
03d2180f40019cc6efe98901abd342fb,分页表格,分页表格,Iff808081017e71047104f9ad017e712d43fb0183
145b94aca3ae4917289b3b8b4cbe0e60,组件联动,组件联动,Iff808081017e71047104f9ad017e712e6f250186
1594881f77df3b9040ee2858ab27d44b,柱图,柱图,Iff808081017e71047104f9ad017e712d171c0181
1719e875525d731c7636f11701a45e4e,跳转页,资源跳转,Iff808081017e71047104f9ad017e712e6f250186

子级里面 c_pid 就是父级的 c_resid 最后一级的 c_pid 是个空值
需要获取
父级 c_resname/子级 c_resname 直到 c_pid 是空值。应该是需要递归获取
目前是建立一个临时表，加了两个字段，写了一个存储过程，但是存储过程目前执行不完。
CREATE TABLE new_table AS
SELECT *
FROM t_restree
WHERE 1 = 0;
INSERT INTO new_table
SELECT *
FROM t_restree;

ALTER TABLE new_table
ADD COLUMN c_resname_full VARCHAR(255) COLLATE gbk_bin NULL,
ADD COLUMN c_resalias_full VARCHAR(255) COLLATE gbk_bin NULL;

DELIMITER //

CREATE PROCEDURE update_recursive()
BEGIN
DECLARE c_resid_var VARCHAR(1024);
DECLARE c_resname_var VARCHAR(1024);
DECLARE c_resalias_var VARCHAR(1024);

update new_table SET c_resname_full = null, c_resalias_full = null WHERE 1=1;
-- 初始化根节点
UPDATE new_table SET c_resname_full = c_resname, c_resalias_full = c_resalias WHERE c_pid IS NULL;

-- 递归更新子节点
REPEAT
SET c_resid_var = NULL;
SET c_resname_var = NULL;
SET c_resalias_var = NULL;

-- 获取未更新的子节点
SELECT c_resid, c_resname, c_resname_full
INTO c_resid_var, c_resname_var, c_resalias_var
FROM new_table
WHERE c_resname_full IS NULL
LIMIT 1;

-- 更新子节点的
UPDATE new_table t1
JOIN new_table t2 ON t1.c_pid = t2.c_resid
SET t1.c_resname_full = CONCAT(t2.c_resname_full, '/', t1.c_resname)
WHERE t1.c_resid = c_resid_var;

UNTIL c_resid_var IS NULL END REPEAT;
END //

DELIMITER ;

call update_recursive();
困惑好久了，来次请教一下。

[victorc]

搞毛的父子层级，不要折腾 mysql 了，加冗余字段，扁平表结构，查一次，在内存中重建树结构

[cnzmz]

@victorc 现在这个数据结构是这样的，需要把这个数据查询出来。

[cnzmz]

@victorc 在内存中重建树结构应该怎么弄呢？是创建个临时表的意思吧。

[cnzmz]

@victorc 大体思路应该就是你说的那个，卡在了重建树结构这部分。

[cnzmz]

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

[Navee]

刚看到 mysql8 的支持: https://mp.weixin.qq.com/s/dywg4DEYUl751IooxFohUg

[dr4gon02142]

@victorc 冗余字段会不会新增数据不一致问题

[iosyyy]

这种活并不是数据库应该做的 建议放在业务代码中

[cnzmz]

@Navee mysql 8 确实支持，但是我们现在这个库是 mysql 5.7

[cnzmz]

@iosyyy 这个是有项目背景在的，是在 bi 中直接读取数据库出一份报表，所以需要数据库直接读取出来。