如何编写复杂MariaDB递归查询？旧数据库关联电路查询需求

需要在设计老旧的数据库中编写递归查询，获取某一电路及其所有关联电路的LitCircuitID。相关表结构如下：

CREATE TABLE CircuitLayout(
    CircuitLayoutID int,
    PRIMARY KEY (CircuitLayoutID)
);

CREATE TABLE LitCircuit (
    LitCircuitID int,
    CircuitLayoutID int,
    PRIMARY KEY (LitCircuitID),
    FOREIGN KEY (CircuitLayoutID) REFERENCES CircuitLayout(CircuitLayoutID)
);

CREATE TABLE CircuitLayoutItem(
    CircuitLayoutItemID int,
    CircuitLayoutID int,
    TableName varchar(255),
    TablePK int,
    PRIMARY KEY (CircuitLayoutItemID),
    FOREIGN KEY (CircuitLayoutID) REFERENCES CircuitLayout(CircuitLayoutID)
);

其中CircuitLayoutItem的TableName字段可取值为LitCircuit，TablePK对应目标表的主键。

我尝试编写了如下无法正常工作的递归CTE查询：

WITH RECURSIVE carries AS (
    SELECT LitCircuit.LitCircuitID AS recurseList FROM LitCircuit
    JOIN CircuitLayoutItem ON LitCircuit.CircuitLayoutID = CircuitLayoutItem.CircuitLayoutID
    WHERE CircuitLayoutItem.TableName = "LitCircuit" AND CircuitLayoutItem.TablePK IN (00340)
    UNION
    SELECT LitCircuit.LitCircuitID AS CircuitIDs FROM LitCircuit
    JOIN CircuitLayout ON LitCircuit.CircuitLayoutID = CircuitLayoutItem.CircuitLayoutID
    WHERE CircuitLayoutItem.TableName = "LitCircuit" AND CircuitLayoutItem.TablePK IN (SELECT recurseList FROM carries)
) 
SELECT * FROM carries;

00340是测试用虚拟值，实际会替换为真实列表。

我对递归CTE的语法存在困惑：不清楚锚点成员的返回结果是带列的表还是值列表，也不知道如何在查询中正确引用递归CTE（carries）。

我用Python实现了该逻辑：

def get_circuits(circuit_list):
    result_list = []
    for layout_item_key, layout_item in CircuitLayoutItem.items():
            if layout_item['TableName'] == "LitCircuit" and layout_item['TablePK'] in circuit_list:
                layout = layout_item['CircuitLayoutID']
                for circuit_key, circuit in LitCircuit.items():
                    if circuit["CircuitLayoutID"] == layout:
                        result_list.append(circuit_key)
    result_list.extend(get_circuits(result_list))
    return result_list

请问如何将该逻辑转换为有效的MariaDB递归查询？

递归CTE核心规则说明 #

首先明确递归CTE的两个核心要求：

• 锚点成员和递归成员必须返回完全一致的列结构（列名、数据类型需匹配）
• 递归成员需要通过关联锚点/递归结果集实现迭代，不能直接用子查询IN (SELECT ... FROM carries)的方式（这种写法会导致逻辑错误，不符合递归CTE的执行逻辑）

正确的MariaDB递归查询 #

根据你的Python逻辑，递归逻辑是：从初始电路ID出发，找到所有关联的CircuitLayoutItem，再通过这些条目找到对应的LitCircuit，重复该过程直到没有新的电路ID出现。对应的SQL实现如下：

WITH RECURSIVE circuit_hierarchy AS (
    -- 锚点成员：初始查询，获取传入ID对应的第一层关联电路
    SELECT l.LitCircuitID
    FROM LitCircuit l
    JOIN CircuitLayoutItem cli 
        ON l.CircuitLayoutID = cli.CircuitLayoutID
    WHERE cli.TableName = 'LitCircuit' 
      AND cli.TablePK IN (00340) -- 替换为真实的初始ID列表
    UNION DISTINCT -- 用DISTINCT避免重复ID，防止无限递归
    -- 递归成员：用上一轮结果的ID，继续查找关联电路
    SELECT l.LitCircuitID
    FROM LitCircuit l
    JOIN CircuitLayoutItem cli 
        ON l.CircuitLayoutID = cli.CircuitLayoutID
    JOIN circuit_hierarchy ch 
        ON cli.TablePK = ch.LitCircuitID -- 关联递归结果集，实现迭代
    WHERE cli.TableName = 'LitCircuit'
)
SELECT * FROM circuit_hierarchy;

关键修正点说明 #

1. 统一列结构：锚点和递归成员都只返回LitCircuitID列，列名保持一致
2. 正确关联递归结果：递归成员通过JOIN circuit_hierarchy ch ON cli.TablePK = ch.LitCircuitID关联上一轮结果，而非子查询IN，这是递归CTE的标准写法
3. 避免无限递归：使用UNION DISTINCT自动去重，防止循环引用导致的无限递归（数据存在循环关联时必须添加）
4. 修正表关联错误：原递归成员错误关联了CircuitLayout表，实际仅需关联CircuitLayoutItem和递归结果集

递归CTE额外说明 #

• 锚点成员返回的是带列的表，递归成员必须与其列结构完全匹配，确保整个CTE结果集结构统一
• 引用递归CTE时，直接在递归成员中用JOIN关联即可，与关联普通表的方式一致，无需嵌套子查询

内容的提问来源于stack exchange，提问作者dafrandle