不用寫預存過程，不用建立資料庫函數，一段sql就可以實作
SELECT ID.LEVEL, DATA.* FROM ( SELECT @ids AS _ids, ( SELECT @ids := GROUP_CONCAT( region_id ) FROM region WHERE FIND_IN_SET(parent_id, @ids ) ) AS cids, @l := @l + 1 AS LEVEL FROM region, ( SELECT @ids := 3, @l := 0 ) b WHERE @ids IS NOT NULL ) ID, region DATA WHERE FIND_IN_SET( DATA.region_id, ID._ids ) ORDER BY LEVEL
##測試測試
--创建测试环境 create table t_test( id int PRIMARY key, parent_id int, name varchar(200) ) insert t_test VALUES(1,null,"中国"); insert t_test VALUES(2,1,"华北"); insert t_test VALUES(3,2,"山西省"); insert t_test VALUES(4,2,"北京"); insert t_test VALUES(5,3,"临汾市"); insert t_test VALUES(6,4,"北京市"); insert t_test VALUES(7,5,"尧都区"); insert t_test VALUES(8,6,"朝阳区"); insert t_test VALUES(9,7,"解放西路"); insert t_test VALUES(10,8,"朝阳北路"); SELECT * FROM t_test;
SELECT ID.LEVEL, DATA.* FROM ( SELECT @ids AS _ids, ( SELECT @ids := GROUP_CONCAT( id ) FROM t_test WHERE FIND_IN_SET(parent_id, @ids ) ) AS cids, @l := @l + 1 AS LEVEL FROM t_test, ( SELECT @ids := 1, @l := 0 ) b WHERE @ids IS NOT NULL ) ID, t_test DATA WHERE FIND_IN_SET( DATA.id, ID._ids ) ORDER BY LEVEL
#id=3，查詢山西下邊有哪些地方
SELECT ID.LEVEL, DATA.* FROM ( SELECT @ids AS _ids, ( SELECT @ids := GROUP_CONCAT( id ) FROM t_test WHERE FIND_IN_SET(parent_id, @ids ) ) AS cids, @l := @l + 1 AS LEVEL FROM t_test, ( SELECT @ids := 3, @l := 0 ) b WHERE @ids IS NOT NULL ) ID, t_test DATA WHERE FIND_IN_SET( DATA.id, ID._ids ) ORDER BY LEVEL
#id=4，查詢北京下邊有哪些地方
##最後再從id=2 華北地區往下查詢
以上是MySql如何利用父id遞歸向下查詢子節點的詳細內容。更多資訊請關注PHP中文網其他相關文章！
