发表更新MySQL系列 / SQL操作14 分钟读完 (大约2144个字)

MySQL系列(30)： MySQL 递归查询

开发中经常会碰到传父类id，查出其下所有子类需求。

实现思路：拿当前查到的 id 递归查询 parent_id = id，合并所有的 id 到一个字段 ids，然后查 id 在 ids 中的数据 。

实现方案：一种是在代码中递归查询实现；另一种是在 SQL语句递归查询实现。

本篇主要描述在 MySQL 中的递归查询SQL语句实现。

父子示例数据

国家多级行政区的数据就是典型的父子结构。

表结构：

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `sys_area` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(100) DEFAULT NULL COMMENT '行政区名称',
  `level` int(11) DEFAULT NULL COMMENT '级别',
  `parent_id` int(11) DEFAULT NULL COMMENT '父id',
  `gb_code` int(11) DEFAULT NULL COMMENT '行政区GB编码',
  `alias_name` varchar(300) DEFAULT NULL COMMENT '别名',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_code` (`gb_code`),
  KEY `idx_level` (`level`),
  KEY `idx_name` (`name`(2))
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='中国内地行政区表';

插入数据：

1
2
3
4
5
6
7
8
9
INSERT INTO `hz_qyd`.`sys_area`(`id`, `name`, `level`, `parent_id`, `gb_code`, `alias_name`) VALUES (19, '广东省', 1, NULL, 440000, NULL);
INSERT INTO `hz_qyd`.`sys_area`(`id`, `name`, `level`, `parent_id`, `gb_code`, `alias_name`) VALUES (231, '广州市', 2, 19, 440100, NULL);
INSERT INTO `hz_qyd`.`sys_area`(`id`, `name`, `level`, `parent_id`, `gb_code`, `alias_name`) VALUES (233, '深圳市', 2, 19, 440300, NULL);
INSERT INTO `hz_qyd`.`sys_area`(`id`, `name`, `level`, `parent_id`, `gb_code`, `alias_name`) VALUES (2083, '荔湾区', 3, 231, 440103, NULL);
INSERT INTO `hz_qyd`.`sys_area`(`id`, `name`, `level`, `parent_id`, `gb_code`, `alias_name`) VALUES (2084, '越秀区', 3, 231, 440104, NULL);
INSERT INTO `hz_qyd`.`sys_area`(`id`, `name`, `level`, `parent_id`, `gb_code`, `alias_name`) VALUES (2085, '海珠区', 3, 231, 440105, NULL);
INSERT INTO `hz_qyd`.`sys_area`(`id`, `name`, `level`, `parent_id`, `gb_code`, `alias_name`) VALUES (2104, '罗湖区', 3, 233, 440303, NULL);
INSERT INTO `hz_qyd`.`sys_area`(`id`, `name`, `level`, `parent_id`, `gb_code`, `alias_name`) VALUES (2105, '福田区', 3, 233, 440304, NULL);
INSERT INTO `hz_qyd`.`sys_area`(`id`, `name`, `level`, `parent_id`, `gb_code`, `alias_name`) VALUES (2106, '南山区', 3, 233, 440305, NULL);

数据结果如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT * FROM `sys_area` WHERE id = 19 OR parent_id IN (19, 231, 233); 
+------+--------+-------+-----------+---------+------------+
| id   | name   | level | parent_id | gb_code | alias_name |
+------+--------+-------+-----------+---------+------------+
|   19 | 广东省 |     1 | NULL      |  440000 | NULL       |
|  231 | 广州市 |     2 |        19 |  440100 | NULL       |
|  233 | 深圳市 |     2 |        19 |  440300 | NULL       |
| 2083 | 荔湾区 |     3 |       231 |  440103 | NULL       |
| 2084 | 越秀区 |     3 |       231 |  440104 | NULL       |
| 2085 | 海珠区 |     3 |       231 |  440105 | NULL       |
| 2104 | 罗湖区 |     3 |       233 |  440303 | NULL       |
| 2105 | 福田区 |     3 |       233 |  440304 | NULL       |
| 2106 | 南山区 |     3 |       233 |  440305 | NULL       |
+------+--------+-------+-----------+---------+------------+

SQL语句实现

实现思路分析

  • **FIND_IN_SET(str, strlist)**：str 代表要查询的字符串，strList 是一个以逗号分隔的字符串，如（’a,b,c’）。

    此函数用于查找 str 字 符串在 字符串 strlist 中的位置，返回结果 1 ~ n，没有返回 0。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    mysql> SELECT FIND_IN_SET('o','h,e,l,l,o');
    +------------------------------+
    | FIND_IN_SET('o','h,e,l,l,o') |
    +------------------------------+
    |                            5 |
    +------------------------------+

    mysql> SELECT * FROM sys_area WHERE FIND_IN_SET(id,'1,2,3,4');
    +----+--------+-------+-----------+---------+------------+
    | id | name   | level | parent_id | gb_code | alias_name |
    +----+--------+-------+-----------+---------+------------+
    |  1 | 北京市 |     1 | NULL      |  110000 | NULL       |
    |  2 | 天津市 |     1 | NULL      |  120000 | NULL       |
    |  3 | 河北省 |     1 | NULL      |  130000 | NULL       |
    |  4 | 山西省 |     1 | NULL      |  140000 | NULL       |
    +----+--------+-------+-----------+---------+------------+

    **思路：**查出包含当前节点和所有子节点的的 id，以逗号拼接成 strlist，然后递归查询 parent_id 在 strlist 中的数据。接下来是想办法拼接 strlist。拼接字符串的相关函数有 concat，concat_ws，group_concat。

  • **CONCAT(str1,str2,…)**：将多个字符拼接成一整个字符串。

    1
    2
    3
    4
    5
    6
    7
    mysql> SELECT CONCAT('H','E','L','L','O');
    +-----------------------------+
    | CONCAT('H','E','L','L','O') |
    +-----------------------------+
    | HELLO                       |
    +-----------------------------+
    1 row in set (0.03 sec)

  • **CONCAT_WS(separator,str1,str2,…)**：指定分隔的符，对字符进行拼接。

    1
    2
    3
    4
    5
    6
    mysql> SELECT CONCAT_WS('_','H','E','L','L','O');
    +------------------------------------+
    | CONCAT_WS('_','H','E','L','L','O') |
    +------------------------------------+
    | H_E_L_L_O                          |
    +------------------------------------+

  • **GROUP_CONCAT(expr)**：功能更强大，可以去重，排序，指定分隔符。分隔符默认为 ,。

    1
    2
    3
    4
    GROUP_CONCAT([DISTINCT] expr [,expr ...]
                 [ORDER BY {unsigned_integer | col_name | expr}
                     [ASC | DESC] [,col_name ...]]
                 [SEPARATOR str_val])

    示例如下：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    mysql> SELECT GROUP_CONCAT(id) FROM temp;
    +------------------+
    | GROUP_CONCAT(id) |
    +------------------+
    | 1,2,3,4          |
    +------------------+

    mysql> SELECT GROUP_CONCAT(id SEPARATOR '_') FROM temp;
    +--------------------------------+
    | GROUP_CONCAT(id SEPARATOR '_') |
    +--------------------------------+
    | 1_2_3_4                        |
    +--------------------------------+

    注意：此函数是有长度限制的，默认为 1024 字节。可以通过 show variables like "group_concat_max_len"; 来查看。修改方式如下：

    1. 修改 MySQL 配置文件 my.cnf ，增加 group_concat_max_len = 102400 #你要的最大长度 。

    2. 全局修改：SET GLOBAL group_concat_max_len=102400; 。

    3. 当前 session 修复：SET SESSION group_concat_max_len=102400;。

      2，3 的修改都会在 MySQL 重启之后失效，建议直接修改配置文件。102400 的长度一般也够用了。假设一个id的长度为10个字节，也能拼上一万个id了。

    **思路：**使用 GROUP_CONCAT 函数，就可以达到 FIND_IN_SET 的 strlist 的效果。

查所有子类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> SELECT u2.id, u2.name
FROM( 
     SELECT 
			@ids AS p_ids, 
			(SELECT @ids := GROUP_CONCAT(id) FROM sys_area WHERE FIND_IN_SET(parent_id, @ids)) AS c_ids, 
			@l := @l+1 AS LEVEL 
			 FROM sys_area, (SELECT @ids := 19, @l := 0 ) b  
			 WHERE @ids IS NOT NULL 
    ) u1 
JOIN sys_area u2
ON FIND_IN_SET(u2.id, u1.p_ids)  AND u2.id != 19; -- 需要包含自己,删掉!=
+------+--------+
| id   | name   |
+------+--------+
|  231 | 广州市 |
|  233 | 深圳市 |
| 2083 | 荔湾区 |
| 2084 | 越秀区 |
| 2085 | 海珠区 |
| 2104 | 罗湖区 |
| 2105 | 福田区 |
| 2106 | 南山区 |
+------+--------+

查所有父类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> SELECT u2.id, u2.name 
FROM( 
     SELECT
            @id c_ids,
            (SELECT @id:=GROUP_CONCAT(parent_id) FROM sys_area WHERE FIND_IN_SET(id,@id)) p_ids,
            @l := @l+1 AS LEVEL 
     FROM sys_area,(SELECT @id:=2106, @l := 0) b
     WHERE @id IS NOT NULL
    ) u1
JOIN sys_area u2  ON u1.c_ids = u2.id  ;
+------+--------+
| id   | name   |
+------+--------+
| 2106 | 南山区 |
|  233 | 深圳市 |
|   19 | 广东省 |
+------+--------+

执行过程分析

  1. 中间部分

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    mysql> SELECT u2.id, u2.name, u1.p_ids
    FROM( SELECT @ids AS p_ids, 
    			(SELECT @ids := GROUP_CONCAT(id) FROM sys_area WHERE FIND_IN_SET(parent_id, @ids)) AS c_ids, 
    			@l := @l+1 AS LEVEL 
    			 FROM sys_area, (SELECT @ids := 19, @l := 0 ) b  
    			 WHERE @ids IS NOT NULL 
        ) u1 
    JOIN sys_area u2
    ON FIND_IN_SET(u2.id, u1.p_ids);
    +------+--------+-------------------------------+
    | id   | name   | p_ids                         |
    +------+--------+-------------------------------+
    |   19 | 广东省 | 19                            |
    |  231 | 广州市 | 231,233                       |
    |  233 | 深圳市 | 231,233                       |
    | 2083 | 荔湾区 | 2083,2084,2085,2104,2105,2106 |
    | 2084 | 越秀区 | 2083,2084,2085,2104,2105,2106 |
    | 2085 | 海珠区 | 2083,2084,2085,2104,2105,2106 |
    | 2104 | 罗湖区 | 2083,2084,2085,2104,2105,2106 |
    | 2105 | 福田区 | 2083,2084,2085,2104,2105,2106 |
    | 2106 | 南山区 | 2083,2084,2085,2104,2105,2106 |
    +------+--------+-------------------------------+

    定义 id 集合变量 ids，递归层级变量 @l。传入父 id，查出子类，再子类赋值递归查出子的子节点，生成临时表。

    FIND_IN_SET( parent_id, @ids ) 中的 @ids 是已查出的 id 集合，SELECT @ids := GROUP_CONCAT( id ) 是本次查询的 id 集合，使用 GROUP_CONCAT 拼接 id 并赋值给 @ids。

    ( SELECT @ids := 19, @l := 0 ) 是先给 @ids 和 @l 赋初始值。

    WHERE @ids IS NOT NULL; 查询条件，也是终止条件，为空则为没有子节点，递归结束。

  2. 外层部分

    1
    2
    3
    4
    5
    6
    SELECT u2.id, u2.name
    FROM( 
    	 -- 中间部分
        ) u1 
    JOIN sys_area u2
    ON FIND_IN_SET(u2.id, u1.p_ids)

    关联两张表，查出 id 查在所有的 p_ids 中的数据。

MySQL 8.0递归方案

MySQL 8.0 的新特性中包含了一个：通用表表达式（Common table expressions）功能。开始支持非递归和递归的通用表表达式。通用表表达式允许使用命名的临时结果集，通过在 SELECT 语句和某些其他语句之前允许WITH子句来实现。更多信息，参考 Section 13.2.15, WITH (Common Table Expressions)。

从 MySQL 8.0.19 开始，递归通用表表达式 (CTE) 的递归 SELECT 部分支持 LIMIT 子句。 还支持带有 OFFSET 的 LIMIT。 更多信息，参考 Recursive Common Table Expressions。

递归通用表表达式

递归通用表表达式是具有引用其自身名称的子查询的表达式。 例如：

1
2
3
4
5
6
7
WITH RECURSIVE cte (n) AS  -- cte为结果集表名, n为字段, 不指定字段则为所有
(
  SELECT 1	-- 递归开始,可理解为 cte 表的第一条记录 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5  -- n+1 结果为2, 此时 cte表有两条记录, 分别为 1,2
)
SELECT * FROM cte;

执行时，该语句会产生以下结果，即包含简单线性序列的单列：

1
2
3
4
5
6
7
8
9
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

递归查询示例实现

查所有子类

需求：传入父id，查出 父及所有子类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-- 不指定列
mysql> WITH RECURSIVE cte AS (
    SELECT * FROM sys_area  WHERE id = 19
    UNION ALL
    SELECT t.* FROM sys_area t INNER JOIN cte ON t.parent_id = cte.id
)
SELECT id, `name`, parent_id FROM cte;

mysql> WITH RECURSIVE cte AS (
    SELECT * FROM sys_area  WHERE id = 19
    UNION ALL
    SELECT t1.* FROM sys_area t1, cte t2 WHERE t1.parent_id = t2.id
)
SELECT id, `name`, parent_id FROM cte;

+------+--------+-----------+
| id   | name   | parent_id |
+------+--------+-----------+
|   19 | 广东省 | NULL      |
|  231 | 广州市 |        19 |
|  233 | 深圳市 |        19 |
| 2083 | 荔湾区 |       231 |
| 2084 | 越秀区 |       231 |
| 2085 | 海珠区 |       231 |
| 2104 | 罗湖区 |       233 |
| 2105 | 福田区 |       233 |
| 2106 | 南山区 |       233 |
+------+--------+-----------+

-- 指定列
mysql> WITH RECURSIVE cte(id, `name`) AS (
    SELECT id, name FROM sys_area  WHERE id = 19
    UNION ALL
    SELECT t.id, t.name FROM sys_area t INNER JOIN cte ON t.parent_id = cte.id
)
SELECT id, `name` FROM cte;
+------+--------+
| id   | name   |
+------+--------+
|   19 | 广东省 |
|  231 | 广州市 |
|  233 | 深圳市 |
| 2083 | 荔湾区 |
| 2084 | 越秀区 |
| 2085 | 海珠区 |
| 2104 | 罗湖区 |
| 2105 | 福田区 |
| 2106 | 南山区 |
+------+--------+

查所有父类

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> WITH RECURSIVE cte AS (
    SELECT * FROM sys_area  WHERE id = 2106
    UNION ALL
    SELECT t1.* FROM sys_area t1, cte t2 WHERE t2.parent_id = t1.id
)
SELECT id, `name`, parent_id FROM cte;
+------+--------+-----------+
| id   | name   | parent_id |
+------+--------+-----------+
| 2106 | 南山区 |       233 |
|  233 | 深圳市 |        19 |
|   19 | 广东省 | NULL      |
+------+--------+-----------+

相关参考

  1. MySQL 8.0 Reference Manual > 13.2.15 WITH (Common Table Expressions)

  2. Mysql8.0递归查询的简单用法示例

MySQL系列(30)： MySQL 递归查询

http://blog.gxitsky.com/2022/06/06/MySQL-28-Recursive-Query/

作者

光星

发布于

2022-06-06

更新于

2023-03-27

许可协议

#

喜欢这篇文章？打赏一下作者吧

评论

关注我
×