CONNECT BY with NOCYCLE （ oracle ） 在 MariaDB 中使用简单的 sql 代码进行分层查询

问：

将提供一个具有自己的 nocycle 解决方案的分层查询。 需要改进。

假设有一棵带或不带环的树（Oidipus）。 桌子：

CREATE TABLE `person` (
  `ID` varchar(10) NOT NULL,
  `PARENT` varchar(10) NOT NULL,
  `TYPE` varchar(10) NOT NULL,
  `NAME` varchar(50) NOT NULL
)

字段 TYPE 和 NAME 不重要。连接是通过字段 PARENT 中另一个人的 ID 实现的。

1. 寻找父母：

WITH recursive Parents(ID, SUMID, TYPE, PARENT, LEVEL) AS (
  SELECT ID, Concat(ID,"Z","                  ...") AS SUMID, TYPE, PARENT, 0 AS LEVEL FROM `person` WHERE ID = '1000000005'
  UNION ALL
  SELECT m.ID, CONCAT(m.ID,"Z",SUMID) AS SUMID , m.TYPE, m.PARENT, LEVEL + 1 FROM `person` as m INNER JOIN Parents t on m.ID = t.PARENT
  WHERE LEVEL < 6
  AND INSTR ( SUMID, m.ID) < 1
)
SELECT * FROM Parents;

额外的列 SUMID（连接的“数字”ID，分隔符=“Z”）将用于检查 NOCCYCLE（参见 Oracle 关键字）。（Oidipus 在字段 ID 中仅出现一次）。 工作正常，但 SUMID 初始内容应编码为 MAXLEVEL 乘以 10 “String”。

仅部分起作用的：

2. 查找所有儿童

WITH recursive Children(ID, SUMID, TYPE, PARENT, LEVEL) AS (
  SELECT ID, Concat(ID,"Z","                  ...") AS SUMID, TYPE, PARENT, 0 AS LEVEL FROM `person` WHERE ID = '1000000002'
  UNION ALL
  SELECT m.ID, CONCAT(m.ID,"Z",SUMID) AS SUMID , m.TYPE, m.PARENT, LEVEL + 1 FROM `person` as m INNER JOIN Children t on m.PARENT = t.ID
  WHERE LEVEL < 6
  AND INSTR ( SUMID, m.ID) < 1
)
SELECT * FROM Children;

当有人有 5 个孩子和 5*5 = 25 个孙子孙女等时，SUMID 就不够长了。 此外，所有儿童的剧本都非常缓慢且性能较弱。 如何在简单的MySQL中实现“查找所有子项”？

我尝试为树 rekordstructure 实现分层查询。查询“查找孩子”速度慢且效率低下。我希望有改进建议。

INSERT INTO `person` (`ID`, `PARENT`, `TYPE`, `NAME`) VALUES
('1000000001', '1000000001', 'A', 'first'),
('1000000002', '1000000004', 'B', 'second'),
('1000000003', '1000000002', 'C', 'third'),
('1000000004', '1000000002', 'C', 'fourth'),
('1000000005', '1000000004', 'C', 'fifth'),
('1000000006', '1000000002', 'C', '6th'),
('1000000007', '1000000002', 'C', '7th'),
('1000000008', '1000000002', 'C', '8th'),
('1000000009', '1000000002', 'C', '9th'),
('1000000010', '1000000005', 'D', '10th'),
('1000000011', '1000000005', 'D', '11th'),
('1000000012', '1000000005', 'D', '12nd'),
('1000000013', '1000000005', 'D', '13rd');

结果：

MariaDB [devmysql]> WITH recursive Children(ID, SUMID, TYPE, PARENT, LEVEL) AS (
    ->   SELECT ID, Concat(ID,"Z","                  ") AS SUMID, TYPE, PARENT, 0 AS LEVEL FROM `person` WHERE ID = '1000000002'
    ->   UNION ALL
    ->   SELECT m.ID, CONCAT(m.ID,"Z",SUMID) AS SUMID , m.TYPE, m.PARENT, LEVEL + 1 FROM `person` as m INNER JOIN Children t on m.PARENT = t.ID
    ->   WHERE LEVEL < 6
    ->   AND INSTR ( SUMID, m.ID) < 1
    -> )
    -> SELECT * FROM Children;
