mysql递归查询

　

递归查询

父子查询： 根据父 id 查询下面所有子节点数据；子父查询： 根据子 id 查询上面所有父节点数据；

                                                                                              ————mysql递归查询

目录结构：

• 创建表并添加测试数据
  • 创建表
  • 添加数据
• 根据父id递归查询所有子节点
  • 创建函数
  • 根据函数查询
• 根据子id递归查询所有父节点
  • 写sql语句
• 根据组织机构名称模糊查询所有父节点
  • 创建函数
  • 根据函数查询

创建表，并添加测试数据

创建表

DROP TABLE IF exists vrv_org_tab;

CREATE TABLE vrv_org_tab (

id bigint(8) NOT NULL AUTO_INCREMENT,

org_name varchar(50) NOT NULL,

org_level int(4) NOT NULL DEFAULT ‘0’,

org_parent_id bigint(8) NOT NULL DEFAULT ‘0’,

PRIMARY KEY (id),

unique KEY unique_org_name (org_name)

) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

添加数据

INSERT INTO vrv_org_tab VALUES (‘1’, ‘北信源’, ‘1’, ‘0’);

INSERT INTO vrv_org_tab VALUES (‘2’, ‘北京’, ‘2’, ‘1’);

INSERT INTO vrv_org_tab VALUES (‘3’, ‘南京’, ‘2’, ‘1’);

INSERT INTO vrv_org_tab VALUES (‘4’, ‘武汉’, ‘2’, ‘1’);

INSERT INTO vrv_org_tab VALUES (‘5’, ‘上海’, ‘2’, ‘1’);

INSERT INTO vrv_org_tab VALUES (‘6’, ‘北京研发中心’, ‘3’, ‘2’);

INSERT INTO vrv_org_tab VALUES (‘7’, ‘南京研发中心’, ‘3’, ‘3’);

INSERT INTO vrv_org_tab VALUES (‘8’, ‘武汉研发中心’, ‘3’, ‘4’);

INSERT INTO vrv_org_tab VALUES (‘9’, ‘上海研发中心’, ‘3’, ‘5’);

INSERT INTO vrv_org_tab VALUES (‘10’, ‘北京EMM项目组’, ‘4’, ‘6’);

INSERT INTO vrv_org_tab VALUES (‘11’, ‘北京linkdd项目组’, ‘4’, ‘6’);

INSERT INTO vrv_org_tab VALUES (‘12’, ‘南京EMM项目组’, ‘4’, ‘7’);

INSERT INTO vrv_org_tab VALUES (‘13’, ‘南京linkdd项目组’, ‘4’, ‘7’);

INSERT INTO vrv_org_tab VALUES (‘14’, ‘武汉EMM项目组’, ‘4’, ‘8’);

INSERT INTO vrv_org_tab VALUES (‘15’, ‘武汉linkdd项目组’, ‘4’, ‘8’);

INSERT INTO vrv_org_tab VALUES (‘16’, ‘上海EMM项目组’, ‘4’, ‘9’);

INSERT INTO vrv_org_tab VALUES (‘17’, ‘上海linkdd项目组’, ‘4’, ‘9’);

select * from vrv_org_tab;

根据父id递归查询所有子节点

创建函数

create function getchildrenOrg(orgid INT)
returns varchar(4000)
BEGIN
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);
SELECT GROUP_CONCAT(id) INTO oTempChild FROM vrv_org_tab WHERE FIND_IN_SET(org_parent_id,oTempChild) > 0;
END WHILE;
RETURN oTemp;
END

根据函数查询

根据子id递归查询所有父节点

根据子id查询父节点就不那么麻烦了，不需要写递归函数，当然，你也可以写递归函数来查询。我这边提供的是不写函数的方式。请看代码

写sql语句

SELECT id,org_name,org_level,org_parent_id
    FROM ( 
        SELECT 
                @r AS _id, 
                (SELECT @r := org_parent_id FROM vrv_org_tab WHERE id = _id) AS parent_id, 
                 @l := @l + 1 AS lvl 
        FROM 
                (SELECT @r := 10000, @l := 0) vars, 
                vrv_org_tab h 
        WHERE @r <> 0) T1 
    JOIN vrv_org_tab T2 
    ON T1._id = T2.id
order BY id;

•  

注意：大家看到那个10000了吗，就是我们的子节点id。

注意：只支持单个查询，意思是不可以根据两个或者两个以上的子节点同时查询出所有父节点。我们可以看到，上面参数都是单个值进行递归查询的。

西面提供一个函数支持多个查询

根据组织机构名称模糊查询所有父节点

该功能常用于组织机构模糊搜索

创建函数

CREATE FUNCTION getParentOrgByOrgName(orgName VARCHAR(20))
RETURNS VARCHAR(4000)
BEGIN
    DECLARE sPid VARCHAR(1000);
    DECLARE sPidTemp VARCHAR(1000);
    DECLARE pid VARCHAR(1000);
    DECLARE count INT DEFAULT 0;
    DECLARE allpid VARCHAR(4000);

    SET sPidTemp = '';
    SELECT GROUP_CONCAT(DISTINCT(CAST(id AS CHAR))) INTO sPid 
    FROM vrv_org_tab WHERE org_name LIKE CONCAT('%',orgName,'%');

    SET allpid = '';
WHILE count = 0
DO
IF sPid IS NULL THEN
SET allpid = '-1';
SET count = 1;
ELSE
    SET pid = substring_INDEX(sPid,',',1);
    SET sPidTemp = CONCAT(sPidTemp,',',pid);
    IF LENGTH(pid) = LENGTH(sPid) THEN
        SET count = 1;
        SET sPid = SUBSTRING(sPid FROM LENGTH(SUBSTRING_INDEX(sPid,',',1)) FOR LENGTH(sPid)+1);
    ELSE
        SET sPid = SUBSTRING(sPid FROM LENGTH(SUBSTRING_INDEX(sPid,',',1))+2 FOR LENGTH(sPid)+1);
    END IF;
    SELECT GROUP_CONCAT(CAST(id AS CHAR)) INTO sPidTemp
            FROM ( 
                    SELECT 
                            @r AS _id, 
                            (SELECT @r := org_parent_id FROM vrv_org_tab WHERE id = _id) AS parent_id, 
                            @l := @l + 1 AS lvl 
                    FROM 
                            (SELECT @r := pid, @l := 0) vars, 
                            vrv_org_tab h 
                    WHERE @r <> 0) T1 
            JOIN vrv_org_tab T2 
            ON T1._id = T2.id;
    SET allpid = CONCAT_WS(',',pid,sPidTemp,allpid);
END IF;
END WHILE;
RETURN allpid;
END

根据函数查询

相关阅读

数据库设计工具MySQLWorkBench

   该工具为MySQL官方提供地址：http://dev.mysql.com/downloads/workbench/ 小伙伴们注意按自己的操作系统选择下载版本。 ·

MySQL错误代码大全

转自：https://www.cnblogs.com/JimCalark/p/7808575.html服务器错误代码和消息 服务器错误信息来自下述源文件： 错误消息信息列在s

高性能MySQL】第7章MySQL高级特性 下

7.10全文索引 通过关键字匹配进行查询过滤，基于相似度的查询 有自己独特的语法，没有索引可以工作，有了索引效率更高，全局搜索的索引

MySQL数据库中tinyint类型字段读取数据为true和false

数据库一个表中有一个tinyint类型的字段，值为0或者1，如果取出来的话，0会变成false，1会变成true。MySQL保存boolean值时用1代表TRUE，0代

mysql触发器

MySQL包含对触发器的支持。触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触