MySQL 8.0 CTE在供应链层级数据处理中的应用与优化

Liang Jie 2026-09-18 06:38

一、从一个让人头疼的库存报表说起

假设你在一家做家具的公司上班。你手里的物料清单（BOM）有好几层：一张桌子由桌板、桌腿、螺丝组成，而桌板又需要木板、贴皮和胶水。系统里每条记录只说“一个桌板要用几张木板”，但老板问的是“做100张桌子要买多少木板”。这时候，你得一层一层往下拆，像剥洋葱一样。以前的做法是写循环，跑一遍，再跑一遍，代码又长又难维护。MySQL 8.0 的 CTE（公共表表达式）就是来解决这种事的。

二、CTE 是啥？说白了就是起个名字的临时结果

写SQL的时候，你可能经常要把一段查出来的结果拿去跟别的表关联，或者对结果再算一遍。以前要么写成子查询，要么建临时表。CTE直接给你一个“带名字的临时结果”，而且只在当前这条SQL里有效，不用建表，不用管清理。

举个例子，你有一张库存流水表，想知道每个仓库当前库存。其实可以这样：

-- 技术栈：MySQL 8.0 (SQL)
-- 这里用 WITH 定义一个 CTE，名字叫 stock_balance
WITH stock_balance AS (
    SELECT 
        warehouse_id,                -- 仓库编号
        SUM(quantity) AS total_qty   -- 这个仓库的库存数量合计
    FROM inventory_transactions
    WHERE quantity IS NOT NULL       -- 过滤掉空值
    GROUP BY warehouse_id
)
-- 在主查询里直接用这个“临时结果”
SELECT 
    w.warehouse_name,
    s.total_qty
FROM warehouse w
LEFT JOIN stock_balance s ON w.warehouse_id = s.warehouse_id;

你看，这不就是在SQL里先算了一笔账，再拿这笔账去跟别的东西比。相比子查询，CTE读起来更像从上到下的流程，不容易把人绕晕。

三、供应链层级数据的正餐：递归CTE

CTE真正让人上头的，是递归。所谓递归CTE，就是让CTE自己引用自己，一遍一遍往下钻，直到没有新结果为止。这正是层级数据最需要的东西。

3.1 一张能说明问题的BOM表

咱们先建一张特别简单的物料清单表，别管真实系统里有多复杂，原理一样：

