代码之家 › 专栏 › 技术社区 › Martin Greenaway

迁移到MySQL8时,存储过程中的游标导致导入错误

database-cursor moodle mysqli stored-procedures mysql

0

Martin Greenaway · 技术社区 · 4 年前

我正在将一个Moodle站点迁移到MySQL8,核心系统没有问题。但是,我们的一些报告依赖于两个自定义存储过程,其中一个如下所示:

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;
DECLARE session_start integer;
DECLARE done INT DEFAULT FALSE;
    
DECLARE cur CURSOR FOR SELECT  @rownum := @rownum + 1 as row_number, 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_number, 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_number = 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 ;;

导入这些在5.7.x上运行正常的存储过程时,出现以下错误:

第36行出现错误1064(42000):SQL语法有错误; 正确的语法使用“row\u number,timecreated FROM”

0 回复 | 直到 4 年前

1

2

Bill Karwin 4 年前

ROW_NUMBER 是MySQL 8.0中的保留关键字。看到了吗 https://dev.mysql.com/doc/refman/8.0/en/keywords.html

如果需要将其用作标识符,请在每次使用时将其括在后面的记号中。

... SELECT  @rownum := @rownum + 1 as `row_number`, ...

或者将其重命名为非保留关键字。

... SELECT  @rownum := @rownum + 1 as rownum, ...

还要注意的是,在MySQL 8.0中,它们不鼓励在查询中将变量作为副作用进行分配。 https://dev.mysql.com/doc/refman/8.0/en/user-variables.html 说:

MySQL的早期版本允许在SET以外的语句中为用户变量赋值。为了向后兼容,MySQL8.0支持此功能,但在将来的MySQL版本中可能会删除此功能。

ROW_NUMBER() 是for,因此可以将查询重写为:

SELECT ROW_NUMBER() OVER (ORDER BY timecreated) AS rownum, timecreated
FROM moodle.mdl_logstore_standard_log
WHERE userid=USERID_IN
ORDER BY timecreated ASC;

ROW_NUMBER()只是MySQL 8.0中实现的许多标准窗口函数之一。看到了吗 https://dev.mysql.com/doc/refman/8.0/en/window-functions.html

:= 用于内联变量赋值。窗口函数是非常多功能的,可以做一些使用内联变量赋值技术非常困难的事情。