工作文档

Appearance

原生脚本 ​

用法：整段内容复制，修改菜单层级的5个参数即可（目前最多支持5级菜单）

sql
-- 开始：清理变量
SET @menu_1 = NULL, @menu_2 = NULL, @menu_3 = NULL, @menu_4 = NULL, @menu_5 = NULL;
SET @menu_level = NULL, @field_parent_id = NULL, @field_value = NULL, @menu_value = NULL, @error_msg = NULL;

-- 菜单层级
-- SET @menu_1 = '采购中心';
SET @menu_2 = '采购折扣管理';
SET @menu_3 = '采购退补价确认单';
-- SET @menu_4 = '测测';
-- SET @menu_5 = '成都成都成都';

SET @menu_level = CASE WHEN @menu_1 IS NULL THEN 0 WHEN @menu_2 IS NULL THEN 1 WHEN @menu_3 IS NULL THEN 2 WHEN @menu_4 IS NULL THEN 3 WHEN @menu_5 IS NULL THEN 4 ELSE 5 END;

SET @field_parent_id = CASE WHEN @menu_1 IS NULL THEN NULL WHEN @menu_2 IS NULL THEN '0' WHEN @menu_3 IS NULL THEN 't1.id' WHEN @menu_4 IS NULL THEN 't2.id' WHEN @menu_5 IS NULL THEN 't3.id' ELSE 't4.id' END;

SET @field_value = 'INSERT INTO `tb_platform_sys_menu` (`id`, `parent_id`, `system_code`, `system_name`, `menu_name`, `menu_type`, `menu_code`, `menu_business_code`, `sort_order`, `is_display`, `menu_status`, `route_path`, `description`, `icon_type`, `icon_url`, `web_url`, `revision`, `button_key`, `button_type`, `operator`, `operator_name`, `delete_flag`, `delete_timestamp`, `create_time`, `modify_time`, `menu_tag`, `system_module_code`, `system_module_name`, `menu_abbr`) \n';


