迁移至MySQL8时Moodle自定义存储过程cursor导入报错如何解决

• 保留关键字冲突：MySQL 8.0 新增内置窗口函数 ROW_NUMBER()，原存储过程中将自定义序号的别名设置为row_number，属于未转义的保留关键字，直接触发语法报错。
• 潜在兼容性风险：MySQL 8.0 对@开头的用户自定义变量的赋值、遍历行为做了调整，原有依赖用户变量生成序号的逻辑就算修复别名问题，运行结果也可能和MySQL5.7不一致。
• 原代码效率缺陷：双游标的实现方式会产生N次嵌套查询，日志量较大时执行效率极低。

把所有查询中as row_number的别名替换为非关键字（比如row_num），修改后的完整存储过程如下：

DELIMITER ;;
CREATE DEFINER=`someuseraccount`@`somewhere` FUNCTION `getOnlineTimeForUser`(USERID_IN int) RETURNS int(11)
BEGIN

DECLARE currenttime integer;
DECLARE nexttime integer;
DECLARE count1 integer;
DECLARE count2 integer;
DECLARE totaldedication_time integer DEFAULT 0;
DECLARE dedication integer DEFAULT 0;
DECLARE session_start integer;
DECLARE done INT DEFAULT FALSE;
    
DECLARE cur CURSOR FOR SELECT  @rownum := @rownum + 1 as row_num, timecreated
FROM moodle.mdl_logstore_standard_log
cross join (select @rownum := 0) r
where userid=USERID_IN
order by timecreated asc;

DECLARE cur2 CURSOR FOR SELECT a.* FROM (SELECT  @rownum := @rownum + 1 as row_num, timecreated
FROM moodle.mdl_logstore_standard_log
cross join (select @rownum := 0) r
where userid=USERID_IN
order by timecreated asc) a where a.row_num = count1+1;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;
    read_loop: LOOP

        FETCH cur INTO count1, currenttime;
        IF done THEN
            LEAVE read_loop;
        END IF;
        IF (count1 < 2) THEN
            SET session_start = currenttime;
            SET totaldedication_time = 0;
        END IF;
        
        OPEN cur2;
            read_loop2: LOOP
                FETCH cur2 INTO count2, nexttime;
                IF done THEN
                    SET done = FALSE;
                    LEAVE read_loop2;
                END IF;

                IF (nexttime - currenttime) > 300 THEN
                    SET dedication = currenttime - session_start;
                    SET totaldedication_time = totaldedication_time + dedication;
                    SET session_start = nexttime;
                END IF;
            END LOOP;
        CLOSE cur2;
    END LOOP;
CLOSE cur;

SET totaldedication_time = totaldedication_time + dedication;

RETURN ceil(totaldedication_time/60);
    
END ;;
DELIMITER ;

注：额外给dedication变量加了默认值0，避免无符合条件的日志时返回NULL的问题。

利用MySQL8原生窗口函数直接实现相同逻辑，无需游标、无需自定义变量，代码更简洁执行效率更高，逻辑和原存储过程完全一致：

DELIMITER ;;
CREATE DEFINER=`someuseraccount`@`somewhere` FUNCTION `getOnlineTimeForUser`(USERID_IN int) RETURNS int(11)
BEGIN
    DECLARE total_min INT DEFAULT 0;
    SELECT CEIL(SUM(session_len)/60) INTO total_min
    FROM (
        SELECT 
            MAX(timecreated) - MIN(timecreated) AS session_len
        FROM (
            SELECT 
                timecreated,
                SUM(flag) OVER(ORDER BY timecreated) AS session_id
            FROM (
                SELECT 
                    timecreated,
                    IF(timecreated - LAG(timecreated) OVER(ORDER BY timecreated) > 300, 1, 0) AS flag
                FROM moodle.mdl_logstore_standard_log
                WHERE userid = USERID_IN
                ORDER BY timecreated
            ) t1
        ) t2
        GROUP BY session_id
    ) t3;
    RETURN IFNULL(total_min, 0);
END ;;
DELIMITER ;

逻辑说明：

• 用LAG()窗口函数取当前日志和上一条日志的时间差
• 时间差超过300秒就标记为新会话的起点，累加标记生成会话ID
• 按会话ID分组计算每个会话的时长，求和后转分钟向上取整

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