Summary

A few days ago, a client consulted about MySQL recursive query method. At that time, he had a simple understanding and felt that recursive query logic level is a special query method. However, it was later found that this is a very common query requirement, such as folding display of some comment floors, various flow charts and other recursive queries can be achieved. But MySQL itself does not realize the recursive query function, but it can be achieved by some special methods. This paper simply tests some methods of MySQL to realize recursive query.

testing environment

The test environment is RDS for MySQL 5.7. The main logic of the test table is province-city-city. In order to realize this logic, the tables and data should be prepared as follows.

Test sheet

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='Recursive Test Table';

Primary key increments, ID and parent ID, followed by name. The logic of tables needs to be implemented by recursive queries

Insert test data

The insertion data is as follows

INSERT INTO recursion_test VALUES(1,1,'Zhejiang Province');
INSERT INTO recursion_test VALUES(2,2,'Jiangsu Province');
INSERT INTO recursion_test VALUES(3,3,'Anhui Province');

INSERT INTO recursion_test VALUES(4,1,'Hangzhou City');
INSERT INTO recursion_test VALUES(5,1,'Ningbo City');
INSERT INTO recursion_test VALUES(6,1,'Jinhua City');


INSERT INTO recursion_test VALUES(7,2,'Nanjing City');
INSERT INTO recursion_test VALUES(8,2,'Suzhou City');
INSERT INTO recursion_test VALUES(9,2,'Xuzhou City');


INSERT INTO recursion_test VALUES(10,3,'Hefei City');
INSERT INTO recursion_test VALUES(11,3,'Wuhu City');
INSERT INTO recursion_test VALUES(12,3,'chizhou');


INSERT INTO recursion_test VALUES(13,4,'xihu district');
INSERT INTO recursion_test VALUES(14,4,'Binjiang');
INSERT INTO recursion_test VALUES(15,4,'Yuhang District');

INSERT INTO recursion_test VALUES(16,5,'Haishu district');
INSERT INTO recursion_test VALUES(17,5,'jiangbei district');
INSERT INTO recursion_test VALUES(18,5,'zhenhai district');

INSERT INTO recursion_test VALUES(19,6,'Wucheng District');
INSERT INTO recursion_test VALUES(20,6,'Jindong District');
INSERT INTO recursion_test VALUES(21,6,'Yongkang City');

INSERT INTO recursion_test VALUES(22,7,'xuanwu district');
INSERT INTO recursion_test VALUES(23,7,'Qinhuai District');
INSERT INTO recursion_test VALUES(24,7,'jianye district');

INSERT INTO recursion_test VALUES(25,8,'Canglang District');
INSERT INTO recursion_test VALUES(26,8,'Pingjiang District');
INSERT INTO recursion_test VALUES(27,8,'Huqiu District');

INSERT INTO recursion_test VALUES(28,9,'Yunlong District');
INSERT INTO recursion_test VALUES(29,9,'gulou district');
INSERT INTO recursion_test VALUES(30,9,'quanshan district');

INSERT INTO recursion_test VALUES(31,10,'Shushan District');
INSERT INTO recursion_test VALUES(32,10,'luyang district');
INSERT INTO recursion_test VALUES(33,10,'Yaohai District');

INSERT INTO recursion_test VALUES(34,11,'Jinghu District');
INSERT INTO recursion_test VALUES(35,11,'Jiujiang District');
INSERT INTO recursion_test VALUES(36,11,'Yijiang District');

INSERT INTO recursion_test VALUES(37,12,'Guichi District');
INSERT INTO recursion_test VALUES(38,12,'Jiuhua Mountain Area');
INSERT INTO recursion_test VALUES(39,12,'Qingyang');

OK, get the boring data ready and start trying to do recursive queries

Start testing

Connect using tables

If the maximum depth of the query tree is determined. You can use left join directly, and join once for every level of recursion. For example, if ID = parent_ID, the corresponding fields can be selected and queried in recursive order.
The logical recursive layer of our table has only three layers. In theory, only two table joins are needed to query. The following is the query.

mysql>SELECT t1.name as 'Province',t2.name as 'City',t3.name as 'urban district'
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;
+----------------+----------------+----------------+
| Province | City | urban district |
+----------------+----------------+----------------+
| Zhejiang Province | Hangzhou City | xihu district |
| Zhejiang Province | Hangzhou City | Binjiang |
| Zhejiang Province | Hangzhou City | Yuhang District |
| Zhejiang Province | Ningbo City | Haishu district |
| Zhejiang Province | Ningbo City | jiangbei district |
| Zhejiang Province | Ningbo City | zhenhai district |
| Zhejiang Province | Jinhua City | Wucheng District |
| Zhejiang Province | Jinhua City | Jindong District |
| Zhejiang Province | Jinhua City | Yongkang City |
+----------------+----------------+----------------+

Temporary Table + stored procedure

The first stored procedure is responsible for writing each node's data to a temporary table and recursively querying the lowest node.

CREATE PROCEDURE `findtestList`() 
 COMMENT 'recursive query' 
BEGIN
  DECLARE v_test VARCHAR(20) DEFAULT '';
  DECLARE done INTEGER DEFAULT 0;
    -- Query results are placed in cursors
  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;
    
    -- Input organization id Write to temporary table
  INSERT INTO tmp_test VALUES (testId);
  OPEN C_test;
  FETCH C_test INTO v_test;
  WHILE (done=0)
  DO
        -- Recursive calls to find subordinates
    CALL findtestList(v_test);
    FETCH C_test INTO v_test;
  END WHILE;
  CLOSE C_test;