SET @menu_value = IF(@menu_level = 0, '', (SELECT CONCAT_WS(', ', 
                       id,
                       @field_parent_id,
                       IFNULL(QUOTE(system_code), 'NULL'),
                       IFNULL(QUOTE(system_name), 'NULL'),
                       IFNULL(QUOTE(menu_name), 'NULL'),
                       IFNULL(menu_type, 'NULL'),
                       IFNULL(QUOTE(menu_code), 'NULL'),
                       IFNULL(QUOTE(menu_business_code), 'NULL'),
                       IFNULL(sort_order, 'NULL'),
                       IFNULL(is_display, 'NULL'),
                       IFNULL(menu_status, 'NULL'),
                       IFNULL(QUOTE(route_path), 'NULL'),
                       IFNULL(QUOTE(description), 'NULL'),
                       IFNULL(QUOTE(icon_type), 'NULL'),
                       IFNULL(QUOTE(icon_url), 'NULL'),
                       IFNULL(QUOTE(web_url), 'NULL'),
                       IFNULL(revision, 'NULL'),
                       IFNULL(QUOTE(button_key), 'NULL'),
                       IFNULL(button_type, 'NULL'),
                       IFNULL(QUOTE(operator), 'NULL'),
                       IFNULL(QUOTE(operator_name), 'NULL'),
                       IFNULL(delete_flag, 'NULL'),
                       IFNULL(delete_timestamp, 'NULL'),
                       IFNULL(QUOTE(create_time), 'NULL'),
                       IFNULL(QUOTE(modify_time), 'NULL'),
                       IFNULL(QUOTE(menu_tag), 'NULL'),
                       IFNULL(QUOTE(system_module_code), 'NULL'),
                       IFNULL(QUOTE(system_module_name), 'NULL'),
                       IFNULL(QUOTE(menu_abbr), 'NULL')
                      )
                     FROM (
                       SELECT t1.*
                       FROM tb_platform_sys_menu t1 
                       WHERE @menu_level = 1 AND t1.menu_name = @menu_1 AND t1.parent_id = 0 AND t1.delete_flag = 0
                        UNION ALL
                       SELECT t2.*
                       FROM tb_platform_sys_menu t1 
                       INNER JOIN tb_platform_sys_menu t2 ON t2.parent_id = t1.id AND t2.menu_name = @menu_2 AND t2.delete_flag = 0
                       WHERE @menu_level = 2 AND t1.menu_name = @menu_1 AND t1.parent_id = 0 AND t1.delete_flag = 0
                        UNION ALL
                       SELECT t3.*
                       FROM tb_platform_sys_menu t1 
                       INNER JOIN tb_platform_sys_menu t2 ON t2.parent_id = t1.id AND t2.menu_name = @menu_2 AND t2.delete_flag = 0
                       INNER JOIN tb_platform_sys_menu t3 ON t3.parent_id = t2.id AND t3.menu_name = @menu_3 AND t3.delete_flag = 0
                       WHERE @menu_level = 3 AND t1.menu_name = @menu_1 AND t1.parent_id = 0 AND t1.delete_flag = 0
                        UNION ALL
                       SELECT t4.*
                       FROM tb_platform_sys_menu t1 
                       INNER JOIN tb_platform_sys_menu t2 ON t2.parent_id = t1.id AND t2.menu_name = @menu_2 AND t2.delete_flag = 0
                       INNER JOIN tb_platform_sys_menu t3 ON t3.parent_id = t2.id AND t3.menu_name = @menu_3 AND t3.delete_flag = 0
                       INNER JOIN tb_platform_sys_menu t4 ON t4.parent_id = t3.id AND t4.menu_name = @menu_4 AND t4.delete_flag = 0
                       WHERE @menu_level = 4 AND t1.menu_name = @menu_1 AND t1.parent_id = 0 AND t1.delete_flag = 0
                        UNION ALL
                       SELECT t5.*
                       FROM tb_platform_sys_menu t1 
                       INNER JOIN tb_platform_sys_menu t2 ON t2.parent_id = t1.id AND t2.menu_name = @menu_2 AND t2.delete_flag = 0
                       INNER JOIN tb_platform_sys_menu t3 ON t3.parent_id = t2.id AND t3.menu_name = @menu_3 AND t3.delete_flag = 0
                       INNER JOIN tb_platform_sys_menu t4 ON t4.parent_id = t3.id AND t4.menu_name = @menu_4 AND t4.delete_flag = 0
                       INNER JOIN tb_platform_sys_menu t5 ON t5.parent_id = t4.id AND t5.menu_name = @menu_5 AND t5.delete_flag = 0
                       WHERE @menu_level = 5 AND t1.menu_name = @menu_1 AND t1.parent_id = 0 AND t1.delete_flag = 0
                     ) t));
										 		 
SET @error_msg = '';
SET @error_msg = IF(@menu_level = 0, CONCAT(@error_msg, '一级菜单未填;'), @error_msg);
SET @error_msg = IF((@menu_value IS NULL OR @menu_value = ''), CONCAT(@error_msg, '未能查询到对应菜单;'), @error_msg); 
										 
SET @menu_value = IF(@menu_level = 1, CONCAT('VALUES (', @menu_value, ');'), @menu_value);
SET @menu_value = IF(@menu_level > 1, CONCAT('SELECT ', @menu_value, '\n'), @menu_value);

-- 使用 SELECT CAST(你的字段 AS CHAR) FROM 表名; 语法将结果强行转换为 MySQL 的字符串类型
-- 拼接字符串，MySQL 的默认最大长度只有 1024 字节。你的结果是 1.05 KB（约 1075 字节），刚好超过了 1024 字节。此时，MySQL 不仅会截断你的数据，还会将返回的结果类型强制转为 BLOB，导致客户端显示为二进制块
SELECT 
    IF(@error_msg != '', @error_msg, 
        CAST(
            CONCAT(
                @field_value, 
                @menu_value,
                IF(@menu_level >= 2, 'FROM tb_platform_sys_menu t1 \n', ''),
                IF(@menu_level >= 3, CONCAT('INNER JOIN tb_platform_sys_menu t2 ON t2.parent_id = t1.id AND t2.menu_name = ', QUOTE(@menu_2), ' AND t2.delete_flag = 0 \n'), ''),
                IF(@menu_level >= 4, CONCAT('INNER JOIN tb_platform_sys_menu t3 ON t3.parent_id = t2.id AND t3.menu_name = ', QUOTE(@menu_3), ' AND t3.delete_flag = 0 \n'), ''),
                IF(@menu_level >= 5, CONCAT('INNER JOIN tb_platform_sys_menu t4 ON t4.parent_id = t3.id AND t4.menu_name = ', QUOTE(@menu_4), ' AND t4.delete_flag = 0 \n'), ''),
                IF(@menu_level >= 2, CONCAT('WHERE t1.menu_name = ', QUOTE(@menu_1), ' AND t1.parent_id = 0 AND t1.delete_flag = 0;'), '')
            )
        AS CHAR)
    ) AS generated_sql
