线上问题-mysql-WITH RECURSIVE导致死循环

父子结构表

CREATE TABLE tis_diagnostic_catalog( IDbigint unsigned NOT NULL AUTO_INCREMENT,, NAMEvarchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '', ROOT_IDbigint DEFAULT NULL COMMENT '根节点ID', CODEbigint DEFAULT NULL COMMENT '节点', PARENT_IDbigint DEFAULT NULL COMMENT '父节点ID (根节点为NULL)', SORTint DEFAULT NULL COMMENT '排序，从小到大', IS_VALIDint DEFAULT '10041001' COMMENT '是否有效', IS_DELETEDtinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除(0:未删除1:删除)', RECORD_VERSIONint NOT NULL COMMENT 'RECORD_VERSION', CREATE_BYvarchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'CREATE_BY', CREATE_TIMEdatetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'CREATE_TIME', UPDATE_BYvarchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'UPDATE_BY', UPDATE_TIME datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'UPDATE_TIME', PRIMARY KEY (ID) USING BTREE, KEY idx_parent_id (PARENT_ID) USING BTREE COMMENT '父节点ID', KEY idx_id_parent_id (ID,PARENT_ID) COMMENT '父子节点关系的联合索引' ) ENGINE=InnoDB AUTO_INCREMENT=11912 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='父子表';

这块比较特殊的情况是code跟parent_id组成父子结构，可以组装成一颗脑图结构。 根节点：parent_id为空则表示跟节点

2、程序中使用了MySQL8.0的递归查询

已知根节点，拼装成一棵树， 因为数据形成了一个闭环，导致当前代码的一个死循环。
形如：A -> B -> C ->B, 导致死循环 `WITH RECURSIVE child_hierarchy AS ( -- 基础查询：获取指定节点自身 SELECT ID, CODE FROM tis_diagnostic_catalog WHERE ID = #{params.diagnosticCatalogId} AND ROOT_ID = #{params.rootId}

UNION ALL

-- 递归查询：获取下级节点
SELECT  t.ID, t.CODE
FROM tis_diagnostic_catalog t
    INNER JOIN child_hierarchy ch ON t.PARENT_ID = ch.CODE
<where>
    AND t.ROOT_ID = #{params.rootId}
</where>

) -- 最终结果：按层级从高到低排序 SELECT ID FROM child_hierarchy;`

3、排查到死循环脚本

增加了一个path来记录所有的路径，如果当前code在path中则表示形成了一个闭环从而跳出循环，避免异常发生 `WITH RECURSIVE parent_hierarchy AS ( SELECT ID, CODE, ROOT_ID, PARENT_ID, CAST(CODE AS CHAR(16836)) AS path, -- 存储路径 0 AS is_cycle -- 循环标记 FROM tis_diagnostic_catalog WHERE ID=243 -- 从所有非根节点开始

UNION ALL

SELECT 
    t.ID,
    t.CODE,
            t.ROOT_ID, 
    t.PARENT_ID,
    CONCAT(ph.path, '->', t.CODE),
    IF(FIND_IN_SET(t.CODE, REPLACE(ph.path, '->', ',')), 1, 0) -- 检查是否形成环
FROM tis_diagnostic_catalog t
JOIN parent_hierarchy ph ON t.PARENT_ID = ph.CODE AND t.ROOT_ID=ph.ROOT_ID
WHERE ph.is_cycle = 0  -- 遇到环则停止递归

) SELECT ID, CODE, PARENT_ID, CONCAT(path, '->', CODE) AS full_cycle_path, is_cycle FROM parent_hierarchy WHERE is_cycle = 1` 有环路的异常数据： 案例1：1769918746610757633->1769918745801256961->1769918746019360770->1769918745692205057->1769918745725759490->1769918745759313922->1769918745801256961->1769918745801256961

4、mysql如何自动跳出这种闭环呢？？？

检测有环则不继续递归，退出； 查询出这样的数据也需要保证java代码中遇到闭环则退出递归操作(核心思想也还是记录递归的路径使用Set存储，若发现闭环，则退出)。 `WITH RECURSIVE child_hierarchy AS ( -- 基础查询：获取指定节点自身 SELECT *, CAST(CODE AS CHAR(16384)) AS visited_nodes, 0 AS is_cycle FROM tis_diagnostic_catalog WHERE ID = #{params.diagnosticCatalogId} AND ROOT_ID = #{params.rootId}

UNION ALL

-- 递归查询：获取下级节点
SELECT  t.*,
        CONCAT(ch.visited_nodes, ',', t.CODE),    -- 追加当前节点
        IF(FIND_IN_SET(t.CODE, ch.visited_nodes), 1, 0)  -- 检查是否已访问
FROM tis_diagnostic_catalog t
    INNER JOIN child_hierarchy ch ON t.PARENT_ID = ch.CODE
<where>
    AND t.ROOT_ID = #{params.rootId}
    AND ch.is_cycle = 0  -- 只继续处理无环的路径
    <if test=" params.seriesCode !=null and params.seriesCode != '' ">
        AND
        (
            t.SERIES_CODE IS NULL OR t.SERIES_CODE = "" OR FIND_IN_SET(t.SERIES_CODE,  #{params.seriesCode})
        )
    </if>
</where>

) -- 最终结果：按层级从高到低排序 SELECT * FROM child_hierarchy; `

5 建议

后期在代码中写递归或者mysql中的递归，都要有判断是都有闭环的处理方式，避免因为闭环导致的死锁，从而将整个系统奔溃。