mysqlcreatefunction通过子ID查询所有父ID

mysqlcreatefunction通过子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å??å??ä?,æ??å?šå…?éƒ? ï,Ÿ