[ppas query]

WITH RECURSIVE help_page(help_id, help_name, help_page, help_tag, help_main_key, help_sub_key, help_location, parent_id, show_type, depth_level, menu_seq) AS (
SELECT
hm.help_id, hm.help_name, hm.help_page, hm.help_tag, hm.help_main_key, hm.help_sub_key, concat(hm.help_name) as help_location, hm.parent_id, hm.show_type, 0 as depth_level, lpad(concat(hm.menu_seq),3,'0') as menu_seq
FROM tb_m00s22_help_meta hm
WHERE hm.parent_id is null or hm.parent_id = '' 
UNION ALL
SELECT
hm.help_id, hm.help_name, hm.help_page, hm.help_tag, hm.help_main_key, hm.help_sub_key, hp.help_location || ' > ' || concat(hm.help_name) as help_location, hm.parent_id, hm.show_type, depth_level + 1 as depth_level, hp.menu_seq || '-' || lpad(concat(hm.menu_seq),3,'0') as menu_seq
FROM tb_m00s22_help_meta hm
INNER JOIN help_page AS hp ON hm.parent_id = hp.help_id
)
SELECT *
FROM help_page
ORDER BY menu_seq

[mysql query]

select original_value as help_id, help_location, depth_level, menu_seq
from (
	select @ov as original_value, help_id, help_location, depth_level, menu_seq
	from (
		select t.help_id as help_id, @pv:=t.parent_id as parent_id, t.help_name
			   , @pv2:=(case when @pv2 = '' then concat(t.help_name,@pv2) else concat(t.help_name,' > ',@pv2) end) as help_location
			   , (@rownum := @rownum+1) as depth_level
			   , @menu_seq:=(case when @menu_seq = '' then concat(t.menu_seq,@menu_seq) else concat(t.menu_seq,'-',@menu_seq) end) as menu_seq
		from (select help_id, parent_id, help_name, lpad(concat(menu_seq),3,'0') as menu_seq from tb_m00s22_help_meta order by help_id desc LIMIT 100000) t
			 join (select @ov:='H300020006', @pv:='H300020006', @pv2:='', @rownum:=-1, @menu_seq:='' ) tmp
		where t.help_id = @pv
	) tt
	order by help_id asc
	limit 1
) ttt

** mysql은 subquery에서 orderby를 했는데, 외부로 나오면 다시 orderby가 원복된다.
limit를 사용하면 정렬이 유지된다.
https://mariadb.com/kb/en/library/why-is-order-by-in-a-from-subquery-ignored/

[select query]

select
	help_id, help_name, help_page, help_tag, help_main_key, help_sub_key, FN_SPLIT(help_data,'|',1) as help_location, parent_id, show_type, FN_SPLIT(help_data,'|',2) as depth_level, FN_SPLIT(help_data,'|',3) as menu_seq
from (
select help_id, help_name, help_page, help_tag, help_main_key, help_sub_key, parent_id, show_type,FN_GET_HELP_DATA(help_id) as help_data from tb_m00s22_help_meta
) t

[mysql function]

DELIMITER $$
CREATE DEFINER=`poswork`@`%` FUNCTION `FN_GET_HELP_DATA`(pv_help_id varchar(50)) RETURNS varchar(1000) CHARSET utf8
    DETERMINISTIC
BEGIN
        DECLA RE v_pv_help_id      varchar(50);
	DECLA RE v_ori_help_id     varchar(500);
        DECLA RE v_help_location   varchar(500);
        DECLA RE v_depth_level     varchar(500);
        DECLA RE v_menu_seq        varchar(500);
        DECLA RE v_rtn_value       varchar(1000);
	DECLA RE EXIT HANDLER FOR NOT FOUND RETURN null;
        
        SET v_pv_help_id = pv_help_id;
		
        IF v_pv_help_id IS NULL OR v_pv_help_id = '' THEN
                RETURN null;
        END IF;

		select original_value as help_id, help_location, depth_level, menu_seq
        INTO v_ori_help_id,v_help_location,v_depth_level,v_menu_seq
		from (
			select @ov as original_value, help_id, help_location, depth_level, menu_seq
			from (
				select t.help_id as help_id, @pv:=t.parent_id as parent_id, t.help_name
					   , @pv2:=(case when @pv2 = '' then concat(t.help_name,@pv2) else concat(t.help_name,' > ',@pv2) end) as help_location
					   , (@rownum := @rownum+1) as depth_level
					   , @menu_seq:=(case when @menu_seq = '' then concat(t.menu_seq,@menu_seq) else concat(t.menu_seq,'-',@menu_seq) end) as menu_seq
				from (select help_id, parent_id, help_name, lpad(concat(menu_seq),3,'0') as menu_seq from tb_m00s22_help_meta order by help_id desc LIMIT 100000) t
					 join (select @ov:=v_pv_help_id, @pv:=v_pv_help_id, @pv2:='', @rownum:=-1, @menu_seq:='') tmp
				where t.help_id = @pv
			) tt
			order by help_id asc
			limit 1
		) ttt;
		
		SET v_rtn_value = concat(v_help_location,'|',v_depth_level,'|',v_menu_seq);

	RETURN v_rtn_value;
    
END$$
DELIMITER ;

[split function]

DELIMITER $$
CREATE DEFINER=`poswork`@`%` FUNCTION `FN_SPLIT`(srcvalue varchar(500), split_char varchar(50), split_index int) RETURNS varchar(500) CHARSET utf8
    DETERMINISTIC
return if(srcvalue is null or srcvalue = '',null,substring_index(substring_index(srcvalue,split_char,split_index),split_char,-1))$$
DELIMITER ;