MySQL游标仅循环1次：自定义时间计算函数返回异常结果

我编写了一个MySQL函数用于统计指定用户30天内的游戏时长，函数代码如下：

BEGIN
DECLARE done INT DEFAULT 0;

DECLARE type VARCHAR(255) DEFAULT '';
DECLARE cas DATETIME DEFAULT 0;
DECLARE starttime DATETIME DEFAULT 0;
DECLARE total INT DEFAULT 0;
 
DECLARE cur CURSOR FOR
    SELECT type,date from log WHERE nick = name AND (type = 'connect' OR type = 'disconnect') AND date > (CURRENT_TIMESTAMP - INTERVAL 30 DAY) ORDER BY id ASC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
label: LOOP
    FETCH cur INTO type,cas;
    SET total = total + 1;/*only for testing number of loops*/
    IF type = 'connect' THEN
            SET starttime = cas;
    END IF;
        IF type = 'disconnect' THEN
            SET total = total + (cas-startime);
        END IF;

    IF done = 1 THEN 
        LEAVE label;
    END IF;
END LOOP label;
CLOSE cur;
  
RETURN total;

调用语句：

select GetGameTimeFromMonth('ATomas'); 

执行后返回结果为1，但log表中符合条件的数据有数千行，游标仅执行了1次循环，请问该如何解决？

核心问题分析 #

1. 参数名冲突：游标查询中nick = name的name大概率是函数参数，但如果未明确区分参数与表字段（比如log表存在name字段），MySQL会将name解析为表字段而非传入的参数，导致查询返回空结果，游标第一次FETCH就触发NOT FOUND，但代码先执行了total +=1再退出，最终返回1。
2. 循环逻辑顺序错误：done的判断放在业务逻辑之后，即使FETCH失败也会执行一次无效的统计操作。
3. 时长计算逻辑错误：直接用cas - starttime计算时间差，MySQL返回的是YYYYMMDDHHMMSS格式的数值差，并非实际秒数。
4. 变量名风险：使用type这类MySQL关键字作为变量名，可能引发解析异常。

修正后的函数代码 #

CREATE FUNCTION GetGameTimeFromMonth(p_nick VARCHAR(255)) RETURNS INT
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE log_type VARCHAR(255) DEFAULT ''; -- 避免关键字冲突
DECLARE cas DATETIME DEFAULT NULL;
DECLARE starttime DATETIME DEFAULT NULL;
DECLARE total_seconds INT DEFAULT 0; -- 明确统计秒数

DECLARE cur CURSOR FOR
    SELECT type, `date` from log 
    WHERE nick = p_nick 
      AND type IN ('connect', 'disconnect') 
      AND `date` > CURRENT_TIMESTAMP - INTERVAL 30 DAY 
    ORDER BY id ASC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur;
label: LOOP
    FETCH cur INTO log_type, cas;
    -- 先判断是否读取到有效数据，无数据直接退出
    IF done = 1 THEN 
        LEAVE label;
    END IF;

    IF log_type = 'connect' THEN
        SET starttime = cas;
    ELSEIF log_type = 'disconnect' THEN
        -- 避免无对应connect的异常情况
        IF starttime IS NOT NULL THEN
            SET total_seconds = total_seconds + TIMESTAMPDIFF(SECOND, starttime, cas);
            SET starttime = NULL; -- 重置，防止重复计算
        END IF;
    END IF;
END LOOP label;
CLOSE cur;
  
RETURN total_seconds;
END

关键修正点说明 #

• 参数名明确化：将函数参数改为p_nick，避免与表字段冲突，确保查询条件正确匹配传入的用户名。
• 循环逻辑调整：FETCH后立即判断done，仅在读取到有效数据时执行统计逻辑，避免无效循环。
• 时长计算修复：用TIMESTAMPDIFF(SECOND, starttime, cas)准确计算两次操作的秒数差。
• 变量名优化：替换type为log_type，规避关键字解析风险；将total改为total_seconds，明确统计单位。
• 异常防护：增加starttime空值判断，避免处理无对应连接记录的断开操作。

验证步骤 #

1. 先单独执行游标对应的查询语句，确认是否返回预期数据：

SELECT type, `date` from log 
WHERE nick = 'ATomas' 
  AND type IN ('connect', 'disconnect') 
  AND `date` > CURRENT_TIMESTAMP - INTERVAL 30 DAY 
ORDER BY id ASC;

若返回数千行，说明参数与过滤条件正常；若返回空，检查nick字段值或时间范围是否合理。
2. 调用修正后的函数，验证返回结果是否符合实际游戏时长统计。

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