V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
cnzmz
V2EX  ›  MySQL

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

  •  
  •   cnzmz · 2023-08-31 09:03:00 +08:00 · 1606 次点击
    这是一个创建于 450 天前的主题,其中的信息可能已经有所发展或是发生改变。
    基本环境是 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();
    困惑好久了,来次请教一下。
    10 条回复    2023-09-04 14:43:13 +08:00
    victorc
        1
    victorc  
       2023-08-31 09:38:01 +08:00
    搞毛的父子层级,不要折腾 mysql 了,加冗余字段,扁平表结构,查一次,在内存中重建树结构
    cnzmz
        2
    cnzmz  
    OP
       2023-08-31 09:40:05 +08:00
    @victorc 现在这个数据结构是这样的,需要把这个数据查询出来。
    cnzmz
        3
    cnzmz  
    OP
       2023-08-31 09:40:41 +08:00
    @victorc 在内存中重建树结构应该怎么弄呢?是创建个临时表的意思吧。
    cnzmz
        4
    cnzmz  
    OP
       2023-08-31 10:14:45 +08:00
    @victorc 大体思路应该就是你说的那个,卡在了重建树结构这部分。
    cnzmz
        5
    cnzmz  
    OP
       2023-08-31 10:58:39 +08:00
    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
        6
    Navee  
       2023-08-31 12:08:40 +08:00 via iPhone
    dr4gon02142
        7
    dr4gon02142  
       2023-08-31 17:33:43 +08:00
    @victorc 冗余字段会不会新增数据不一致问题
    iosyyy
        8
    iosyyy  
       2023-08-31 18:03:51 +08:00
    这种活并不是数据库应该做的 建议放在业务代码中
    cnzmz
        9
    cnzmz  
    OP
       2023-09-04 14:42:12 +08:00
    @Navee mysql 8 确实支持,但是我们现在这个库是 mysql 5.7
    cnzmz
        10
    cnzmz  
    OP
       2023-09-04 14:43:13 +08:00
    @iosyyy 这个是有项目背景在的,是在 bi 中直接读取数据库出一份报表,所以需要数据库直接读取出来。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   918 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 22:17 · PVG 06:17 · LAX 14:17 · JFK 17:17
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.