递归查询的几种方式

拼搏现实的明天。 2022-12-16 13:20 560阅读 0赞

# Mybatis中实现递归查询 #

## 创建数据 ##

DROP TABLE IF EXISTS `sys_depart`;
    CREATE TABLE `sys_depart` (
      `id` varchar(32) NOT NULL COMMENT 'ID',
      `parent_id` varchar(32) DEFAULT NULL COMMENT '父机构ID',
      `depart_name` varchar(100) NOT NULL COMMENT '机构/部门名称',
      `depart_name_en` varchar(500) DEFAULT NULL COMMENT '英文名',
      `depart_name_abbr` varchar(500) DEFAULT NULL COMMENT '缩写',
      `depart_order` int(11) DEFAULT '0' COMMENT '排序',
      `description` varchar(500) DEFAULT NULL COMMENT '描述',
      `org_category` varchar(10) NOT NULL DEFAULT '1' COMMENT '机构类别 1组织机构，2岗位',
      `org_type` varchar(10) DEFAULT NULL COMMENT '机构类型 1一级部门 2子部门',
      `org_code` varchar(64) NOT NULL COMMENT '机构编码',
      `mobile` varchar(32) DEFAULT NULL COMMENT '手机号',
      `fax` varchar(32) DEFAULT NULL COMMENT '传真',
      `address` varchar(100) DEFAULT NULL COMMENT '地址',
      `memo` varchar(500) DEFAULT NULL COMMENT '备注',
      `status` varchar(1) DEFAULT NULL COMMENT '状态（1启用，0不启用）',
      `del_flag` varchar(1) DEFAULT NULL COMMENT '删除状态（0，正常，1已删除）',
      `create_by` varchar(32) DEFAULT NULL COMMENT '创建人',
      `create_time` datetime DEFAULT NULL COMMENT '创建日期',
      `update_by` varchar(32) DEFAULT NULL COMMENT '更新人',
      `update_time` datetime DEFAULT NULL COMMENT '更新日期',
      PRIMARY KEY (`id`) USING BTREE,
      UNIQUE KEY `uniq_depart_org_code` (`org_code`) USING BTREE,
      KEY `index_depart_parent_id` (`parent_id`) USING BTREE,
      KEY `index_depart_depart_order` (`depart_order`) USING BTREE,
      KEY `index_depart_org_code` (`org_code`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='组织机构表';
    
    -- ----------------------------
    -- Records of sys_depart
    -- ----------------------------
    INSERT INTO `sys_depart` VALUES ('2c9533f930b346519026a6f7c5f94fe8', '9b88ba1475ef448dbed32a6ad95d9989', '四级市场', null, null, '0', null, '2', '5', 'A01A03A01A01A01', null, null, null, null, null, '0', 'admin', '2020-10-20 16:28:07', null, null);
    INSERT INTO `sys_depart` VALUES ('4f1765520d6346f9bd9c79e2479e5b12', 'c6d7cb4deeac411cb3384b1b31278596', '市场部', null, null, '0', null, '1', '2', 'A01A03', null, null, null, null, null, '0', 'admin', '2019-02-20 17:15:34', 'admin', '2019-02-26 16:36:18');
    INSERT INTO `sys_depart` VALUES ('5159cde220114246b045e574adceafe9', '6d35e179cd814e3299bd588ea7daed3f', '研发部', null, null, '0', null, '1', '2', 'A02A02', null, null, null, null, null, '0', 'admin', '2019-02-26 16:44:38', 'admin', '2019-03-07 09:36:53');
    INSERT INTO `sys_depart` VALUES ('57197590443c44f083d42ae24ef26a2c', 'c6d7cb4deeac411cb3384b1b31278596', '研发部', null, null, '0', null, '1', '2', 'A01A05', null, null, null, null, null, '0', 'admin', '2019-02-21 16:14:41', 'admin', '2019-03-27 19:05:49');
    INSERT INTO `sys_depart` VALUES ('63775228b7b041a99825f79760590b7d', '57197590443c44f083d42ae24ef26a2c', '研发经理', null, null, '0', null, '3', '3', 'A01A05A01', null, null, null, null, null, '0', 'admin', '2020-05-02 15:29:09', null, null);
    INSERT INTO `sys_depart` VALUES ('648bd701e1f64984acd24222e725b8b6', '4f1765520d6346f9bd9c79e2479e5b12', '市场部', null, null, '0', null, '2', '3', 'A01A03A01', null, null, null, null, null, '0', 'admin', '2020-10-20 13:40:14', null, null);
    INSERT INTO `sys_depart` VALUES ('6d35e179cd814e3299bd588ea7daed3f', '', '北京互动', null, null, '0', null, '1', '1', 'A02', null, null, null, null, null, '0', 'admin', '2019-02-26 16:36:39', 'admin', '2020-05-02 18:21:22');
    INSERT INTO `sys_depart` VALUES ('743ba9dbdc114af8953a11022ef3096a', 'f28c6f53abd841ac87ead43afc483433', '财务部', null, null, '0', null, '1', '2', 'A03A01', null, null, null, null, null, '0', 'admin', '2019-03-22 16:45:43', null, null);
    INSERT INTO `sys_depart` VALUES ('9b88ba1475ef448dbed32a6ad95d9989', '648bd701e1f64984acd24222e725b8b6', '三级市场', null, null, '0', null, '2', '4', 'A01A03A01A01', null, null, null, null, null, '0', 'admin', '2020-10-20 16:27:58', null, null);
    INSERT INTO `sys_depart` VALUES ('a7d7e77e06c84325a40932163adcdaa6', '6d35e179cd814e3299bd588ea7daed3f', '财务部', null, null, '0', null, '1', '2', 'A02A01', null, null, null, null, null, '0', 'admin', '2019-02-26 16:36:47', 'admin', '2019-02-26 16:37:25');
    INSERT INTO `sys_depart` VALUES ('c6d7cb4deeac411cb3384b1b31278596', '', '北京软件', null, null, '0', null, '1', '1', 'A01', null, null, null, null, null, '0', 'admin', '2019-02-11 14:21:51', 'admin', '2020-05-02 18:21:27');

## entity ##

/**
     * <p>
     * 部门表
     * <p>
     * 
     * @Author Steve
     * @Since  2019-01-22
     */
    @Data
    public class SysDepartT implements Serializable {
        private static final long serialVersionUID = 1L;
        
    	/**ID*/
    	@TableId(type = IdType.ASSIGN_ID)
    	private String id;
    	/**父机构ID*/
    	private String parentId;
    	/**机构/部门名称*/
    	@Excel(name="机构/部门名称",width=15)
    	private String departName;
    	/**英文名*/
    	@Excel(name="英文名",width=15)
    	private String departNameEn;
    	/**缩写*/
    	private String departNameAbbr;
    	/**排序*/
    	@Excel(name="排序",width=15)
    	private Integer departOrder;
    	/**描述*/
    	@Excel(name="描述",width=15)
    	private String description;
    	/**机构类别 1组织机构，2岗位*/
    	@Excel(name="机构类别",width=15,dicCode="org_category")
    	private String orgCategory;
    	/**机构类型*/
    	private String orgType;
    	/**机构编码*/
    	@Excel(name="机构编码",width=15)
    	private String orgCode;
    	/**手机号*/
    	@Excel(name="手机号",width=15)
    	private String mobile;
    	/**传真*/
    	@Excel(name="传真",width=15)
    	private String fax;
    	/**地址*/
    	@Excel(name="地址",width=15)
    	private String address;
    	/**备注*/
    	@Excel(name="备注",width=15)
    	private String memo;
    	/**状态（1启用，0不启用）*/
    	@Dict(dicCode = "depart_status")
    	private String status;
    	/**删除状态（0，正常，1已删除）*/
    	@Dict(dicCode = "del_flag")
    	private String delFlag;
    	/**创建人*/
    	private String createBy;
    	/**创建日期*/
    	@JsonFormat(timezone = "GMT+8",pattern = "yyyy-MM-dd HH:mm:ss")
        @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
    	private Date createTime;
    	/**更新人*/
    	private String updateBy;
    	/**更新日期*/
    	@JsonFormat(timezone = "GMT+8",pattern = "yyyy-MM-dd HH:mm:ss")
        @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
    	private Date updateTime;
    
    	private List<SysDepartT> sysDeparts = new ArrayList<>();
    	
    	/**
    	 * 重写equals方法
    	 */
        @Override
        public boolean equals(Object o) {
            if (this == o) {
    			return true;
    		}
            if (o == null || getClass() != o.getClass()) {
    			return false;
    		}
            if (!super.equals(o)) {
    			return false;
    		}
            SysDepartT depart = (SysDepartT) o;
            return Objects.equals(id, depart.id) &&
                    Objects.equals(parentId, depart.parentId) &&
                    Objects.equals(departName, depart.departName) &&
                    Objects.equals(departNameEn, depart.departNameEn) &&
                    Objects.equals(departNameAbbr, depart.departNameAbbr) &&
                    Objects.equals(departOrder, depart.departOrder) &&
                    Objects.equals(description, depart.description) &&
                    Objects.equals(orgCategory, depart.orgCategory) &&
                    Objects.equals(orgType, depart.orgType) &&
                    Objects.equals(orgCode, depart.orgCode) &&
                    Objects.equals(mobile, depart.mobile) &&
                    Objects.equals(fax, depart.fax) &&
                    Objects.equals(address, depart.address) &&
                    Objects.equals(memo, depart.memo) &&
                    Objects.equals(status, depart.status) &&
                    Objects.equals(delFlag, depart.delFlag) &&
                    Objects.equals(createBy, depart.createBy) &&
                    Objects.equals(createTime, depart.createTime) &&
                    Objects.equals(updateBy, depart.updateBy) &&
                    Objects.equals(updateTime, depart.updateTime);
        }
    
        /**
         * 重写hashCode方法
         */
        @Override
        public int hashCode() {
    
            return Objects.hash(super.hashCode(), id, parentId, departName, 
            		departNameEn, departNameAbbr, departOrder, description,orgCategory, 
            		orgType, orgCode, mobile, fax, address, memo, status, 
            		delFlag, createBy, createTime, updateBy, updateTime);
        }
    }

## Controller ##

/**
    	  * 通过id查询
    	  * @param id
    	  * @return
    	  */
    	 @AutoLog(value = "递归一")
    	 @ApiOperation(value="递归一", notes="递归")
    	 @GetMapping(value = "/getDepartList")
    	 public ResponseEntityT<List<SysDepartT>> getDepartList(@RequestParam(name="id",required=true) String id) {
    		 return ResponseEntityT.OK(sysDepartService.getDepartList(id));
    	 }

## service ##

List<SysDepartT> getDepartList(String id);

## ServiceImpl ##

@Override
    	public List<SysDepartT> getDepartList(String id) {
    		return baseMapper.getDepartList(id);
    	}

## Mapper ##

List<SysDepartT> getDepartList(@Param("departId") String departId);

## xml ##

<resultMap id="getSelf" type="org.jeecg.modules.system.entity.SysDepartT">
            <id column="id" property="id"></id>
            <collection property="sysDeparts" select="getDepartList" column="id"></collection>       
        </resultMap>
    
        <select id="getDepartList" resultMap="getSelf">
            select * from sys_depart where del_flag = '0' AND parent_id=#{departId}
        </select>

### xml 传递多值 ###

<resultMap id="getSelf" type="org.jeecg.modules.ai.DTO.AiColumnList">
            <result column="id" property="id"/>
            <result column="column_type" property="columnType"/>
            <result column="pid" property="pid"/>
            <collection property="aiColumnLists" select="getColumnList" column="{pid=id,column_type=column_type}">
                <result column="pid" property="pid"/>
                <result column="column_type" property="columnType"/>
            </collection>
        </resultMap>
    
        <select id="getColumnList" resultMap="getSelf"  parameterType="java.util.Map" >
            select id 'key',column_name title,ai_column.* from ai_column where status=1 AND pid=#{pid}
                <if test="column_type!=null and column_type!=''">
                    AND column_type=#{column_type}
                </if>
            ORDER BY column_type,sorting,create_time
        </select>
    
    
    
    @Data
    public class AiColumnList {
    
        /**主键*/
        @ApiModelProperty(value = "主键")
        private String key;
        @ApiModelProperty(value = "栏目名称")
        private String title;
    	/**主键*/
        @ApiModelProperty(value = "主键")
        private String id;
    	/**栏目类型*/
        @ApiModelProperty(value = "栏目类型")
        private String columnType;
    	/**上级ID（顶级0）*/
        @ApiModelProperty(value = "上级ID（顶级0）")
        private String pid;
    	/**外链地址*/
        @ApiModelProperty(value = "外链地址")
        private String outsideChainUrl;
        /**图标[压缩后]*/
        @ApiModelProperty(value = "图标[压缩后]")
        private String iocReduce;
    	/**排序*/
    	@Excel(name = "排序", width = 15)
        @ApiModelProperty(value = "排序")
        private Integer sorting;
    
    	private List<AiColumnList> aiColumnLists;
    
    }

## 执行结果 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Rhbmdjdg_size_16_color_FFFFFF_t_70][]

{
    	"result": [{
    		"id": "4f1765520d6346f9bd9c79e2479e5b12",
    		"parentId": "c6d7cb4deeac411cb3384b1b31278596",
    		"departName": "市场部",
    		"departNameEn": null,
    		"departNameAbbr": null,
    		"departOrder": 0,
    		"description": null,
    		"orgCategory": "1",
    		"orgType": "2",
    		"orgCode": "A01A03",
    		"mobile": null,
    		"fax": null,
    		"address": null,
    		"memo": null,
    		"status": null,
    		"delFlag": "0",
    		"createBy": "admin",
    		"createTime": "2019-02-20 17:15:34",
    		"updateBy": "admin",
    		"updateTime": "2019-02-26 16:36:18",
    		"sysDeparts": [{
    			"id": "648bd701e1f64984acd24222e725b8b6",
    			"parentId": "4f1765520d6346f9bd9c79e2479e5b12",
    			"departName": "市场部",
    			"departNameEn": null,
    			"departNameAbbr": null,
    			"departOrder": 0,
    			"description": null,
    			"orgCategory": "2",
    			"orgType": "3",
    			"orgCode": "A01A03A01",
    			"mobile": null,
    			"fax": null,
    			"address": null,
    			"memo": null,
    			"status": null,
    			"delFlag": "0",
    			"createBy": "admin",
    			"createTime": "2020-10-20 13:40:14",
    			"updateBy": null,
    			"updateTime": null,
    			"sysDeparts": [{
    				"id": "9b88ba1475ef448dbed32a6ad95d9989",
    				"parentId": "648bd701e1f64984acd24222e725b8b6",
    				"departName": "三级市场",
    				"departNameEn": null,
    				"departNameAbbr": null,
    				"departOrder": 0,
    				"description": null,
    				"orgCategory": "2",
    				"orgType": "4",
    				"orgCode": "A01A03A01A01",
    				"mobile": null,
    				"fax": null,
    				"address": null,
    				"memo": null,
    				"status": null,
    				"delFlag": "0",
    				"createBy": "admin",
    				"createTime": "2020-10-20 16:27:58",
    				"updateBy": null,
    				"updateTime": null,
    				"sysDeparts": [{
    					"id": "2c9533f930b346519026a6f7c5f94fe8",
    					"parentId": "9b88ba1475ef448dbed32a6ad95d9989",
    					"departName": "四级市场",
    					"departNameEn": null,
    					"departNameAbbr": null,
    					"departOrder": 0,
    					"description": null,
    					"orgCategory": "2",
    					"orgType": "5",
    					"orgCode": "A01A03A01A01A01",
    					"mobile": null,
    					"fax": null,
    					"address": null,
    					"memo": null,
    					"status": null,
    					"delFlag": "0",
    					"createBy": "admin",
    					"createTime": "2020-10-20 16:28:07",
    					"updateBy": null,
    					"updateTime": null,
    					"sysDeparts": []
    				}]
    			}]
    		}]
    	}, {
    		"id": "57197590443c44f083d42ae24ef26a2c",
    		"parentId": "c6d7cb4deeac411cb3384b1b31278596",
    		"departName": "研发部",
    		"departNameEn": null,
    		"departNameAbbr": null,
    		"departOrder": 0,
    		"description": null,
    		"orgCategory": "1",
    		"orgType": "2",
    		"orgCode": "A01A05",
    		"mobile": null,
    		"fax": null,
    		"address": null,
    		"memo": null,
    		"status": null,
    		"delFlag": "0",
    		"createBy": "admin",
    		"createTime": "2019-02-21 16:14:41",
    		"updateBy": "admin",
    		"updateTime": "2019-03-27 19:05:49",
    		"sysDeparts": [{
    			"id": "63775228b7b041a99825f79760590b7d",
    			"parentId": "57197590443c44f083d42ae24ef26a2c",
    			"departName": "研发经理",
    			"departNameEn": null,
    			"departNameAbbr": null,
    			"departOrder": 0,
    			"description": null,
    			"orgCategory": "3",
    			"orgType": "3",
    			"orgCode": "A01A05A01",
    			"mobile": null,
    			"fax": null,
    			"address": null,
    			"memo": null,
    			"status": null,
    			"delFlag": "0",
    			"createBy": "admin",
    			"createTime": "2020-05-02 15:29:09",
    			"updateBy": null,
    			"updateTime": null,
    			"sysDeparts": []
    		}]
    	}],
    	"message": "请求成功",
    	"code": 200,
    	"success": true
    }

# MySQL 直接查询 #

## SQL ##

select * from sys_depart where del_flag = '0' and org_code like concat((select org_code from sys_depart where id='c6d7cb4deeac411cb3384b1b31278596'),'%') ORDER BY org_code

## 显示结果 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Rhbmdjdg_size_16_color_FFFFFF_t_70 1][]

# MySQL函数实现递归查询 #

出错信息：

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指定一个参数。

解决方法：

SQL code

show variables like 'log_bin_trust_function_creators';

\+\--\-------------------------------+-------+

| Variable\_name                   | Value |

\+\--\-------------------------------+-------+

| log\_bin\_trust\_function\_creators | OFF   |

\+\--\-------------------------------+-------+

set global log_bin_trust_function_creators=1;

show variables like 'log_bin_trust_function_creators';

\+\--\-------------------------------+-------+

| Variable\_name                   | Value |

\+\--\-------------------------------+-------+

| log\_bin\_trust\_function\_creators | ON    |

\+\--\-------------------------------+-------+

这样添加了参数以后，如果mysqld重启，那个参数又会消失，因此记得在my.cnf配置文件中添加：  
log\_bin\_trust\_function\_creators=1

## 创建数据 ##

SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for t_areainfo
    -- ----------------------------
    DROP TABLE IF EXISTS `t_areainfo`;
    CREATE TABLE `t_areainfo` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `level` int(11) DEFAULT NULL,
      `name` varchar(255) DEFAULT NULL,
      `parentId` int(11) DEFAULT NULL,
      `status` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8mb4;
    
    -- ----------------------------
    -- Records of t_areainfo
    -- ----------------------------
    INSERT INTO `t_areainfo` VALUES ('1', '0', '中国', '0', '0');
    INSERT INTO `t_areainfo` VALUES ('2', '0', '华北区', '1', '0');
    INSERT INTO `t_areainfo` VALUES ('3', '0', '华南区', '1', '0');
    INSERT INTO `t_areainfo` VALUES ('4', '0', '北京', '2', '0');
    INSERT INTO `t_areainfo` VALUES ('5', '0', '海淀区', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('6', '0', '丰台区', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('7', '0', '朝阳区', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('8', '0', '北京XX区1', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('9', '0', '北京XX区2', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('10', '0', '北京XX区3', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('11', '0', '北京XX区4', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('12', '0', '北京XX区5', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('13', '0', '北京XX区6', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('14', '0', '北京XX区7', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('15', '0', '北京XX区8', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('16', '0', '北京XX区9', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('17', '0', '北京XX区10', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('18', '0', '北京XX区11', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('19', '0', '北京XX区12', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('20', '0', '北京XX区13', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('21', '0', '北京XX区14', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('22', '0', '北京XX区15', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('23', '0', '北京XX区16', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('24', '0', '北京XX区17', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('25', '0', '北京XX区18', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('26', '0', '北京XX区19', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('27', '0', '北京XX区1', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('28', '0', '北京XX区2', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('29', '0', '北京XX区3', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('30', '0', '北京XX区4', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('31', '0', '北京XX区5', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('32', '0', '北京XX区6', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('33', '0', '北京XX区7', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('34', '0', '北京XX区8', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('35', '0', '北京XX区9', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('36', '0', '北京XX区10', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('37', '0', '北京XX区11', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('38', '0', '北京XX区12', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('39', '0', '北京XX区13', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('40', '0', '北京XX区14', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('41', '0', '北京XX区15', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('42', '0', '北京XX区16', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('43', '0', '北京XX区17', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('44', '0', '北京XX区18', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('45', '0', '北京XX区19', '4', '0');
    INSERT INTO `t_areainfo` VALUES ('46', '0', 'xx省1', '1', '0');
    INSERT INTO `t_areainfo` VALUES ('47', '0', 'xx省2', '1', '0');
    INSERT INTO `t_areainfo` VALUES ('48', '0', 'xx省3', '1', '0');
    INSERT INTO `t_areainfo` VALUES ('49', '0', 'xx省4', '1', '0');
    INSERT INTO `t_areainfo` VALUES ('50', '0', 'xx省5', '1', '0');
    INSERT INTO `t_areainfo` VALUES ('51', '0', 'xx省6', '1', '0');
    INSERT INTO `t_areainfo` VALUES ('52', '0', 'xx省7', '1', '0');
    INSERT INTO `t_areainfo` VALUES ('53', '0', 'xx省8', '1', '0');
    INSERT INTO `t_areainfo` VALUES ('54', '0', 'xx省9', '1', '0');
    INSERT INTO `t_areainfo` VALUES ('55', '0', 'xx省10', '1', '0');
    INSERT INTO `t_areainfo` VALUES ('56', '0', 'xx省11', '1', '0');
    INSERT INTO `t_areainfo` VALUES ('57', '0', 'xx省12', '1', '0');
    INSERT INTO `t_areainfo` VALUES ('58', '0', 'xx省13', '1', '0');
    INSERT INTO `t_areainfo` VALUES ('59', '0', 'xx省14', '1', '0');
    INSERT INTO `t_areainfo` VALUES ('60', '0', 'xx省15', '1', '0');
    INSERT INTO `t_areainfo` VALUES ('61', '0', 'xx省16', '1', '0');
    INSERT INTO `t_areainfo` VALUES ('62', '0', 'xx省17', '1', '0');
    INSERT INTO `t_areainfo` VALUES ('63', '0', 'xx省18', '1', '0');
    INSERT INTO `t_areainfo` VALUES ('64', '0', 'xx省19', '1', '0');

## 向下递归 ##

利用find\_in\_set()函数和group\_concat()函数实现递归查询：

DROP FUNCTION IF EXISTS queryChildrenAreaInfo;
    CREATE FUNCTION queryChildrenAreaInfo(areaId INT)
    RETURNS VARCHAR(4000)
    BEGIN
    DECLARE sTemp VARCHAR(4000);
    DECLARE sTempChd VARCHAR(4000);
    
    SET sTemp='$';
    SET sTempChd = CAST(areaId AS CHAR);
    
    WHILE sTempChd IS NOT NULL DO
    SET sTemp= CONCAT(sTemp,',',sTempChd);
    SELECT GROUP_CONCAT(id) INTO sTempChd FROM t_areainfo WHERE FIND_IN_SET(parentId,sTempChd)>0;
    END WHILE;
    RETURN sTemp;
    END;

### 调用方式 ###

SELECT queryChildrenAreaInfo(1);

![format_png][]

查询id为"4"下面的所有节点

SELECT * FROM t_areainfo WHERE FIND_IN_SET(id,queryChildrenAreaInfo(4));

![format_png 1][]

## 向上递归 ##

DROP FUNCTION IF EXISTS queryChildrenAreaInfo1;
    CREATE FUNCTION queryChildrenAreaInfo1(areaId INT)
    RETURNS VARCHAR(4000)
    BEGIN
    DECLARE sTemp VARCHAR(4000);
    DECLARE sTempChd VARCHAR(4000);
    
    SET sTemp='$';
    SET sTempChd = CAST(areaId AS CHAR);
    SET sTemp = CONCAT(sTemp,',',sTempChd);
    
    SELECT parentId INTO sTempChd FROM t_areainfo WHERE id = sTempChd;
    WHILE sTempChd <> 0 DO
    SET sTemp = CONCAT(sTemp,',',sTempChd);
    SELECT parentId INTO sTempChd FROM t_areainfo WHERE id = sTempChd;
    END WHILE;
    RETURN sTemp;
    END;

### 调用方式 ###

查询id为"7"的节点的所有上级节点

SELECT * from t_areainfo where FIND_IN_SET(id,queryChildrenAreaInfo1(7));

![format_png 2][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Rhbmdjdg_size_16_color_FFFFFF_t_70]: /images/20221123/aa42ba319c5c4897bf679fbf4505ee5b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Rhbmdjdg_size_16_color_FFFFFF_t_70 1]: /images/20221123/90768a38e7374482aebd51760b3b8fff.png
[format_png]: /images/20221123/dd2df2fa7cf449a7b661bc44c7895ebe.png
[format_png 1]: /images/20221123/4f7101a3841c4168898e5717c0a388b4.png
[format_png 2]: /images/20221123/a2a55c435db547a2b7aa48bf37e54690.png