+------------+-------------------------------+------+------------+-------+
| ID         | SUMID                         | TYPE | PARENT     | LEVEL |
+------------+-------------------------------+------+------------+-------+
| 1000000002 | 1000000002Z                   | B    | 1000000004 |     0 |
| 1000000003 | 1000000003Z1000000002Z        | C    | 1000000002 |     1 |
| 1000000004 | 1000000004Z1000000002Z        | C    | 1000000002 |     1 |
| 1000000006 | 1000000006Z1000000002Z        | C    | 1000000002 |     1 |
| 1000000007 | 1000000007Z1000000002Z        | C    | 1000000002 |     1 |
| 1000000008 | 1000000008Z1000000002Z        | C    | 1000000002 |     1 |
| 1000000009 | 1000000009Z1000000002Z        | C    | 1000000002 |     1 |
| 1000000005 | 1000000005Z1000000004Z1000000 | C    | 1000000004 |     2 |
| 1000000010 | 1000000010Z1000000005Z1000000 | D    | 1000000005 |     3 |
| 1000000011 | 1000000011Z1000000005Z1000000 | D    | 1000000005 |     3 |
| 1000000012 | 1000000012Z1000000005Z1000000 | D    | 1000000005 |     3 |
| 1000000013 | 1000000013Z1000000005Z1000000 | D    | 1000000005 |     3 |
+------------+-------------------------------+------+------------+-------+
12 rows in set, 11 warnings (0.004 sec)

Oracle 等价物如下所示（见下文）：
Hint
In oracle

• 否则，UNION ALL是强制性的 ORA-32040：递归 WITH 子句必须使用 UNION ALL 操作
• 参数列表是必需的，否则 ORA-32039：递归 WITH 子句必须具有列别名列表
• 递归省略，否则系统语法错误
• LEVEL 是 ORACLE 中的一个关键字，即使用 LEV 代替

WITH Children (ID, SUMID, LEVEL) 
AS
(
  SELECT
    m.ID,
    ',' || CAST(m.ID AS VARCHAR(120) || ','  AS SUMID,
    0 AS LEV
  FROM
    person  AS m
  WHERE
    ID = '1000000002'
  UNION ALL
  SELECT
    m.ID,
    t.SUMID || m.ID || ','  AS SUMID,
    LEV + 1 AS LEV
  FROM
    person AS m
  INNER JOIN
    Children AS t
      ON m.PARENT = t.ID
  WHERE
      t.LEV < 10
    AND INSTR(t.SUMID, CONCAT(',', m.ID, ',')) < 1
)
SELECT * FROM Children;

查询在 600 毫秒内传送了大约 60000 条记录。
Query 已通过 oracle 对应物与 MINUS 在两个方向上针对他的连接进行了测试：

select ID
FROM person 
  START WITH ID = '1000000002'
  CONNECT BY NOCYCLE PRIOR ID = PARENT
MINUS
SELECT ID FROM (
WITH Children (ID, SUMID, LEVEL) 
AS
(
  SELECT
    m.ID,
    ',' || CAST(m.ID AS VARCHAR(120) || ','  AS SUMID,
    0 AS LEV
  FROM
    person  AS m
  WHERE
    ID = '1000000002'
  UNION ALL
  SELECT
    m.ID,
    t.SUMID || m.ID || ','  AS SUMID,
    LEV + 1 AS LEV
  FROM
    person AS m
  INNER JOIN
    Children AS t
      ON m.PARENT = t.ID
  WHERE
      t.LEV < 10
    AND INSTR(t.SUMID, CONCAT(',', m.ID, ',')) < 1
)
SELECT * FROM Children);

不提供任何记录。
查询在 1 秒内在 ORACLE 中成功测试。至于我难以置信的快。
“Na ja”，Connect by 可以自动提供更多功能：

• CONNECT_BY_ISCYCLE
• CONNECT_BY_ISLEAF
• LEVEL
  [参见：oracle 文档][1]https://docs.oracle.com/cd/B12037_01/server.101/b10759/pseudocolumns001.htm#i1009434]