mysql 查询所有子节点的相关数据

定义一个函数

CREATE DEFINER=`root`@`localhost` FUNCTION `getColumnChildLst`(rootId INT) RETURNS varchar(1000) CHARSET utf8
BEGIN
       DECLARE sTemp VARCHAR(1000);
       DECLARE sTempChd VARCHAR(1000);
    
       SET sTemp = '$';
       SET sTempChd =cast(rootId as CHAR);
    
       WHILE sTempChd is not null DO
         SET sTemp = concat(sTemp,',',sTempChd);
         SELECT group_concat(column_id) INTO sTempChd FROM cms_column where FIND_IN_SET(p_id,sTempChd)>0 ORDER BY sort, column_id;
       END WHILE;
       RETURN sTemp;
     END

使用:

select t1.article_id, t1.sort,t1.source_from,t1.title,
        t1.column_id,t1.is_top,t1.view_count,t1.type,t1.status,
        t1.audit_flag,
        t2.name
        
        from cms_article t1
        left join cms_column t2 on t1.column_id = t2.column_id
        
        where find_in_set(t1.column_id, getColumnChildLst(0)) and t1.del_flag = 0;