MySql通过父id递归向下查询子节点

不用写存储过程，不用建数据库函数，一段sql就可以实现
不用写存储过程，不用建数据库函数，一段sql就可以实现
不用写存储过程，不用建数据库函数，一段sql就可以实现

SELECT
    ID.LEVEL,DATA.*FROM(SELECT@idsAS _ids,(SELECT@ids := GROUP_CONCAT( region_id )FROM region WHERE FIND_IN_SET(parent_id,@ids))AS cids,@l :=@l+1ASLEVELFROM
        region,(SELECT@ids :=3,@l :=0) b 
    WHERE@idsISNOTNULL) ID,
    region DATAWHERE
    FIND_IN_SET(DATA.region_id, ID._ids )ORDERBYLEVEL

测试

--创建测试环境createtable t_test(
    id intPRIMARYkey,
    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;

测试数据展示

查询 id=1，查询中国下边有哪些地方

SELECT
    ID.LEVEL,DATA.*FROM(SELECT@idsAS _ids,(SELECT@ids := GROUP_CONCAT( id )FROM t_test WHERE FIND_IN_SET(parent_id,@ids))AS cids,@l :=@l+1ASLEVELFROM
        t_test,(SELECT@ids :=1,@l :=0) b 
    WHERE@idsISNOTNULL) ID,
    t_test DATAWHERE
    FIND_IN_SET(DATA.id, ID._ids )ORDERBYLEVEL

id=3，查询山西下边有哪些地方

SELECT
    ID.LEVEL,DATA.*FROM(SELECT@idsAS _ids,(SELECT@ids := GROUP_CONCAT( id )FROM t_test WHERE FIND_IN_SET(parent_id,@ids))AS cids,@l :=@l+1ASLEVELFROM
        t_test,(SELECT@ids :=3,@l :=0) b 
    WHERE@idsISNOTNULL) ID,
    t_test DATAWHERE
    FIND_IN_SET(DATA.id, ID._ids )ORDERBYLEVEL

id=4，查询北京下边有哪些地方

最后再从 id=2 华北地区往下查询