-- 技术栈：MySQL 8.0 (SQL)
-- 建一张物料清单表，用来描述“一个成品由哪些零件组成”
CREATE TABLE bom (
    parent_part VARCHAR(20) NOT NULL COMMENT '父物料编号',
    child_part  VARCHAR(20) NOT NULL COMMENT '子物料编号',
    quantity    DECIMAL(10,3) NOT NULL COMMENT '组装一个父物料需要几个子物料',
    PRIMARY KEY (parent_part, child_part)  -- 同一父件下子件唯一
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='物料清单表';

-- 插入几条测试数据：桌子 -> 桌板/桌腿/螺丝；桌板 -> 木板/贴皮/胶水
INSERT INTO bom (parent_part, child_part, quantity) VALUES
('DESK', 'TOP',   1.000),     -- 组装桌子需要1张桌板
('DESK', 'LEG',   4.000),     -- 需要4条桌腿
('DESK', 'SCREW', 20.000),    -- 需要20颗螺丝
('TOP',  'WOOD',  2.000),     -- 做1张桌板需要2块木板
('TOP',  'VENEER', 3.000),    -- 需要3张贴皮
('TOP',  'GLUE',  0.500);     -- 需要0.5公斤胶水

注意：这里我把“螺丝”右边是不是还分？不分了，这个例子里螺丝就是最底层。

3.2 用递归CTE把整棵树掏出来

现在我们要问：一张桌子用到哪些物料，每个物料到底要多少？对“桌板”这种中间件，还要继续往下拆。递归CTE写起来特别符合直觉：

-- 技术栈：MySQL 8.0 (SQL)
-- 递归展开：从 DESK 开始，一层一层往下找
WITH RECURSIVE bom_tree AS (
    -- 锚点：先拿到顶级物料下直接挂着的零件
    SELECT
        parent_part        AS root_part,   -- 记下顶级品名
        child_part,                        -- 当前这一层的物料
        quantity,                          -- 当前这一层相对上一层的用量
        1                  AS lvl,         -- 层级，顶级下面算第1层
        CAST(parent_part AS CHAR(500)) AS path  -- 用来记录走过的路径
    FROM bom
    WHERE parent_part = 'DESK'             -- 只查这一个成品

    UNION ALL

    -- 递归部分：拿“刚才找到的物料”继续去当爸爸查孩子
    SELECT
        bt.root_part,                      -- 顶级品名一直不变
        b.child_part,                      -- 下一级的物料
        bt.quantity * b.quantity,          -- 累积数量：父级累积量 × 当前用量
        bt.lvl + 1,                        -- 层级加一
        CONCAT_WS(' -> ', bt.path, b.child_part) -- 把路径串长一点
    FROM bom_tree bt
    JOIN bom b ON b.parent_part = bt.child_part  -- 关键：透过孩子找孙子
)
SELECT * FROM bom_tree ORDER BY lvl, child_part;

跑出来大概就是：

第一层有 TOP、LEG、SCREW，数量分别是1、4、20。
第二层有 WOOD、VENEER、GLUE，是从 TOP 拆下来的，数量就是 TOP 的1乘以各自的用量，也就是2、3、0.5。
没有第三层，因为 WOOD 不再有子件。

这个结果就是一张桌子的完整物料需求树。要是你还要算“做10张桌子”，外面再包一层乘10就行了。实际系统里，BOM可能十几层，靠循环写会写到哭，递归CTE几行就出来了。

3.3 从下往上汇总：老板要的采购清单

树展开了，但老板只看“总共需要哪几种原料，每种多少”。比如桌板是半成品，不是最终买来的，而木板、贴皮、胶水、桌腿、螺丝才是要采购的。怎么弄？在上面的递归结果后面，接着用 GROUP BY 汇总：

-- 技术栈：MySQL 8.0 (SQL)
-- 把递归展开的结果再包一层，按最底层物料汇总
WITH RECURSIVE bom_tree AS (
    -- 同样先递归展开
    SELECT
        parent_part AS root_part,
        child_part,
        quantity,
        1 AS lvl,
        CAST(parent_part AS CHAR(500)) AS path
    FROM bom
    WHERE parent_part = 'DESK'

    UNION ALL

    SELECT
        bt.root_part,
        b.child_part,
        bt.quantity * b.quantity,
        bt.lvl + 1,
        CONCAT_WS(' -> ', bt.path, b.child_part)
    FROM bom_tree bt
    JOIN bom b ON b.parent_part = bt.child_part
)
-- 这里就是“从下往上汇总”
SELECT
    child_part AS material,            -- 采购的物料号
    SUM(quantity) AS total_required,   -- 一共需要多少个/多少公斤
    MIN(lvl)     AS first_used_level   -- 最早出现在哪一层（可当参考）
FROM bom_tree
GROUP BY child_part
ORDER BY total_required DESC;

得到的结果，就是做一张桌子的采购清单。实际中还可以 JOIN 物料主文件，过滤掉自制品，留下采购件。这个写法，把“展开”和“汇总”放在同一条SQL里完成了，省了中间建临时表的麻烦。

3.4 路径追踪与死循环防范

有时候你会遇到“A物料包含B物料，B物料又包含A物料”这种错误数据，一旦递归起来就死循环。MySQL 默认最多跑1000层，到限制就会报错。咱们可以在CTE里加个深度限制，顺便用路径字段检测环：

-- 技术栈：MySQL 8.0 (SQL)
-- 带环检测的递归CTE
WITH RECURSIVE bom_tree AS (
    SELECT
        parent_part AS root_part,
        child_part,
        quantity,
        1 AS lvl,
        CAST(CONCAT(parent_part, '->', child_part) AS CHAR(1000)) AS path
    FROM bom
    WHERE parent_part = 'DESK'

    UNION ALL

    SELECT
        bt.root_part,
        b.child_part,
        bt.quantity * b.quantity,
        bt.lvl + 1,
        CONCAT_WS('->', bt.path, b.child_part)
    FROM bom_tree bt
    JOIN bom b ON b.parent_part = bt.child_part
    -- 下面这个条件：如果新路径里已经出现过这个子件，就说明有环，直接停掉
    AND LOCATE(CONCAT('->', b.child_part, '->'), CONCAT('->', bt.path, '->')) = 0
    -- 同时限制层级不要超过20层，防止数据太深跑太久
    AND bt.lvl < 20
)
SELECT * FROM bom_tree;

实际用的时候，你可能还要加个变量控制最大深度。但别急，MySQL自带一个参数，叫做 cte_max_recursion_depth。比如允许跑100层：

-- 技术栈：MySQL 8.0 (SQL)
-- 把递归深度上限临时调高到100
SET SESSION cte_max_recursion_depth = 100;

不过这个参数不要随便调大，递归层数越多，计算量往往是成倍涨的。没做约束的深递归，能把数据库CPU烧到冒烟。

四、优化：别让递归变成“深坑”

递归CTE写起来爽，跑起来不一定快。供应链层级数据动辄几万条，很多企业一张BOM拆到底要几十层。咱们有几个土办法能顶用。

4.1 先解决“有没有索引”

递归每层都要做一次 JOIN，也就是拿父物料去查子物料。如果 bom 表的 parent_part 没有索引，每一层都得全表扫，那真是灾难。咱们建表时已经设了主键(parent_part, child_part)，MySQL 默认就是联合索引。但真实系统里，如果你用自增ID做主键，而 parent_part 只是普通列，一定要单独加索引：

-- 技术栈：MySQL 8.0 (SQL)
-- 给父物料列加索引，能让递归连接快非常多
ALTER TABLE bom ADD INDEX idx_parent_part (parent_part);

另外，如果想经常按子件反查父件，也建议给 child_part 加索引。别小看这个动作，层级每深一层，索引就能省掉一次全表扫描。

4.2 用 UNION ALL 而不是 UNION

递归CTE里，如果没有强制去重需求，一定用 UNION ALL。UNION 会额外做一次排序去重，这对层级展开毫无意义，纯粹浪费资源。从咱们前面的例子可以看出，全部用的 UNION ALL，就是这道理。

4.3 控制展开范围

很多新手喜欢先把整张 BOM 全表递归展开，再慢慢过滤。这是最笨的做法。应该像例子一样，从顶级物料开始往下走，而且顶层条件尽量用上索引。如果你要算多张成品，就用 WHERE parent_part IN (...)，而不是把整棵树拉出来。还有，只展开需要的层级，比如只查3层以内的子件，就在递归部分加 AND bt.lvl < 3。这样能省掉不少底层的计算。

4.4 跟临时表、WITH子句、物化视图的关系

MySQL 8.0 的CTE在内部可能被物化成临时表，也可能被合并成子查询，具体看执行计划。你可以在SQL前面加 EXPLAIN 来看。如果递归结果被重复使用，业务上又特别慢，可以考虑把结果先插入一张临时表，然后再多次查询。不过在大多数场景里，CTE的可读性远大于手动建临时表，这也是为什么我们优先推荐它。

我顺便提一个和CTE经常一起出现的兄弟——窗口函数。比如你想在每个层级里找数量最大的那条路径，可以配合 ROW_NUMBER() 使用。但窗口函数在这里属于进阶玩法，先用好递归CTE更重要。别把脑筋烧坏了。

五、应用场景、技术优缺点

5.1 非常适合做什么

多级BOM展开与需求汇总。这是最典型的场景，咱们上面演示的就是。
组织架构汇报关系查询。比如查某个员工下面挂了多少人，以及公司层层汇报的链路线。
供应链缺料分析。当库存不够时，把成品需求拆到原材料，再跟现有库存对比，算出缺什么、缺多少。
成本逐级卷算。每个半成品成本是下层物料成本之和，用递归CTE可以从原材料往上滚。
风险传播路径分析。比如某种原料断货，会影响哪些成品，沿着BOM往上游/往下游找。

5.2 技术优缺点

优点：

表达层级逻辑非常直观，SQL看起来就像在做“逐层钻取”。
一条SQL能完成递归+分组汇总，不用写存储过程，不用挑语言。
相比在Java或Python里写循环查很多次数据库，递归CTE大大减少了网络往返，对数据一致性也有好处。

缺点：

递归层数深、数据量大时，性能可能很差，需要配合索引和深度限制。
排查算法问题时，SQL的执行计划没普通查询那么好懂。
不是所有数据库都支持同样的递归写法，换库迁移时要改。
如果BOM带有版本有效期、替代料等复杂业务模型，CTE会变得特别复杂，光靠SQL可能不如专门的计算引擎灵活。

六、注意事项

首先，递归CTE的锚点部分不能引用自己，递归部分必须无条件用 UNION ALL 或 UNION 连接，而且两边列数要一致。这是语法硬性要求，错了直接报错。

其次，必须设置合理的最大递归深度。MySQL 默认 cte_max_recursion_depth 是1000，但1000层递归在供应链里往往意味着数据有问题。你要么用 SET SESSION 调整，要么在SQL里通过 lvl < 一个数 限制。千万别一直开着超深递归跑全量数据，会堵死数据库。

第三，注意环路。即使你觉得BOM不会环，系统给你灌了几个脏数据就能让你整个报表挂掉。在递归部分加一个 LOCATE 检测路径是否重复，成本不高，非常值得加。

第四，递归的结果顺序不一定稳定。想按层级排列，就在最外层 ORDER BY，不要依赖递归产生的顺序。

第五，关于数量精度。BOM的用量可能是小数，乘着乘着浮点误差会积累。DECIMAL 比 FLOAT 靠谱，建议全都用 DECIMAL，尤其涉及金额成本时。

第六，CTE名称不要跟已经存在的表名重复，否则默认会用用户的CTE，但容易引起困惑。最好给CTE起一个一眼能看出是“临时结果”的名字，比如 bom_tree、stock_balance 这类。

第七，别把所有复杂业务都塞进一个CTE里。如果逻辑太绕，可以先拆成两三个CTE，一个算基础数据，第二个在上一个基础上再加工，最后主查询汇总。这样可读性更好：

-- 技术栈：MySQL 8.0 (SQL)
-- 多个CTE串联使用的示例：先算库存，再算需求，最后对比
WITH inventory AS (
    SELECT part_no, SUM(quantity) AS onhand
    FROM stock
    GROUP BY part_no
),
demand AS (
    SELECT part_no, SUM(qty) AS needed
    FROM demand_orders
    GROUP BY part_no
)
SELECT 
    d.part_no,
    d.needed,
    IFNULL(i.onhand, 0) AS onhand,
    d.needed - IFNULL(i.onhand, 0) AS shortage
FROM demand d
LEFT JOIN inventory i ON d.part_no = i.part_no
HAVING shortage > 0;

这种写法，读起来就像看一张流程图，先算库存，再算需求，再比较。比起各种嵌套子查询，心情好得多。

七、总结

供应链里的层级数据，天生适合树形处理。MySQL 8.0 的递归CTE给了SQL一条非常直接的出路：不用存储过程，不用临时表，也能写出一层一层往下钻的逻辑。咱们通过BOM展开、需求汇总、路径追踪这几个例子，已经能覆盖很多日常报表需求。优化的核心也就是三个词：索引、限量、去环。把这三个词记住，碰上再复杂的层级数据，都不至于手足无措。

如果屏幕前的你正被多层BOM搞得头大，不妨把文中的SQL复制下来，改成自己的表名和字段试一遍。你会发现，以前几百行的循环代码，现在几行SQL就完事了。这大概就是 CTE 让人舒服的地方：结构清晰、写法简单、还不占额外空间。数据库里那点破数据，终于能服服帖帖了。

好了，工具给你了，剩下的就是动手造。

围绕“MySQL 8.0 CTE在供应链层级数据处理中的应用与优化”参与讨论

0 条

MySQL 8.0 CTE在供应链层级数据处理中的应用与优化

一、从一个让人头疼的库存报表说起

二、CTE 是啥？说白了就是起个名字的临时结果

三、供应链层级数据的正餐：递归CTE

3.1 一张能说明问题的BOM表

3.2 用递归CTE把整棵树掏出来

3.3 从下往上汇总：老板要的采购清单

3.4 路径追踪与死循环防范

四、优化：别让递归变成“深坑”

4.1 先解决“有没有索引”

4.2 用 UNION ALL 而不是 UNION

4.3 控制展开范围

4.4 跟临时表、WITH子句、物化视图的关系

五、应用场景、技术优缺点

5.1 非常适合做什么

5.2 技术优缺点

六、注意事项

七、总结

评论

关联文章

AI助手

一、从一个让人头疼的库存报表说起

二、CTE 是啥？说白了就是起个名字的临时结果

三、供应链层级数据的正餐：递归CTE

3.1 一张能说明问题的BOM表

3.2 用递归CTE把整棵树掏出来

3.3 从下往上汇总：老板要的采购清单

3.4 路径追踪与死循环防范

四、优化：别让递归变成“深坑”

4.1 先解决“有没有索引”

4.2 用 UNION ALL 而不是 UNION

4.3 控制展开范围

4.4 跟临时表、WITH子句、物化视图的关系

五、应用场景、技术优缺点

5.1 非常适合做什么

5.2 技术优缺点

六、注意事项

七、总结

同分类面试题

关联文章

AI助手