MySQL存储过程使用CURSOR时FETCH NEXT回退及循环异常解决咨询

核心限制与问题根因 #

• MySQL 原生游标仅支持单向向前遍历，无回退、跳转语法，无法实现FETCH NEXT后回退到上一行的操作
• 当前存储过程单次循环执行2次FETCH操作，每次消耗2行数据，导致4行数据仅循环2次，属于逻辑设计错误，无需硬抠游标回退能力，换用变量缓存上一行数据即可实现相邻行差值计算

修改后的存储过程 #

DELIMITER //
DROP PROCEDURE IF EXISTS calculation //

CREATE PROCEDURE calculation (IN workDayId INT)
BEGIN 
    -- 时长变量改用DECIMAL避免整数截断
    DECLARE TimeSpan DECIMAL(18,3) DEFAULT 0;
    DECLARE BreakTime DECIMAL(18,3) DEFAULT 0;
    DECLARE cur_Id BIGINT DEFAULT 0;
    DECLARE cur_TimeStamp DATETIME;
    DECLARE cur_WorkDayId BIGINT DEFAULT 0;
    DECLARE cur_EmployeeId BIGINT DEFAULT 0;
    DECLARE cur_Type VARCHAR(10);
    -- 上一行数据缓存变量
    DECLARE prev_Id BIGINT DEFAULT 0;
    DECLARE prev_TimeStamp DATETIME;
    DECLARE prev_Type VARCHAR(10);
    DECLARE cur_IdList VARCHAR(100) DEFAULT "";
    DECLARE done INT DEFAULT FALSE;
    -- 游标按时间升序取数
    DECLARE cursor_clockIn CURSOR FOR 
        SELECT c.Id, c.TimeStamp, c.WorkDayId, c.EmployeeId, c.`Type` 
        FROM clockintest c 
        WHERE c.WorkDayId = workDayId 
        ORDER BY c.TimeStamp ASC;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cursor_clockIn;
    SET TimeSpan = 0;
    SET BreakTime = 0;
    -- 预取第一行作为初始上一行缓存
    FETCH cursor_clockIn INTO prev_Id, prev_TimeStamp, prev_WorkDayId, prev_EmployeeId, prev_Type;
    IF done THEN
        CLOSE cursor_clockIn;
        SELECT TimeSpan, BreakTime, cur_IdList;
        UPDATE workday w SET w.TimeSpan = TimeSpan, w.BreakTime = BreakTime WHERE w.Id = workDayId;
    END IF;
    SET cur_IdList = CONCAT(cur_IdList,';', prev_Id);

    loop_through_rows: LOOP
        -- 每次循环仅取一次当前行
        FETCH cursor_clockIn INTO cur_Id, cur_TimeStamp, cur_WorkDayId, cur_EmployeeId, cur_Type;
        IF done THEN 
            LEAVE loop_through_rows;
        END IF;
        -- 用当前行和上一行缓存做差值计算
        IF prev_Type = 'Start' THEN
            SET TimeSpan = TimeSpan + TIMESTAMPDIFF(MICROSECOND, prev_TimeStamp, cur_TimeStamp) / 1000;
        ELSEIF prev_Type = 'End' THEN
            SET BreakTime = BreakTime + TIMESTAMPDIFF(MICROSECOND, prev_TimeStamp, cur_TimeStamp) / 1000;
        END IF;
        SET cur_IdList = CONCAT(cur_IdList,';', cur_Id );
        -- 更新上一行缓存为当前行，供下一次循环使用
        SET prev_Id = cur_Id;
        SET prev_TimeStamp = cur_TimeStamp;
        SET prev_Type = cur_Type;
    END LOOP;

    SELECT TimeSpan, BreakTime, cur_IdList;
    UPDATE workday w SET w.TimeSpan = TimeSpan, w.BreakTime = BreakTime WHERE w.Id = workDayId;
    CLOSE cursor_clockIn;
END //

DELIMITER ;

-- 测试调用
CALL calculation (149);

逻辑说明 #

1. 预取第一行数据存入上一行缓存变量，避免首次循环无对比数据
2. 每次循环仅读取1次当前行数据，和上一行缓存值计算后，将当前行赋值给上一行缓存，实现相邻行逐行对比，4行数据会执行3次计算，符合业务需求
3. 无需游标回退操作，完全兼容MySQL游标单向遍历的限制
4. 调整变量类型避免整数截断导致的时长精度丢失

内容的提问来源于stack exchange，提问作者Abhijit Mondal Abhi