MySQL实现递归查询

2021-10-08 05:20:36

概述

前几日有客户咨询关于mysql实现递归查询的方法，当时简单了解了一下，觉得递归查询逻辑层面一种特殊查询方式。但是后来才发现这是一种很常见的查询需求，例如某些评论楼层的折叠显示、各类流程图等用递归查询都能实现。但是MySQL本身而言是没有实现递归查询功能，但是可以通过一些特殊的方法来实现此功能，本文就简单测试一些MySQL实现递归查询的方法

测试环境

测试环境是RDS for MySQL 5.7，测试的表的主要逻辑就是省份--城市--市区，为了实现这个逻辑，先要准备好表与数据，如下

测试表

CREATE TABLE `recursion_test` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `parent_id` int(11) NOT NULL,
 `name` varchar(32) NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='递归测试表';

主键递增，ID与父ID，然后就是名字。表的逻辑需要通过递归查询才能实现

插入测试数据

插入数据如下

INSERT INTO recursion_test VALUES(1,1,'浙江省');
INSERT INTO recursion_test VALUES(2,2,'江苏省');
INSERT INTO recursion_test VALUES(3,3,'安徽省');

INSERT INTO recursion_test VALUES(4,1,'杭州市');
INSERT INTO recursion_test VALUES(5,1,'宁波市');
INSERT INTO recursion_test VALUES(6,1,'金华市');


INSERT INTO recursion_test VALUES(7,2,'南京市');
INSERT INTO recursion_test VALUES(8,2,'苏州市');
INSERT INTO recursion_test VALUES(9,2,'徐州市');


INSERT INTO recursion_test VALUES(10,3,'合肥市');
INSERT INTO recursion_test VALUES(11,3,'芜湖市');
INSERT INTO recursion_test VALUES(12,3,'池州市');


INSERT INTO recursion_test VALUES(13,4,'西湖区');
INSERT INTO recursion_test VALUES(14,4,'滨江区');
INSERT INTO recursion_test VALUES(15,4,'余杭区');

INSERT INTO recursion_test VALUES(16,5,'海曙区');
INSERT INTO recursion_test VALUES(17,5,'江北区');
INSERT INTO recursion_test VALUES(18,5,'镇海区');

INSERT INTO recursion_test VALUES(19,6,'婺城区');
INSERT INTO recursion_test VALUES(20,6,'金东区');
INSERT INTO recursion_test VALUES(21,6,'永康市');

INSERT INTO recursion_test VALUES(22,7,'玄武区');
INSERT INTO recursion_test VALUES(23,7,'秦淮区');
INSERT INTO recursion_test VALUES(24,7,'建邺区');

INSERT INTO recursion_test VALUES(25,8,'沧浪区');
INSERT INTO recursion_test VALUES(26,8,'平江区');
INSERT INTO recursion_test VALUES(27,8,'虎丘区');

INSERT INTO recursion_test VALUES(28,9,'云龙区');
INSERT INTO recursion_test VALUES(29,9,'鼓楼区');
INSERT INTO recursion_test VALUES(30,9,'泉山区');

INSERT INTO recursion_test VALUES(31,10,'蜀山区');
INSERT INTO recursion_test VALUES(32,10,'庐阳区');
INSERT INTO recursion_test VALUES(33,10,'瑶海区');

INSERT INTO recursion_test VALUES(34,11,'镜湖区');
INSERT INTO recursion_test VALUES(35,11,'鸠江区');
INSERT INTO recursion_test VALUES(36,11,'弋江区');

INSERT INTO recursion_test VALUES(37,12,'贵池区');
INSERT INTO recursion_test VALUES(38,12,'九华山区');
INSERT INTO recursion_test VALUES(39,12,'青阳');

OK，准备好无趣的数据，开始试着做做递归查询

开始测试

使用表连接

若确定所需查询树的最大深度。则可以直接使用left join来实现，每有一级递归就做一次join。例如ID=父_ID,选取相应的字段就可以按照递归顺序查询出来
我们这个表的逻辑递归层只有三层，理论上只要做两次表连接即可查询，如下，查询

