Mysql8和Oracle实际项目中递归查询树形结构

背景：

项目升级，引入MySQL数据库，之前一直用的是Oracle数据，在做用户登录单位维护的时候，需要返回该用户所属单位下的所有子单位。下边是模拟项目数据实践的过程。

数据准备：

准备一张单位表，里面存储下级单位以及上级单位的对应关系数据。

-- 创建单位表
CREATE TABLE UNITS (
  ID INT PRIMARY KEY,
  NAME VARCHAR(255),
  PARENT_ID INT
);

-- 插入数据
INSERT INTO UNITS (ID, NAME, PARENT_ID)
VALUES
  (1500, '1500单位', NULL),
  (1501, '1501单位', 1500),
  (15011, '15011单位', 1501),
  (15012, '15012单位', 1501),
  (150121, '150121单位', 15012),
  (1600, '1600单位', NULL),
  (1601, '1601单位', 1600),
  (1602, '1602单位', 1600);

实践：

1、Oracle做法

主要使用START WITH和CONNECT BY PRIOR完成递归查询，多用于层次查询。
START WITH：表示递归的起始记录。
CONNECT BY PRIOR：表示递归时与当前记录的关联关系，其中PRIOR的位置是是可以变动的，位置的不同，其查询结果也不同。

所有顶级单位向下查询属于该单位的所有的子单位：

# 使用START WITH和CONNECT BY PRIOR完成递归查询，以所有PARENT_ID IS NULL的记录为起始记录，关联表中的ID字段，查询所有属于下级单位
SELECT * FROM UNITS U CONNECT BY PRIOR ID = PARENT_ID START WITH PARENT_ID IS NULL;


ID    |NAME    |PARENT_ID|
------|--------|---------|
  1500|1500单位  |         |
  1501|1501单位  |     1500|
 15011|15011单位 |     1501|
 15012|15012单位 |     1501|
150121|150121单位|    15012|
  1600|1600单位  |         |
  1601|1601单位  |     1600|
  1602|1602单位  |     1600|

根据指定单位向下查询属于该单位的所有的子单位：

# 指定查询1600单位下的所有的子单位，注意向下查找，PRIOR的位置在ID的前边
SELECT * FROM UNITS U CONNECT BY PRIOR ID = PARENT_ID START WITH PARENT_ID = 1600;

ID  |NAME  |PARENT_ID|
----|------|---------|
1601|1601单位|     1600|
1602|1602单位|     1600|

根据子单位向上查询属于该单位的所有的上级单位，如果不要包含指定的子单位，那就在结果集中过滤掉即可：

# 查询1501的所有的上级单位，注意向上查找，PRIOR的位置在PARENT_ID的前边
SELECT * FROM UNITS U CONNECT BY ID = PRIOR PARENT_ID START WITH ID = 1501;

ID  |NAME  |PARENT_ID|
----|------|---------|
1501|1501单位|     1500|
1500|1500单位|         |

2、Mysql做法

主要是使用关键WITH RECURSIVE 进行递归查询，不过要求，Mysql的版本需要在8.0以上。

所有顶级单位向下查询属于该单位的所有的子单位：

# WITH RECURSIVE是MySQL8支持的递归关键字。
WITH RECURSIVE UNITS_PARENT (ID ,NAME, PARENT_ID) AS (
	# 递归体的开始，所有的父级下子单位，根据这个语句产生递归体的初始行，并将这些初始行数据插入到UNITS_PARENT临时表中。
    SELECT U.ID, U.NAME, U.PARENT_ID FROM UNITS AS U WHERE U.PARENT_ID IS NULL 
    UNION ALL 
    # 递归的开始，将根据UNITS_PARENT表中的初始行数据和UNITS表进行真正的递归查询，直到不在产生新的数据行为止，也就是递归结束，并将查询的数据插入到UNITS_PARENT临时表中。
    # 注意ON的条件：UNITS_PARENT中初始行数据存的是所有的上级单位信息，向下查询，那就是查询所有的PARENT_ID等于初始行ID的数据。
    SELECT U.ID, U.NAME, U.PARENT_ID FROM UNITS_PARENT AS UP JOIN UNITS AS U ON UP.ID = U.PARENT_ID
)
# 从UNITS_PARENT临时表查询出所有的符合要求的数据
SELECT ID ,NAME, PARENT_ID FROM UNITS_PARENT ORDER BY ID;


