mysql 分类查询_MySQL 查询一分类所有的子分类

MySQL 查询一分类所有的子分类CREATE FUNCTION `getChildLst`(rootId INT)

RETURNS varchar(1000)

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(cid) INTO sTempChd FROM zz_category where FIND_IN_SET(pid,sTempChd)>0;

END WHILE;

RETURN sTemp;

END

有可能会遇到 This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary 的问题，只需要在MySQL中执行如下命令就可以了：

set global log_bin_trust_function_creators=TRUE;

查询的SQL语句：

select getChildLst(133) cids;

结果：

+---------------------------+

| cids |

+---------------------------+

| $,133,137,141,142,143,144 |

+---------------------------+