FROM DUAL;

-- 结束：清理变量
SET @menu_1 = NULL, @menu_2 = NULL, @menu_3 = NULL, @menu_4 = NULL, @menu_5 = NULL;
SET @menu_level = NULL, @field_parent_id = NULL, @field_value = NULL, @menu_value = NULL, @error_msg = NULL;

存储过程用法 ​

用法：修改菜单层级的5个参数即可（目前最多支持5级菜单） 注意：没有存储过程可以先创建存储过程，或者直接使用原生脚本

sql
CALL get_menu_sql('用户中心', '敏感菜单配置', '新建', NULL, NULL);

存储过程创建脚本 ​

sql
DELIMITER //

CREATE PROCEDURE get_menu_sql(
    IN p_menu_1 VARCHAR(100),
    IN p_menu_2 VARCHAR(100),
    IN p_menu_3 VARCHAR(100),
    IN p_menu_4 VARCHAR(100),
    IN p_menu_5 VARCHAR(100)
)
BEGIN
    -- 声明局部变量，替代所有 @ 变量
    DECLARE v_menu_1 VARCHAR(100) DEFAULT p_menu_1;
    DECLARE v_menu_2 VARCHAR(100) DEFAULT p_menu_2;
    DECLARE v_menu_3 VARCHAR(100) DEFAULT p_menu_3;
    DECLARE v_menu_4 VARCHAR(100) DEFAULT p_menu_4;
    DECLARE v_menu_5 VARCHAR(100) DEFAULT p_menu_5;
    DECLARE v_menu_level INT;
    DECLARE v_field_parent_id VARCHAR(100);
    DECLARE v_field_value TEXT;
    DECLARE v_menu_value TEXT;
    DECLARE v_error_msg TEXT;

    -- 菜单层级（完全照搬原逻辑，只是将 @ 换成 v_）
    SET v_menu_level = CASE 
        WHEN v_menu_1 IS NULL THEN 0 
        WHEN v_menu_2 IS NULL THEN 1 
        WHEN v_menu_3 IS NULL THEN 2 
        WHEN v_menu_4 IS NULL THEN 3 
        WHEN v_menu_5 IS NULL THEN 4 
        ELSE 5 
    END;

    SET v_field_parent_id = CASE 
        WHEN v_menu_1 IS NULL THEN NULL 
        WHEN v_menu_2 IS NULL THEN '0' 
        WHEN v_menu_3 IS NULL THEN 't1.id' 
        WHEN v_menu_4 IS NULL THEN 't2.id' 
        WHEN v_menu_5 IS NULL THEN 't3.id' 
        ELSE 't4.id' 
    END;

    SET v_field_value = 'INSERT INTO `tb_platform_sys_menu` (`id`, `parent_id`, `system_code`, `system_name`, `menu_name`, `menu_type`, `menu_code`, `menu_business_code`, `sort_order`, `is_display`, `menu_status`, `route_path`, `description`, `icon_type`, `icon_url`, `web_url`, `revision`, `button_key`, `button_type`, `operator`, `operator_name`, `delete_flag`, `delete_timestamp`, `create_time`, `modify_time`, `menu_tag`, `system_module_code`, `system_module_name`, `menu_abbr`) \n';

    -- 注意：这里的子查询中要用到 v_ 变量，但表名和列名不变
    SET v_menu_value = IF(v_menu_level = 0, '', (
        SELECT CONCAT_WS(', ',
            id,
            v_field_parent_id,
            IFNULL(QUOTE(system_code), 'NULL'),
            IFNULL(QUOTE(system_name), 'NULL'),
            IFNULL(QUOTE(menu_name), 'NULL'),
            IFNULL(menu_type, 'NULL'),
            IFNULL(QUOTE(menu_code), 'NULL'),
            IFNULL(QUOTE(menu_business_code), 'NULL'),
            IFNULL(sort_order, 'NULL'),
            IFNULL(is_display, 'NULL'),
            IFNULL(menu_status, 'NULL'),
            IFNULL(QUOTE(route_path), 'NULL'),
            IFNULL(QUOTE(description), 'NULL'),
            IFNULL(QUOTE(icon_type), 'NULL'),
            IFNULL(QUOTE(icon_url), 'NULL'),
            IFNULL(QUOTE(web_url), 'NULL'),
            IFNULL(revision, 'NULL'),
            IFNULL(QUOTE(button_key), 'NULL'),
            IFNULL(button_type, 'NULL'),
            IFNULL(QUOTE(operator), 'NULL'),
            IFNULL(QUOTE(operator_name), 'NULL'),
            IFNULL(delete_flag, 'NULL'),
            IFNULL(delete_timestamp, 'NULL'),
            IFNULL(QUOTE(create_time), 'NULL'),
            IFNULL(QUOTE(modify_time), 'NULL'),
            IFNULL(QUOTE(menu_tag), 'NULL'),
            IFNULL(QUOTE(system_module_code), 'NULL'),
            IFNULL(QUOTE(system_module_name), 'NULL'),
            IFNULL(QUOTE(menu_abbr), 'NULL')
        )
        FROM (
            SELECT t1.*
            FROM tb_platform_sys_menu t1 
            WHERE v_menu_level = 1 AND t1.menu_name = v_menu_1 AND t1.parent_id = 0 AND t1.delete_flag = 0
            UNION ALL
            SELECT t2.*
            FROM tb_platform_sys_menu t1 
            INNER JOIN tb_platform_sys_menu t2 ON t2.parent_id = t1.id AND t2.menu_name = v_menu_2 AND t2.delete_flag = 0
            WHERE v_menu_level = 2 AND t1.menu_name = v_menu_1 AND t1.parent_id = 0 AND t1.delete_flag = 0
            UNION ALL
            SELECT t3.*
            FROM tb_platform_sys_menu t1 
            INNER JOIN tb_platform_sys_menu t2 ON t2.parent_id = t1.id AND t2.menu_name = v_menu_2 AND t2.delete_flag = 0
            INNER JOIN tb_platform_sys_menu t3 ON t3.parent_id = t2.id AND t3.menu_name = v_menu_3 AND t3.delete_flag = 0
            WHERE v_menu_level = 3 AND t1.menu_name = v_menu_1 AND t1.parent_id = 0 AND t1.delete_flag = 0
            UNION ALL
            SELECT t4.*
            FROM tb_platform_sys_menu t1 
            INNER JOIN tb_platform_sys_menu t2 ON t2.parent_id = t1.id AND t2.menu_name = v_menu_2 AND t2.delete_flag = 0
            INNER JOIN tb_platform_sys_menu t3 ON t3.parent_id = t2.id AND t3.menu_name = v_menu_3 AND t3.delete_flag = 0
            INNER JOIN tb_platform_sys_menu t4 ON t4.parent_id = t3.id AND t4.menu_name = v_menu_4 AND t4.delete_flag = 0
            WHERE v_menu_level = 4 AND t1.menu_name = v_menu_1 AND t1.parent_id = 0 AND t1.delete_flag = 0
            UNION ALL
            SELECT t5.*
            FROM tb_platform_sys_menu t1 
            INNER JOIN tb_platform_sys_menu t2 ON t2.parent_id = t1.id AND t2.menu_name = v_menu_2 AND t2.delete_flag = 0
            INNER JOIN tb_platform_sys_menu t3 ON t3.parent_id = t2.id AND t3.menu_name = v_menu_3 AND t3.delete_flag = 0
            INNER JOIN tb_platform_sys_menu t4 ON t4.parent_id = t3.id AND t4.menu_name = v_menu_4 AND t4.delete_flag = 0
            INNER JOIN tb_platform_sys_menu t5 ON t5.parent_id = t4.id AND t5.menu_name = v_menu_5 AND t5.delete_flag = 0
            WHERE v_menu_level = 5 AND t1.menu_name = v_menu_1 AND t1.parent_id = 0 AND t1.delete_flag = 0
        ) t
    ));

    SET v_error_msg = '';
    SET v_error_msg = IF(v_menu_level = 0, CONCAT(v_error_msg, '一级菜单未填;'), v_error_msg);
    SET v_error_msg = IF((v_menu_value IS NULL OR v_menu_value = ''), CONCAT(v_error_msg, '未能查询到对应菜单;'), v_error_msg); 

    SET v_menu_value = IF(v_menu_level = 1, CONCAT('VALUES (', v_menu_value, ');'), v_menu_value);
    SET v_menu_value = IF(v_menu_level > 1, CONCAT('SELECT ', v_menu_value, '\n'), v_menu_value);

    -- 直接返回结果集（无需 INTO 变量）
    SELECT 
        IF(v_error_msg != '', v_error_msg, 
            CAST(
                CONCAT(
                    v_field_value, 
                    v_menu_value,
                    IF(v_menu_level >= 2, 'FROM tb_platform_sys_menu t1 \n', ''),
                    IF(v_menu_level >= 3, CONCAT('INNER JOIN tb_platform_sys_menu t2 ON t2.parent_id = t1.id AND t2.menu_name = ', QUOTE(v_menu_2), ' AND t2.delete_flag = 0 \n'), ''),
                    IF(v_menu_level >= 4, CONCAT('INNER JOIN tb_platform_sys_menu t3 ON t3.parent_id = t2.id AND t3.menu_name = ', QUOTE(v_menu_3), ' AND t3.delete_flag = 0 \n'), ''),
                    IF(v_menu_level >= 5, CONCAT('INNER JOIN tb_platform_sys_menu t4 ON t4.parent_id = t3.id AND t4.menu_name = ', QUOTE(v_menu_4), ' AND t4.delete_flag = 0 \n'), ''),
                    IF(v_menu_level >= 2, CONCAT('WHERE t1.menu_name = ', QUOTE(v_menu_1), ' AND t1.parent_id = 0 AND t1.delete_flag = 0;'), '')
                )
            AS CHAR)
        ) AS generated_sql
    FROM DUAL;

    -- 无需清理变量，因为 DECLARE 的变量自动销毁
