AnthonyZero's Bolg

MySQL递归遍历树

发表于 | 分类于

SQL递归

众所周知，目前的Mysql版本中并不支持直接的递归查询，但是通过递归到迭代转化的思路，还是可以在一句SQL内实现树的递归查询的。这个得益于Mysql允许在SQL存储过程内使用@变量

创建表 机构信息

CREATE TABLE def_org_info (
org_id bigint(20) NOT NULL COMMENT ‘机构ID’,
org_type int(11) NOT NULL COMMENT ‘机构类型 1 企业 2 部门 3 分公司 4 配送中心 5 门店 6 车间 7 质检部 8 仓库’,
up_org_id bigint(20) NOT NULL COMMENT ‘上级机构ID,顶级机构填0’,
org_no varchar(32) NOT NULL COMMENT ‘编号’,
org_name varchar(64) NOT NULL COMMENT ‘名称’,
org_simple_name varchar(64) DEFAULT NULL COMMENT ‘简称’,
org_pinyin varchar(64) DEFAULT NULL COMMENT ‘机构拼音’,

area_id int(11) DEFAULT NULL COMMENT ‘区域, 数据字典’,
province_id bigint(20) DEFAULT NULL COMMENT ‘省份ID’,
city_id bigint(20) DEFAULT NULL COMMENT ‘城市ID’,
address varchar(256) DEFAULT NULL COMMENT ‘地址’,
tel_phone varchar(32) DEFAULT NULL COMMENT ‘电话’,
rela_name varchar(32) DEFAULT NULL COMMENT ‘联系人’,
email varchar(32) DEFAULT NULL COMMENT ‘邮箱’,
is_depart_item int(11) DEFAULT NULL COMMENT ‘是否启用部门与品项关系设定,org_type=2时有值 1 启用 0 不启用’,
is_join int(11) DEFAULT NULL COMMENT ‘是否加盟 org_type=4,5是有值 1 是 0 否’,
is_area_center int(11) DEFAULT NULL COMMENT ‘是否区域配送中心 org_type=4时有值 1 是 0 否’,
account_period bigint(20) DEFAULT NULL COMMENT ‘启用会计期 org_type=4,5时有值’,
abc_cate int(11) DEFAULT NULL COMMENT ‘ABC分类,org_type=5时有值’,
is_store int(11) DEFAULT NULL COMMENT ‘是否可作为店间调拨发货门店 1 是 0 否’,
is_receive int(11) DEFAULT NULL COMMENT ‘是否启用应收应付 org_type=4,5时有值 1 是 0 否’,
is_online int(11) DEFAULT NULL COMMENT ‘是否线上门店 1 是 0 否’,
warehouses_type int(11) DEFAULT NULL COMMENT ‘仓库类型 org_type=8时有值 1 档口库 2 非档口库’,
is_warehouses_item int(11) DEFAULT NULL COMMENT ‘是否启用仓库与品项关系设定 org_type=8时有值 1 是 0 否’,
state int(11) NOT NULL COMMENT ‘启用标志 1 启用 0 不启用’,
create_time datetime NOT NULL COMMENT ‘创建时间’,
create_mem_id bigint(20) NOT NULL COMMENT ‘创建人’,
modify_time datetime DEFAULT NULL COMMENT ‘最后修改时间’,
modify_mem_id bigint(20) DEFAULT NULL COMMENT ‘最后修改人’,
delete_mark int(11) NOT NULL COMMENT ‘删除标志 0 未删除 1 已删除（移到回收站）2 彻底删除’,
PRIMARY KEY (org_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’机构信息表’

导入数据

Alt text

创建存储过程

存储过程的入参为机构Id orgid int, 返回值为varchar(4000)：机构树（包括树头）机构ID
以逗号分隔的字符串。利用find_in_set()函数和group_concat()函数实现递归查询

BEGIN
    #根据父id递归查询所有子节点
    DECLARE oTemp VARCHAR(4000);
    DECLARE oTempChild VARCHAR(4000);

    SET oTemp = '';
    SET oTempChild = CAST(orgid AS CHAR);

    WHILE oTempChild IS NOT NULL
    DO
    SET oTemp = CONCAT(oTemp,',',oTempChild);
    #得到的结果赋值给变量oTempChild                             
    SELECT GROUP_CONCAT(org_id) INTO oTempChild FROM 
    def_org_info WHERE FIND_IN_SET(up_org_id,oTempChild) > 0;
                       #只有当机构的上级机构存在于oTempChild
    END WHILE;
    RETURN oTemp;
END

FIND_IN_SET(str,strlist)函数：查询字段(strlist)中包含(str)的结果，返回结果为null或记录。 strlist参数以,分隔 如 (1,2,6,8). Find_IN_SET 是精确匹配，查询的结果要小于like查询的结果。
GROUP_CONCAT函数是典型的字符串连接函数 例如select id,group_concat(name) from aa group by id; 根据Id分组之后 把每一组的name默认逗号分隔拼成字符串。

调用方式

调用存储过程返回某机构树的机构ID集合

Alt text

查询机构树（筛选出未启用和已经删除的机构）

Alt text