mysql>SELECT t1.name as '省份',t2.name as '城市',t3.name as '市区'
FROM recursion_test t1
LEFT JOIN recursion_test t2 ON t1.id = t2.parent_id
LEFT JOIN recursion_test t3 ON t2.id = t3.parent_id
WHERE t1.id = '1' and t2.id <> 1;
+----------------+----------------+----------------+
| 省份 | 城市 | 市区 |
+----------------+----------------+----------------+
| 浙江省 | 杭州市 | 西湖区 |
| 浙江省 | 杭州市 | 滨江区 |
| 浙江省 | 杭州市 | 余杭区 |
| 浙江省 | 宁波市 | 海曙区 |
| 浙江省 | 宁波市 | 江北区 |
| 浙江省 | 宁波市 | 镇海区 |
| 浙江省 | 金华市 | 婺城区 |
| 浙江省 | 金华市 | 金东区 |
| 浙江省 | 金华市 | 永康市 |
+----------------+----------------+----------------+

临时表+存储过程

第一个存储过程负责将每个节点的数据写到临时表中，递归查询到最底层的节点

CREATE PROCEDURE `findtestList`() 
 COMMENT '递归查询' 
BEGIN
  DECLARE v_test VARCHAR(20) DEFAULT '';
  DECLARE done INTEGER DEFAULT 0;
    -- 查询结果放入游标中
  DECLARE C_test CURSOR FOR SELECT d.id
                           FROM recursion_test d
                           WHERE d.parent_id = testId;
  DECLARE CONTINUE HANDLER FOR NOT found SET done=1;
  SET @@max_sp_recursion_depth = 10;
    
    -- 传入的组织id写入临时表
  INSERT INTO tmp_test VALUES (testId);
  OPEN C_test;
  FETCH C_test INTO v_test;
  WHILE (done=0)
  DO
        -- 递归调用，查找下级
    CALL findtestList(v_test);
    FETCH C_test INTO v_test;
  END WHILE;
  CLOSE C_test;
END

第二个存储过程是负责创建和删除临时表，并且调用第一个存储过程进行表的数据的输出

CREATE DEFINER=`root`@`%` PROCEDURE `recursion_testList`(
  IN testid VARCHAR(20)
)
    DETERMINISTIC
    COMMENT '临时表'
BEGIN
 DROP TEMPORARY TABLE IF EXISTS tmp_test;
    -- 创建临时表
    CREATE TEMPORARY TABLE tmp_test(testid VARCHAR(20));
    -- 清空临时表数据
    DELETE FROM tmp_test;
    -- 发起调用
    CALL findtestList(testId);
    -- 从临时表查询结果
    select * from recursion_test where id in (SELECT * FROM tmp_test ORDER BY testid);
END

做几次测试查询

mysql>call recursion_testList(2)
+--------------+---------------------+----------------+
| id | parent_id | name |
+--------------+---------------------+----------------+
| 2 | 0 | 江苏省 |
| 7 | 2 | 南京市 |
| 22 | 7 | 玄武区 |
| 23 | 7 | 秦淮区 |
| 24 | 7 | 建邺区 |
| 8 | 2 | 苏州市 |
| 25 | 8 | 沧浪区 |
| 26 | 8 | 平江区 |
| 27 | 8 | 虎丘区 |
| 9 | 2 | 徐州市 |
| 28 | 9 | 云龙区 |
| 29 | 9 | 鼓楼区 |
| 30 | 9 | 泉山区 |
+--------------+---------------------+----------------+


mysql>call recursion_testList(8)
+--------------+---------------------+----------------+
| id | parent_id | name |
+--------------+---------------------+----------------+
| 8 | 2 | 苏州市 |
| 25 | 8 | 沧浪区 |
| 26 | 8 | 平江区 |
| 27 | 8 | 虎丘区 |
+--------------+---------------------+----------------+

使用函数

使用自定义函数也可以实现递归查询，个人觉得自定义函数实现递归查询最好的方法，灵活多变。

自上而下实现查询，比如查询一个城市，显示这个城市下所有的区域等

CREATE DEFINER=`root`@`%` FUNCTION `findtest_down`(rootId INT) RETURNS varchar(4000) CHARSET utf8
    DETERMINISTIC
BEGIN
  DECLARE sTemp VARCHAR(4000);
  DECLARE sTempChd VARCHAR(4000);
  SET sTemp = '$';
  SET sTempChd = CAST(rootId as CHAR);
  WHILE sTempChd is not null DO
    SET sTemp = CONCAT(sTemp,',',sTempChd);
    SELECT GROUP_CONCAT(id) INTO sTempChd FROM recursion_test
        WHERE FIND_IN_SET(parent_id,sTempChd)>0;
  END WHILE;
  RETURN sTemp;
