工作中的MySQL-CSDN博客

本文链接：https://blog.csdn.net/qq_53566672/article/details/135107890

文章介绍了在MySQL中使用存储过程实现的两种功能：一种是根据已知长度处理任务依赖，通过`substring_index`和`replace`函数炸裂字段；另一种是循环获取任务的依赖，通过递归和正则表达式获取多层依赖。

摘要由CSDN通过智能技术生成

MySQL炸裂函数

已知炸裂长度

select id,job_name,substring_index(substring_index(rely,',',numbers.n),',',-1) as rely_id
from t_etl_job_config 
JOIN (
        SELECT 1 AS n
        UNION ALL
        SELECT 2
        UNION ALL
        SELECT 3
        UNION ALL
        SELECT 4
    ) numbers
    ON CHAR_LENGTH(rely) - CHAR_LENGTH(REPLACE(rely, ',', '')) >= numbers.n - 1

未知炸裂长度

select id,
       job_name,
       replace(substring(curr_content, length(last_content) + 1), ',', '') as rely_id
from (
         select t.id,
                t.job_name,
                t.rely,
                -- r.rank_num,
                substring_index(t.rely,',',r.rank_num - 1) as last_content,
                substring_index(t.rely,',',r.rank_num)     as curr_content
         from (
                  select id,
                         rely,
                         job_name,
                         length(rely) - length(replace(rely, ',', '')) + 1 as len -- 需炸开的字段
                  from t_etl_job_config where is_failure <>1
              ) t
                  join dim_rank_config r on r.rank_num > 0 and r.rank_num <= t.len  -- dim_rank_config为配置表 存1-100 需大于炸裂字段的长度
     ) res
;

rely为需要炸开的字段，dim_rank_config为配置表存1-100 需大于炸裂字段的长度。

MySQL存储过程-循环获取任务依赖


-- 获取执行任务的所有依赖任务
CREATE FUNCTION `dubhe_sch_db`.`task_follow_desc`
(
`task_id` int(11)
) RETURNS varchar(40000) BEGIN
    DECLARE follow_id VARCHAR(40000);
    DECLARE follow_id_tmp VARCHAR(40000);
    DECLARE counter int DEFAULT 0;
    
    -- 通过传参获取第一层依赖任务
    select `rely` into follow_id_tmp from `dubhe_sch_db`.`t_etl_job_config` where `id`=task_id and `is_failure`=0;
    
    set follow_id = follow_id_tmp;
    -- 当此任务依赖不为空时 循环寻找后续依赖
    while follow_id_tmp <> '' or is not null 
    do
        -- 通过正则匹配依赖任务 并获取依赖任务的下一层依赖任务 如果依赖为空 则设为空串
        select ifnull(group_concat(rely),'') into follow_id_tmp from `dubhe_sch_db`.`t_etl_job_config` 
        where concat(',',id,',') regexp concat(',',replace(follow_id_tmp,',',',|,'),',')
        and is_failure=0 ;
        -- 如果依赖为空 结束循环
        if(follow_id_tmp='') THEN
            set follow_id=follow_id;
        else -- 将上一层依赖任务和本次依赖任务拼接
            set follow_id=concat(follow_id,',',follow_id_tmp);
        end if;

    end while;
    return follow_id;
end