MySQL5.7兼容WITH RECURSIVE递归查询的SQL改造问题

问题根因 #

你遇到的报错核心不是贴出的这段SQL本身有语法问题，而是你在MySQL8.0中用来生成序列表tmp的WITH RECURSIVE递归CTE语法，在MySQL5.7版本不被支持。你当前的SQL依赖存储连续数字的tmp表拆分parent_path父级路径，只需要把递归生成tmp表的逻辑替换为MySQL5.7兼容的手动序列构造即可。

修改步骤 #

1. 替换tmp序列表的生成逻辑：手动构造足够覆盖资源最大层级的连续数字序列，不需要递归语法，支持多少层就构造多少个数字即可，一般资源目录层级不会超过20层，构造1~20的序列足够覆盖绝大多数场景。
2. 适配MySQL5.7默认的ONLY_FULL_GROUP_BY模式：如果你的MySQL5.7开启了该模式，原SQL中仅按p1.res_id分组会报错，需要要么关闭该SQL模式，要么把SELECT中所有非聚合列加入GROUP BY，或者用ANY_VALUE()包裹非聚合非分组列。

适配后完整SQL #

SELECT
p1.res_id,
p1.res_name,
p1.res_type,
p1.file_id,
p1.create_time,
p1.parent_id,
p1.parent_path,
t4.fullname AS create_user,
t5.orgshortname org_name,
t3.label_describe,
( CASE WHEN p1.res_type IN ( 'file' ) THEN NULL ELSE p3.oss_down_path END ) oss_down_path,
p3.file_size,
GROUP_CONCAT( p2.res_name ORDER BY p1.n SEPARATOR '/' ) AS all_parent_name 
FROM
    (
    SELECT
        t2.*,
        SUBSTRING_INDEX( SUBSTRING_INDEX( t2.parent_path, ',', t1.n ), ',', - 1 ) AS linkid,
        t1.n 
    FROM
        -- 替换原来的tmp表，手动构造1~20的序列，可根据实际最大层级增加
        (
            SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
            SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
            SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
            SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL
            SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL
            SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL
            SELECT 19 UNION ALL SELECT 20
        ) t1
        CROSS JOIN tb_vk_resources t2 
    WHERE
        t1.n BETWEEN 1 
        AND (
        SELECT
            1 + LENGTH( parent_path ) - LENGTH(
            REPLACE ( parent_path, ',', '' )))) p1
    LEFT OUTER JOIN tb_vk_resources p2 ON ( p1.linkid = p2.res_id )
    LEFT OUTER JOIN tb_vk_res_basics t3 ON p1.relation_id = t3.res_basics_id
    LEFT OUTER JOIN tb_sys_user t4 ON p1.create_user = t4.userid
    LEFT OUTER JOIN tb_idm_org t5 ON t3.org_name = t5.orgid
    LEFT OUTER JOIN tb_oss_file_details p3 ON ( p1.file_id = p3.id ) 
WHERE
    p1.parent_id = '1'  
GROUP BY
    -- 适配ONLY_FULL_GROUP_BY模式，把所有非聚合列加入分组，或者关闭该SQL模式
    p1.res_id, p1.res_name, p1.res_type, p1.file_id, p1.create_time,
    p1.parent_id, p1.parent_path, t4.fullname, t5.orgshortname,
    t3.label_describe, oss_down_path, p3.file_size
LIMIT 100 OFFSET 0;

补充说明 #

如果你的资源层级超过20层，只需要在t1子查询的UNION ALL序列里继续加对应的数字即可，不需要调整其他逻辑。

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