ibeautiful
试试这些:表定义:DROP TABLE IF EXISTS category;CREATE TABLE category ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20), parent_id INT, CONSTRAINT fk_category_parent FOREIGN KEY (parent_id) REFERENCES category (id)) engine=innodb;实验行:INSERT INTO category VALUES(19, 'category1', NULL),(20, 'category2', 19),(21, 'category3', 20),(22, 'category4', 21),(23, 'categoryA', 19),(24, 'categoryB', 23),(25, 'categoryC', 23),(26, 'categoryD', 24);递归存储过程:DROP PROCEDURE IF EXISTS getpath;DELIMITER $$CREATE PROCEDURE getpath(IN cat_id INT, OUT path TEXT)BEGIN DECLARE catname VARCHAR(20); DECLARE temppath TEXT; DECLARE tempparent INT; SET max_sp_recursion_depth = 255; SELECT name, parent_id FROM category WHERE id=cat_id INTO catname, tempparent; IF tempparent IS NULL THEN SET path = catname; ELSE CALL getpath(tempparent, temppath); SET path = CONCAT(temppath, '/', catname); END IF;END$$DELIMITER ;存储过程的包装函数:DROP FUNCTION IF EXISTS getpath;DELIMITER $$CREATE FUNCTION getpath(cat_id INT) RETURNS TEXT DETERMINISTICBEGIN DECLARE res TEXT; CALL getpath(cat_id, res); RETURN res;END$$DELIMITER ;选择示例:SELECT id, name, getpath(id) AS path FROM category;输出:+----+-----------+-----------------------------------------+| id | name | path |+----+-----------+-----------------------------------------+| 19 | category1 | category1 || 20 | category2 | category1/category2 || 21 | category3 | category1/category2/category3 || 22 | category4 | category1/category2/category3/category4 || 23 | categoryA | category1/categoryA || 24 | categoryB | category1/categoryA/categoryB || 25 | categoryC | category1/categoryA/categoryC || 26 | categoryD | category1/categoryA/categoryB/categoryD |+----+-----------+-----------------------------------------+过滤具有特定路径的行:SELECT id, name, getpath(id) AS path FROM category HAVING path LIKE 'category1/category2%';输出:+----+-----------+-----------------------------------------+| id | name | path |+----+-----------+-----------------------------------------+| 20 | category2 | category1/category2 || 21 | category3 | category1/category2/category3 || 22 | category4 | category1/category2/category3/category4 |+----+-----------+-----------------------------------------+