MySQL查询树形结构的数据

在项目中经常会遇到树形结构的数据，如何去根据需要去查询也显得尤为重要。

其中表结构如下：

CREATE TABLE `s_region` (
  `region_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '行政区编码',
  `region_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '行政区名称',
  `parent_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '上级行政区编码',
  `all_parent` varchar(4000) DEFAULT NULL COMMENT '所有父级节点的编码',
  PRIMARY KEY (`region_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

注：all_parent字段可根据实际需求决定是否使用。

添加数据

INSERT INTO `cmdp_auth`.`s_region`(`region_code`, `region_name`, `parent_code`, `all_parent`) VALUES ('110000', '北京市', '0', '110000');
INSERT INTO `cmdp_auth`.`s_region`(`region_code`, `region_name`, `parent_code`, `all_parent`) VALUES ('110100', '市辖区', '110000', '110000,110100');
INSERT INTO `cmdp_auth`.`s_region`(`region_code`, `region_name`, `parent_code`, `all_parent`) VALUES ('110101', '东城区', '110100', '110000,110100,110101');
INSERT INTO `cmdp_auth`.`s_region`(`region_code`, `region_name`, `parent_code`, `all_parent`) VALUES ('110102', '西城区', '110100', '110000,110100,110102');
INSERT INTO `cmdp_auth`.`s_region`(`region_code`, `region_name`, `parent_code`, `all_parent`) VALUES ('110105', '朝阳区', '110100', '110000,110100,110105');
INSERT INTO `cmdp_auth`.`s_region`(`region_code`, `region_name`, `parent_code`, `all_parent`) VALUES ('420000', '湖北省', '0', '420000');
INSERT INTO `cmdp_auth`.`s_region`(`region_code`, `region_name`, `parent_code`, `all_parent`) VALUES ('420100', '武汉市', '420000', '420000,420100');
INSERT INTO `cmdp_auth`.`s_region`(`region_code`, `region_name`, `parent_code`, `all_parent`) VALUES ('420103', '江汉区', '420100', '420000,420100,420103');
INSERT INTO `cmdp_auth`.`s_region`(`region_code`, `region_name`, `parent_code`, `all_parent`) VALUES ('420106', '武昌区', '420100', '420000,420100,420106');
INSERT INTO `cmdp_auth`.`s_region`(`region_code`, `region_name`, `parent_code`, `all_parent`) VALUES ('420111', '洪山区', '420100', '420000,420100,420111');
INSERT INTO `cmdp_auth`.`s_region`(`region_code`, `region_name`, `parent_code`, `all_parent`) VALUES ('420115', '江夏区', '420100', '420000,420100,420115');
INSERT INTO `cmdp_auth`.`s_region`(`region_code`, `region_name`, `parent_code`, `all_parent`) VALUES ('420200', '黄石市', '420000', '420000,420200');
INSERT INTO `cmdp_auth`.`s_region`(`region_code`, `region_name`, `parent_code`, `all_parent`) VALUES ('420204', '下陆区', '420200', '420000,420200,420204');
INSERT INTO `cmdp_auth`.`s_region`(`region_code`, `region_name`, `parent_code`, `all_parent`) VALUES ('420222', '阳新县', '420200', '420000,420200,420222');

1）需求1：查询当前节点的所有父级节点信息

set @tempCode='要查询的编码'; 
SELECT
    T2.* 
FROM
    (
    SELECT
        @R AS rid,
        ( SELECT @R := parent_code FROM s_region WHERE region_code = rid ) AS parent_code,
        @L := @L + 1 AS LVL 
    FROM
        ( SELECT @R := @tempCode, @L := 0) VARS, s_region h
        WHERE @R >= 0 ) T1
        INNER JOIN s_region T2 ON T1.rid = T2.region_code 
ORDER BY
    T1.LVL DESC;

其中@tempCode为临时变量，是实际的mybatis的xml中，直接使用变量替换即可。

如查询北京市西城区的所有父级信息，查询结果如下：

2）需求2：查询当前节点的所有子级节点信息

set @tempCode='要查询的编码' ;   
select t3.* from (
    select t1.*,
           if(find_in_set(parent_code, @pids) > 0, @pids := concat(@pids, ',', region_code), 
                     if (t1.region_code = @tempCode,@tempCode,0)) as ischild
               from (
                     select t.* from s_region t
                ) t1,
           (select @pids :=  @tempCode) t2
        ) t3 where ischild != 0

如要查询湖北省武汉市的所有子节点：

当然上述在查询子节点时，当树形结构子节点数据太大时，查询效率会降低

这里字段all_arent就派上用场了，这个字段存的是所有父级和自身节点的编码，便于查询。此字段需要根据实际需求去决定是否使用，因为这种方式采用的是使用空间换时间。树形结构的数据类似一棵树，子节点是横向与扩展，会存在子节点的数据非常庞大。而此字段存储的是所有的父节点的编码，只要树的深度不是很大，那么其内容就不会很大。

select * from s_region where find_in_set('420100',all_parent)

当然提高了查询的效率，那么在数据更新时，是需要根据实际情况去更新all_parent字段的值。