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 |
+---------------------------+

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注