引用场景

• 盖楼评论
• 菜单

简单表结构

• id
• pid
• name

不多解释直接上函数

DELIMITER ;;
CREATE FUNCTION getchildren(orgid BIGINT)
RETURNS VARCHAR(4000)
BEGIN
DECLARE oTemp VARCHAR(4000);
DECLARE oTempChild VARCHAR(4000);
SET oTemp = '';
SET oTempChild = CAST(orgid AS CHAR);
WHILE oTempChild IS NOT NULL
DO
SET oTemp = CONCAT(oTemp,',',oTempChild);
SELECT GROUP_CONCAT(id) INTO oTempChild FROM 表名 WHERE FIND_IN_SET(parent_id,oTempChild) > 0;
END WHILE;
RETURN oTemp;
END;
;;
DELIMITER ;

使用

SELECT * FROM `表名` where find_in_set(id,getchildren(根节点ID))

树型数据递归查询

select a1.id,CONCAT('[',GROUP_CONCAT(
'{',
'"comments":',
'"',
a2.comments,
'"',
',' '"comment_num":',
'"',
a2.comment_num,
'"',
',' '"user_id":',
'"',
a2.user_id,
'"',
',' '"floor":',
a2.floor,'}'
),']') AS my_json from c2_comments a1 
left JOIN c2_comments a2 ON FIND_IN_SET(a2.id, getchildren(a1.id))
WHERE a1.parent_id = -1 GROUP by a1.id