END 

The second stored procedure is responsible for creating and deleting temporary tables, and calling the first stored procedure to output the table's data.

CREATE DEFINER=`root`@`%` PROCEDURE `recursion_testList`(
  IN testid VARCHAR(20)
)
    DETERMINISTIC
    COMMENT 'cursor'
BEGIN
 DROP TEMPORARY TABLE IF EXISTS tmp_test;
    -- Create temporary tables
    CREATE TEMPORARY TABLE tmp_test(testid VARCHAR(20));
    -- Clear temporary table data
    DELETE FROM tmp_test;
    -- Initiate a call
    CALL findtestList(testId);
    -- Query results from temporary tables
    select * from recursion_test where id in (SELECT * FROM tmp_test ORDER BY testid);
END

Make several test queries

mysql>call recursion_testList(2)
+--------------+---------------------+----------------+
| id | parent_id | name |
+--------------+---------------------+----------------+
| 2 | 0 | Jiangsu Province |
| 7 | 2 | Nanjing City |
| 22 | 7 | xuanwu district |
| 23 | 7 | Qinhuai District |
| 24 | 7 | jianye district |
| 8 | 2 | Suzhou City |
| 25 | 8 | Canglang District |
| 26 | 8 | Pingjiang District |
| 27 | 8 | Huqiu District |
| 9 | 2 | Xuzhou City |
| 28 | 9 | Yunlong District |
| 29 | 9 | gulou district |
| 30 | 9 | quanshan district |
+--------------+---------------------+----------------+


mysql>call recursion_testList(8)
+--------------+---------------------+----------------+
| id | parent_id | name |
+--------------+---------------------+----------------+
| 8 | 2 | Suzhou City |
| 25 | 8 | Canglang District |
| 26 | 8 | Pingjiang District |
| 27 | 8 | Huqiu District |
+--------------+---------------------+----------------+

Using functions

User-defined functions can also be used to achieve recursive query. Personally, I think that the best way to achieve recursive query with user-defined functions is flexible and changeable.

Implement queries from top to bottom, such as querying a city, displaying all areas under the city, etc.

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

The functions of CONCAT and FIND_IN_SET are:

• GROUP_CONCAT(expr)
  This function connects all non-NULL strings from expr. If there is no non-NULL string, it returns NULL.

Note: The default maximum length limit of GROUP_CONCAT query results is 1024, which is the default value of the system variable group_concat_max_len, and can be changed by SET [GLOBAL | SESSION] group_concat_max_len = val.

• FIND_IN_SET(str,strlist)
  This function returns a value of 1-N to indicate the location of str in strlist.

This function combines with WHERE to filter the result set (looking for the records str contains in the strlist result set)

You can directly query all the cities and time zones saved

mysql>SELECT * FROM recursion_test WHERE FIND_IN_SET(id,findtest_down(1));
+--------------+---------------------+----------------+
| id | parent_id | name |
+--------------+---------------------+----------------+
| 1 | 0 | Zhejiang Province |
| 4 | 1 | Hangzhou City |
| 5 | 1 | Ningbo City |
| 6 | 1 | Jinhua City |
| 13 | 4 | xihu district |
| 14 | 4 | Binjiang |
| 15 | 4 | Yuhang District |
| 16 | 5 | Haishu district |
| 17 | 5 | jiangbei district |
| 18 | 5 | zhenhai district |
| 19 | 6 | Wucheng District |
| 20 | 6 | Jindong District |
| 21 | 6 | Yongkang City |
+--------------+---------------------+----------------+

Check out Jinhua City with ID 6 and Nanjing City with ID 7

mysql>SELECT * FROM recursion_test WHERE FIND_IN_SET(id,findtest_down(6));
+--------------+---------------------+----------------+
| id | parent_id | name |
+--------------+---------------------+----------------+
| 6 | 1 | Jinhua City|
| 19 | 6 | Wucheng District|
| 20 | 6 | Jindong District|
| 21 | 6 | Yongkang City|
+--------------+---------------------+----------------+
Return rows: [4], time-consuming: 9 ms.
mysql>SELECT * FROM recursion_test WHERE FIND_IN_SET(id,findtest_down(7));
+--------------+---------------------+----------------+
| id | parent_id | name |
+--------------+---------------------+----------------+
| 7 | 2 | Nanjing|
| 22 | 7 | Basalt Area|
| 23 | 7 | Qinhuai District|
| 24 | 7 | Jianye District|
+--------------+---------------------+----------------+

From bottom to top, query the cities and regions of a location
Create functions

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

Try making several queries

mysql>SELECT * FROM recursion_test WHERE FIND_IN_SET(id,findtest_up(39));
+--------------+---------------------+----------------+
| id | parent_id | name |
+--------------+---------------------+----------------+
| 3 | 0 | Anhui Province |
| 12 | 3 | chizhou |
| 39 | 12 | Qingyang |
+--------------+---------------------+----------------+

mysql>SELECT * FROM recursion_test WHERE FIND_IN_SET(id,findtest_up(30));
+--------------+---------------------+----------------+
| id | parent_id | name |
+--------------+---------------------+----------------+
| 2 | 0 | Jiangsu Province |
| 9 | 2 | Xuzhou City |
| 30 | 9 | quanshan district |
+--------------+---------------------+----------------+