MySQL 8存储过程中如何使用双游标并返回树形结果集？

问题概述 #

需要编写存储过程合并多表数据，返回UserPermisionTickets→UserName→TicketUser的树形结构结果集，使用双游标实现时遇到两个核心问题：

1. 如何为第二个游标传递外部循环的参数
2. 如何返回合并后的结果集
   同时编译时出现语法错误：SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':user_id;

错误原因与修复 #

原代码中DECLARE cursorTicketUser CURSOR FOR ... where user_id = :user_id;的:user_id是错误语法，MySQL不支持这种参数绑定方式，且普通游标为静态结构，无法直接动态更新查询条件。

完整解决方案代码 #

DELIMITER $$

DROP PROCEDURE IF EXISTS getUserPermissionTickets $$

CREATE PROCEDURE getUserPermissionTickets(IN permission_id INT, IN user_id INT)
BEGIN
    -- 声明变量
    DECLARE userId INT;
    DECLARE userName VARCHAR(255);
    DECLARE permissionName VARCHAR(255);
    DECLARE userCreatedAt DATETIME;
    DECLARE ticketId INT;
    DECLARE teamLead tinyint(1);
    DECLARE percent tinyint;
    -- 游标结束标志
    DECLARE done INT DEFAULT 0;
    DECLARE doneTicket INT DEFAULT 0;

    -- 外部游标：获取用户权限信息，添加permission_id过滤
    DECLARE cursorUserPermission CURSOR FOR
        SELECT users.id as user_id, users.name as user_name, spt_permissions.name as permission_name, users.created_at as user_created_at
        FROM spt_model_has_roles
        JOIN users ON users.id = spt_model_has_roles.model_id
        JOIN spt_permissions ON spt_permissions.id = spt_model_has_roles.role_id
        WHERE spt_permissions.id = permission_id;

    -- 内部游标：绑定外部循环的userId变量
    DECLARE cursorTicketUser CURSOR FOR 
        SELECT ticket_id, team_lead, percent FROM ticket_user WHERE user_id = userId;

    -- 设置游标结束处理逻辑
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET doneTicket = 1;

    -- 创建临时表存储合并结果
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_user_tickets (
        user_id INT,
        user_name VARCHAR(255),
        permission_name VARCHAR(255),
        user_created_at DATETIME,
        ticket_id INT,
        team_lead tinyint(1),
        percent tinyint
    );

    -- 启动外部循环
    OPEN cursorUserPermission;
    userPermissionLoop: LOOP
        FETCH cursorUserPermission INTO userId, userName, permissionName, userCreatedAt;
        -- 外部游标遍历完成则退出
        IF done = 1 THEN
            LEAVE userPermissionLoop;
        END IF;

        -- 重置内部游标结束标志，准备遍历当前用户的ticket
        SET doneTicket = 0;
        OPEN cursorTicketUser;

        TicketUserLoop: LOOP
            FETCH cursorTicketUser INTO ticketId, teamLead, percent;
            -- 内部游标遍历完成则退出
            IF doneTicket = 1 THEN
                LEAVE TicketUserLoop;
            END IF;

            -- 将用户与ticket数据合并插入临时表
            INSERT INTO temp_user_tickets 
            VALUES (userId, userName, permissionName, userCreatedAt, ticketId, teamLead, percent);
        END LOOP TicketUserLoop;
        CLOSE cursorTicketUser;

        -- 可选：若用户无ticket，插入仅用户基础信息的记录
        IF doneTicket = 1 THEN
            INSERT INTO temp_user_tickets (user_id, user_name, permission_name, user_created_at)
            VALUES (userId, userName, permissionName, userCreatedAt);
        END IF;
    END LOOP userPermissionLoop;
    CLOSE cursorUserPermission;

    -- 返回最终合并结果集
    SELECT * FROM temp_user_tickets;

    -- 清理临时表（会话结束后自动删除，可选执行）
    DROP TEMPORARY TABLE IF EXISTS temp_user_tickets;

END $$

DELIMITER ;

核心问题解决说明 #

1. 第二个游标传递外部参数 #

• 移除错误的:user_id语法，直接使用外部循环的变量userId作为内部游标的查询条件
• 利用MySQL游标执行时机特性：每次外部循环获取新的userId后再打开内部游标，此时游标会使用当前userId的值执行查询，实现动态参数传递
• 为两个游标分别设置独立的NOT FOUND处理程序，避免循环冲突

2. 返回合并结果集 #

• 创建临时表temp_user_tickets，覆盖用户信息与ticket信息的所有字段
• 在内部循环中，将当前用户信息与对应ticket数据合并插入临时表
• 若用户无关联ticket，可选插入仅用户基础信息的记录（根据业务需求调整）
• 最后通过SELECT * FROM temp_user_tickets返回完整的合并结果

额外优化点 #

• 将原隐式JOIN改为显式JOIN语法，提高代码可读性与兼容性
• 启用传入参数permission_id的过滤逻辑（原代码未使用该参数）
• 添加游标边界处理，防止无限循环

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