END

其中CONCAT和FIND_IN_SET函数的作用是：

GROUP_CONCAT(expr)
该函数会从expr中连接所有非NULL的字符串。如果没有非 NULL 的字符串，那么它就会返回NULL。

注意事项：GROUP_CONCAT查询结果默认最大长度限制为1024，该值是系统变量group_concat_max_len的默认值，可以通过SET [GLOBAL | SESSION] group_concat_max_len = val;更改该值。

FIND_IN_SET(str,strlist)
该函数返回一个1～N的值表示str在strlist中的位置。

该函数结合WHERE使用对结果集进行过过滤(查找str包含在strlist结果集里面的记录)

可以直接查询某个省下的所有城市和时区

mysql>SELECT * FROM recursion_test WHERE FIND_IN_SET(id,findtest_down(1));
+--------------+---------------------+----------------+
| id | parent_id | name |
+--------------+---------------------+----------------+
| 1 | 0 | 浙江省 |
| 4 | 1 | 杭州市 |
| 5 | 1 | 宁波市 |
| 6 | 1 | 金华市 |
| 13 | 4 | 西湖区 |
| 14 | 4 | 滨江区 |
| 15 | 4 | 余杭区 |
| 16 | 5 | 海曙区 |
| 17 | 5 | 江北区 |
| 18 | 5 | 镇海区 |
| 19 | 6 | 婺城区 |
| 20 | 6 | 金东区 |
| 21 | 6 | 永康市 |
+--------------+---------------------+----------------+

查询一下ID为6 金华市和ID为7南京的下属市区

mysql>SELECT * FROM recursion_test WHERE FIND_IN_SET(id,findtest_down(6));
+--------------+---------------------+----------------+
| id | parent_id | name |
+--------------+---------------------+----------------+
| 6 | 1 | 金华市 |
| 19 | 6 | 婺城区 |
| 20 | 6 | 金东区 |
| 21 | 6 | 永康市 |
+--------------+---------------------+----------------+
返回行数：[4]，耗时：9 ms.
mysql>SELECT * FROM recursion_test WHERE FIND_IN_SET(id,findtest_down(7));
+--------------+---------------------+----------------+
| id | parent_id | name |
+--------------+---------------------+----------------+
| 7 | 2 | 南京市 |
| 22 | 7 | 玄武区 |
| 23 | 7 | 秦淮区 |
| 24 | 7 | 建邺区 |
+--------------+---------------------+----------------+

自下而上，查询一个地点的所属城市和地区
创建函数

CREATE DEFINER=`root`@`%` FUNCTION `findtest_up`(rootId INT) RETURNS varchar(4000) CHARSET utf8
    DETERMINISTIC
BEGIN
  DECLARE sTemp VARCHAR(4000);
  DECLARE sTempChd VARCHAR(4000);
  
  SET sTemp = '$';
  SET sTempChd = CAST(rootId as CHAR);
  SET sTemp = CONCAT(sTemp,',',sTempChd);
  
  SELECT parent_id INTO sTempChd FROM recursion_test WHERE id = sTempChd;
  WHILE sTempChd <> 0 DO
    SET sTemp = CONCAT(sTemp,',',sTempChd);
    SELECT parent_id INTO sTempChd FROM recursion_test WHERE id = sTempChd;
  END WHILE;
  RETURN sTemp;
END

试着做几次查询

mysql>SELECT * FROM recursion_test WHERE FIND_IN_SET(id,findtest_up(39));
+--------------+---------------------+----------------+
| id | parent_id | name |
+--------------+---------------------+----------------+
| 3 | 0 | 安徽省 |
| 12 | 3 | 池州市 |
| 39 | 12 | 青阳 |
+--------------+---------------------+----------------+

mysql>SELECT * FROM recursion_test WHERE FIND_IN_SET(id,findtest_up(30));
+--------------+---------------------+----------------+
| id | parent_id | name |
+--------------+---------------------+----------------+
| 2 | 0 | 江苏省 |
| 9 | 2 | 徐州市 |
| 30 | 9 | 泉山区 |
+--------------+---------------------+----------------+

码农公寓

概述