#|ID    |NAME    |PARENT_ID|
-+------+--------+---------+
1|  1500|1500单位  |         |
2|  1501|1501单位  |     1500|
3|  1600|1600单位  |         |
4|  1601|1601单位  |     1600|
5|  1602|1602单位  |     1600|
6| 15011|15011单位 |     1501|
7| 15012|15012单位 |     1501|
8|150121|150121单位|    15012|

根据指定单位向下查询属于该单位的所有的子单位：

# WITH RECURSIVE是MySQL8支持的递归关键字。
WITH RECURSIVE UNITS_PARENT (ID ,NAME, PARENT_ID) AS (
	# 递归体的开始，1600下子单位，根据这个语句产生递归体的初始行，并将这些初始行数据插入到UNITS_PARENT临时表中。
    SELECT U.ID, U.NAME, U.PARENT_ID FROM UNITS AS U WHERE U.PARENT_ID = 1600 
    UNION ALL 
    # 递归的开始，将根据UNITS_PARENT表中的初始行数据和UNITS表进行真正的递归查询，直到不在产生新的数据行为止，也就是递归结束，并将查询的数据插入到UNITS_PARENT临时表中。
    # 注意ON的条件：UNITS_PARENT中初始行数据存的是1600单位信息，向下查询，那就是查询所有的PARENT_ID等于初始行1600 ID的数据。
    SELECT U.ID, U.NAME, U.PARENT_ID FROM UNITS_PARENT AS UP JOIN UNITS AS U ON UP.ID = U.PARENT_ID
)
# 从UNITS_PARENT临时表查询出所有的符合要求的数据
SELECT ID ,NAME, PARENT_ID FROM UNITS_PARENT ORDER BY ID;


#|ID  |NAME  |PARENT_ID|
-+----+------+---------+
1|1601|1601单位|     1600|
2|1602|1602单位|     1600|

根据子单位向上查询属于该单位的所有的上级单位，如果不要包含指定的子单位，那就在结果集中过滤掉即可：

# WITH RECURSIVE是MySQL8支持的递归关键字。
WITH RECURSIVE UNITS_PARENT (ID ,NAME, PARENT_ID) AS (
	# 递归体的开始，查询1501的所有上级单位，根据这个语句产生递归体的初始行，并将这些初始行数据插入到UNITS_PARENT临时表中。
    SELECT U.ID, U.NAME, U.PARENT_ID FROM UNITS AS U WHERE U.ID = 1501
    UNION ALL 
    # 递归的开始，将根据UNITS_PARENT表中的初始行数据和UNITS表进行真正的递归查询，直到不在产生新的数据行为止，也就是递归结束，并将查询的数据插入到UNITS_PARENT临时表中。
    # 注意ON的条件：UNITS_PARENT中初始行数据存的是1501单位信息，向上查询，那就是查询所有ID等于初始行PARENT_ID的数据。
    SELECT U.ID, U.NAME, U.PARENT_ID FROM UNITS_PARENT AS UP JOIN UNITS AS U ON U.ID = UP.PARENT_ID
)
# 从UNITS_PARENT临时表查询出所有的符合要求的数据
SELECT ID ,NAME, PARENT_ID FROM UNITS_PARENT ORDER BY ID;


#|ID  |NAME  |PARENT_ID|
-+----+------+---------+
1|1500|1500单位|         |
2|1501|1501单位|     1500|