mysql create function 通过子ID 查询 所有父ID

分析家

已知某产品XXX    种类名为： 化浊降脂剂
对应表种类表中  ctg_id=33  查询其种类的 所有父类种类 如下：
ctg_cname  ctg_id   ctg_fatherid
'化浊降脂剂', 33         8
'内科用药',   8          5
'中成药',     5          1
'药品',       1         null
显然满足  树结构
药品'
----中成药'
----------内科用药'
-----------------化浊降脂剂'
根据产品 得到所有的 产品种类名称（父亲到儿子）
开始比较笨的办法 查询 sql

SELECT ctg_cname ,ctg_id FROM product_category   WHERE ctg_id in (33,)
UNION
SELECT a.ctg_cname ,a.ctg_id FROM product_category a  WHERE  a.ctg_id=(SELECT ctg_fatherid FROM product_category   WHERE ctg_id=33 )
UNION
SELECT b.ctg_cname ,b.ctg_id FROM product_category b  WHERE  b.ctg_id=(SELECT ctg_fatherid FROM product_category a  WHERE  a.ctg_id=(SELECT ctg_fatherid FROM product_category   WHERE ctg_id=33 ))
UNION
SELECT c.ctg_cname ,c.ctg_id FROM product_category c  WHERE  c.ctg_id=(SELECT ctg_fatherid FROM product_category b  WHERE  b.ctg_id=(SELECT ctg_fatherid FROM product_category   WHERE  ctg_id=(SELECT ctg_fatherid FROM product_category   WHERE ctg_id=33 )) )


后来 设想  如果可以 一次全部拿到其 种类和父种类 ID 一次查询
SELECT ctg_cname ,ctg_id FROM product_category   WHERE ctg_id in (33, 父亲IDS,...)

那么效果不是更好。
想到了  create function 调用 递归调用 自己 输出所有  ids

// 发现递归 调用 貌似 mysql不支持
//  也了测试方法  调用失败  后放弃
DELIMITER $$
CREATE FUNCTION getPatherCategory (id INT,str char(20)) RETURNS   CHAR(50)
BEGIN
DECLARE fid INT default -1;
SET fid=(SELECT ctg_fatherid FROM product_category  WHERE ctg_id=id);
IF fid > 0 THEN
SET str=concat(str,',',fid,',',id); RETURN getPatherCategory(fid,str);
ELSE SET str=concat(str,',',id);
END IF;
RETURN str;
END $$


//改用如下方法  成功
//*_*！
DELIMITER $$
CREATE FUNCTION getPatherCategory (id INT) RETURNS   CHAR(255)
BEGIN
DECLARE fid INT default 1;
DECLARE str CHAR(255) default id;
WHILE id>0 DO   
SET fid=(SELECT ctg_fatherid FROM product_category  WHERE ctg_id=id);
IF fid > 0 THEN
SET str=concat(str,',',fid);
SET id=fid;
ELSE SET id=fid;
END IF;
END WHILE;  
RETURN str;
END $$


用到了 while  if  调用
使用到的 sql

show function status;  
drop function getPatherCategory
select getPatherCategory(33)

输出
'33,8,5,1'
-----------------\
可能会遇到一些问题  在创见 function的时候
创建function时
出错信息：
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
原因：
这是我们开启了bin-log, 我们就必须指定我们的函数是否是
1 DETERMINISTIC 不确定的
2 NO SQL 没有SQl语句，当然也不会修改数据
3 READS SQL DATA 只是读取数据，当然也不会修改数据
4 MODIFIES SQL DATA 要修改数据
5 CONTAINS SQL 包含了SQL语句
其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。
简单修改一下：

DELIMITER $$
CREATE FUNCTION getPatherCategory (id INT) RETURNS   CHAR(255) READS SQL DATA
BEGIN
DECLARE fid INT default 1;
DECLARE str CHAR(255) default id;
WHILE id>0 DO   
SET fid=(SELECT ctg_fatherid FROM product_category  WHERE ctg_id=id);
IF fid > 0 THEN
SET str=concat(str,',',fid);
SET id=fid;
ELSE SET id=-1;
END IF;
END WHILE;  
RETURN str;
END $$


---------------------------/
在创建查询的时候 不能直接执行

SELECT ctg_cname ,ctg_id FROM product_category   WHERE ctg_id in (getPatherCategory (33));
//33 为程序传进的ID

比较郁闷只能  先 select getPatherCategory (33);   再组装ID入SQL中查询
目前没有找到更合适的办法，对于 树结构数据查询的方法， 你有好办法么，最好一条SQL就可以搞定全部  ？