END //

DELIMITER ;

函数用法（当前云ERP数据库账号无权限创建函数） ​

用法：修改菜单层级的5个参数即可（目前最多支持5级菜单） 注意：当前云ERP数据库账号无权限创建函数，建议用存储过程方式或者原生脚本替代

sql
SELECT get_menu_sql('用户中心', '敏感菜单配置', '新建', NULL, NULL) FROM DUAL;

函数创建脚本（当前云ERP数据库账号无权限创建函数） ​

sql
DELIMITER //

CREATE FUNCTION get_menu_sql(
    p_menu_1 VARCHAR(100),
    p_menu_2 VARCHAR(100),
    p_menu_3 VARCHAR(100),
    p_menu_4 VARCHAR(100),
    p_menu_5 VARCHAR(100)
) RETURNS TEXT
DETERMINISTIC
BEGIN
    -- 声明局部变量，对应原脚本中的用户变量
    DECLARE v_menu_1 VARCHAR(100) DEFAULT p_menu_1;
    DECLARE v_menu_2 VARCHAR(100) DEFAULT p_menu_2;
    DECLARE v_menu_3 VARCHAR(100) DEFAULT p_menu_3;
    DECLARE v_menu_4 VARCHAR(100) DEFAULT p_menu_4;
    DECLARE v_menu_5 VARCHAR(100) DEFAULT p_menu_5;
    DECLARE v_menu_level INT;
    DECLARE v_field_parent_id VARCHAR(100);
    DECLARE v_field_value TEXT;
    DECLARE v_menu_value TEXT;
    DECLARE v_error_msg TEXT;
    DECLARE v_generated_sql TEXT;

    -- 1. 菜单层级计算（完全照搬原逻辑）
    SET v_menu_level = CASE
        WHEN v_menu_1 IS NULL THEN 0
        WHEN v_menu_2 IS NULL THEN 1
        WHEN v_menu_3 IS NULL THEN 2
        WHEN v_menu_4 IS NULL THEN 3
        WHEN v_menu_5 IS NULL THEN 4
        ELSE 5
    END;

    -- 2. 父ID字段（原样保留）
    SET v_field_parent_id = CASE
        WHEN v_menu_1 IS NULL THEN NULL
        WHEN v_menu_2 IS NULL THEN '0'
        WHEN v_menu_3 IS NULL THEN 't1.id'
        WHEN v_menu_4 IS NULL THEN 't2.id'
        WHEN v_menu_5 IS NULL THEN 't3.id'
        ELSE 't4.id'
    END;

    -- 3. INSERT 前缀（固定字符串，含换行，用 CONCAT 模拟）
    SET v_field_value = 'INSERT INTO `tb_platform_sys_menu` (`id`, `parent_id`, `system_code`, `system_name`, `menu_name`, `menu_type`, `menu_code`, `menu_business_code`, `sort_order`, `is_display`, `menu_status`, `route_path`, `description`, `icon_type`, `icon_url`, `web_url`, `revision`, `button_key`, `button_type`, `operator`, `operator_name`, `delete_flag`, `delete_timestamp`, `create_time`, `modify_time`, `menu_tag`, `system_module_code`, `system_module_name`, `menu_abbr`) \n';

    -- 4. 查询菜单并拼接字段值（完全照搬原 UNION ALL 结构）
    SET v_menu_value = IF(v_menu_level = 0, '', (
        SELECT CONCAT_WS(', ',
            id,
            v_field_parent_id,   -- 注意此处按原逻辑是字符串 't1.id' 等，保留
            IFNULL(QUOTE(system_code), 'NULL'),
            IFNULL(QUOTE(system_name), 'NULL'),
            IFNULL(QUOTE(menu_name), 'NULL'),
            IFNULL(menu_type, 'NULL'),
            IFNULL(QUOTE(menu_code), 'NULL'),
            IFNULL(QUOTE(menu_business_code), 'NULL'),
            IFNULL(sort_order, 'NULL'),
            IFNULL(is_display, 'NULL'),
            IFNULL(menu_status, 'NULL'),
            IFNULL(QUOTE(route_path), 'NULL'),
            IFNULL(QUOTE(description), 'NULL'),
            IFNULL(QUOTE(icon_type), 'NULL'),
            IFNULL(QUOTE(icon_url), 'NULL'),
            IFNULL(QUOTE(web_url), 'NULL'),
            IFNULL(revision, 'NULL'),
            IFNULL(QUOTE(button_key), 'NULL'),
            IFNULL(button_type, 'NULL'),
            IFNULL(QUOTE(operator), 'NULL'),
            IFNULL(QUOTE(operator_name), 'NULL'),
            IFNULL(delete_flag, 'NULL'),
            IFNULL(delete_timestamp, 'NULL'),
            IFNULL(QUOTE(create_time), 'NULL'),
            IFNULL(QUOTE(modify_time), 'NULL'),
            IFNULL(QUOTE(menu_tag), 'NULL'),
            IFNULL(QUOTE(system_module_code), 'NULL'),
            IFNULL(QUOTE(system_module_name), 'NULL'),
            IFNULL(QUOTE(menu_abbr), 'NULL')
        )
        FROM (
            SELECT t1.*
            FROM tb_platform_sys_menu t1
            WHERE v_menu_level = 1 AND t1.menu_name = v_menu_1 AND t1.parent_id = 0 AND t1.delete_flag = 0
            UNION ALL
            SELECT t2.*
            FROM tb_platform_sys_menu t1
            INNER JOIN tb_platform_sys_menu t2 ON t2.parent_id = t1.id AND t2.menu_name = v_menu_2 AND t2.delete_flag = 0
            WHERE v_menu_level = 2 AND t1.menu_name = v_menu_1 AND t1.parent_id = 0 AND t1.delete_flag = 0
            UNION ALL
            SELECT t3.*
            FROM tb_platform_sys_menu t1
            INNER JOIN tb_platform_sys_menu t2 ON t2.parent_id = t1.id AND t2.menu_name = v_menu_2 AND t2.delete_flag = 0
            INNER JOIN tb_platform_sys_menu t3 ON t3.parent_id = t2.id AND t3.menu_name = v_menu_3 AND t3.delete_flag = 0
            WHERE v_menu_level = 3 AND t1.menu_name = v_menu_1 AND t1.parent_id = 0 AND t1.delete_flag = 0
            UNION ALL
            SELECT t4.*
            FROM tb_platform_sys_menu t1
            INNER JOIN tb_platform_sys_menu t2 ON t2.parent_id = t1.id AND t2.menu_name = v_menu_2 AND t2.delete_flag = 0
            INNER JOIN tb_platform_sys_menu t3 ON t3.parent_id = t2.id AND t3.menu_name = v_menu_3 AND t3.delete_flag = 0
            INNER JOIN tb_platform_sys_menu t4 ON t4.parent_id = t3.id AND t4.menu_name = v_menu_4 AND t4.delete_flag = 0
            WHERE v_menu_level = 4 AND t1.menu_name = v_menu_1 AND t1.parent_id = 0 AND t1.delete_flag = 0
            UNION ALL
            SELECT t5.*
            FROM tb_platform_sys_menu t1
            INNER JOIN tb_platform_sys_menu t2 ON t2.parent_id = t1.id AND t2.menu_name = v_menu_2 AND t2.delete_flag = 0
            INNER JOIN tb_platform_sys_menu t3 ON t3.parent_id = t2.id AND t3.menu_name = v_menu_3 AND t3.delete_flag = 0
            INNER JOIN tb_platform_sys_menu t4 ON t4.parent_id = t3.id AND t4.menu_name = v_menu_4 AND t4.delete_flag = 0
            INNER JOIN tb_platform_sys_menu t5 ON t5.parent_id = t4.id AND t5.menu_name = v_menu_5 AND t5.delete_flag = 0
            WHERE v_menu_level = 5 AND t1.menu_name = v_menu_1 AND t1.parent_id = 0 AND t1.delete_flag = 0
        ) t
    ));

    -- 5. 错误信息收集
    SET v_error_msg = '';
    SET v_error_msg = IF(v_menu_level = 0, CONCAT(v_error_msg, '一级菜单未填;'), v_error_msg);
    SET v_error_msg = IF((v_menu_value IS NULL OR v_menu_value = ''), CONCAT(v_error_msg, '未能查询到对应菜单;'), v_error_msg);

    -- 6. 根据层级组装最终 SQL（VALUES 或 SELECT 模式，完全照搬）
    SET v_menu_value = IF(v_menu_level = 1, CONCAT('VALUES (', v_menu_value, ');'), v_menu_value);
    SET v_menu_value = IF(v_menu_level > 1, CONCAT('SELECT ', v_menu_value, '\n'), v_menu_value);

    -- 7. 生成最终的 SQL 字符串（原脚本最后的 SELECT）
    SELECT
        IF(v_error_msg != '', v_error_msg,
            CAST(
                CONCAT(
                    v_field_value,
                    v_menu_value,
                    IF(v_menu_level >= 2, 'FROM tb_platform_sys_menu t1 \n', ''),
                    IF(v_menu_level >= 3, CONCAT('INNER JOIN tb_platform_sys_menu t2 ON t2.parent_id = t1.id AND t2.menu_name = ', QUOTE(v_menu_2), ' AND t2.delete_flag = 0 \n'), ''),
                    IF(v_menu_level >= 4, CONCAT('INNER JOIN tb_platform_sys_menu t3 ON t3.parent_id = t2.id AND t3.menu_name = ', QUOTE(v_menu_3), ' AND t3.delete_flag = 0 \n'), ''),
                    IF(v_menu_level >= 5, CONCAT('INNER JOIN tb_platform_sys_menu t4 ON t4.parent_id = t3.id AND t4.menu_name = ', QUOTE(v_menu_4), ' AND t4.delete_flag = 0 \n'), ''),
                    IF(v_menu_level >= 2, CONCAT('WHERE t1.menu_name = ', QUOTE(v_menu_1), ' AND t1.parent_id = 0 AND t1.delete_flag = 0;'), '')
                )
            AS CHAR)
        ) INTO v_generated_sql
    FROM DUAL;

    -- 8. 返回结果
    RETURN v_generated_sql;
END //

DELIMITER ;

最后更新于:

页脚：版权前显示的信息

页脚：实际的版权文本