题解 | 微服务架构下的深层依赖链路漏洞影响面分析

with recursive temp as (
    select 
    s2.service_id,    -- 注意区分s和s2   s用于确定Payment_Gateway为锚点，s2为每次查找迭代的对象
    s2.service_name,
    1 as dependency_depth,
    concat('Payment_Gateway->',s2.service_name) as dependency_path
    from service_dependencies sp
    join services s on s.service_id = sp.callee_service_id   -- 关键的两步骤，第一步确定Payment_Gateway为初始的被调用对象，它的id是等于sp里的被调用id
    join services s2 on s2.service_id = sp.caller_service_id  -- 下一次随机迭代的s2.service_id是sp的主调用对象
    where year(first_call_date) = 2025 and s.service_name = 'Payment_Gateway'

    union all

    select 
    s2.service_id,
    s2.service_name,
    t.dependency_depth + 1,
    concat(t.dependency_path,'->',s2.service_name)
    from temp t
    join service_dependencies sp on sp.callee_service_id = t.service_id  -- 上一阶段的t.service_id，变为被调用对象
    join services s2 on s2.service_id = sp.caller_service_id   -- 下次迭代的s2.service_id，又变为了主调用对象
    where year(first_call_date) = 2025
)
select * from temp
order by dependency_depth,service_id,dependency_path;
-- 理清主调用对象和被调用对象之间的关系，如何迭代是关键

一次把过程彻底搞清楚，相信练习时间长了，这类问题可以轻松拿下。