SQL存储函数getRunPointValue触发1365除零错误排查

数据老陈

2026-8-27

跑酷积分模块SQL除零错误排查

问题描述

开发游戏跑酷记录积分核算、排名更新模块时，编写了2个自定义SQL函数与1个触发器，功能调用入口示例：

SELECT insertRun(76561198117520501, 99, 0, 757.91015625, 0, TRUE) as run_id

运行过程中getRunPointValue函数会抛出除零错误，已在两处显式除法运算前添加防护逻辑：检测到除数为0时强制将其赋值为1，理论上所有参与显式除法运算的数值都不应为0。若取消insertRun函数中被注释的UPDATE语句，该问题会消失，但该语句是新记录参与排名计算的必要逻辑，移除后新提交的跑酷记录无法完成排名。
经实际观测，函数在99%的场景下可正常运行，仅在提交记录排名为第1位或末位等边缘场景下触发除零错误，返回的错误信息为：

1365, Division by zero

问题根因

现有防护只覆盖了代码中显式写的/除法运算，漏掉了两类隐式除零场景，恰好对应排名首尾的边缘case：

对数运算隐式除零：MySQL中LOG(底数, 真数)的底层计算逻辑为LN(真数)/LN(底数)，当底数bracket_max为1时，LN(1)=0，直接触发除零。当地图总完成数小于20时，前5%排名的人数不足1人，计算出的bracket_max = total_completions * 0.05结果小于等于1，就会触发这个问题，对应排名第1的报错场景。
百分位分支未覆盖导致bracket_max为初始值0：现有逻辑只处理了排名在前25%的记录，当记录排名在25%之后（即末位场景），四个百分位判断分支都不会命中，bracket_max会保持声明时的默认值0，传入LOG函数后直接触发除零。

现有完整代码

CREATE FUNCTION insertRun(userid BIGINT UNSIGNED, mid INT, styleid INT, run_time FLOAT(12,4), type INT, best BOOLEAN)
RETURNS INT
BEGIN
    DECLARE runid INT;
    DECLARE totalPoints INT DEFAULT 0;
    IF best = TRUE THEN
        UPDATE surf_run SET best_run = FALSE WHERE user_id = userid AND map_id = mid AND style = styleid AND run_type = type;
    END IF;
    INSERT INTO surf_run (user_id, map_id, style, time, run_type, best_run) VALUES (userid, mid, styleid, run_time, type, best);
    -- if best_run is true then set all other runs by user_id and map_id to false
    SELECT run_id INTO runid FROM surf_run WHERE user_id = userid AND map_id = mid AND style = styleid AND run_type = type AND best_run = TRUE ORDER BY run_id DESC LIMIT 1;
    --UPDATE surf_run SET points = getRunPointValue(runid) WHERE run_id = runid;
    SELECT SUM(points) INTO totalPoints FROM surf_run WHERE user_id = userid AND best_run=TRUE;
    UPDATE surf_user SET points = totalPoints WHERE user_id = userid;
    RETURN runid;
END;


CREATE FUNCTION getRunPointValue(id INT)
RETURNS INT
exit_getrunpointvalue:BEGIN
    DECLARE points INT DEFAULT 10;
    DECLARE total_completions INT DEFAULT 1;
    DECLARE place INT DEFAULT 1;
    DECLARE run_time FLOAT(12,4);
    DECLARE run_style INT;
    DECLARE type INT;
    DECLARE mapid INT;
    DECLARE maptier INT;
    DECLARE tierMulti FLOAT(3,2) DEFAULT 1.0;

    DECLARE percentile FLOAT(12,4);
    DECLARE total_points INT DEFAULT 3000;
    DECLARE percentile_potential INT;
    DECLARE completionsbonus INT DEFAULT 0;

    DECLARE temp FLOAT;

    DECLARE bracket_min INT DEFAULT 0;
    DECLARE bracket_max INT DEFAULT 0;

    SELECT time INTO run_time FROM surf_run WHERE run_id = id LIMIT 1;
    SELECT style INTO run_style FROM surf_run WHERE run_id = id;
    SELECT run_type INTO type FROM surf_run WHERE run_id = id;
    SELECT map_id INTO mapid FROM surf_run WHERE run_id = id;
    SELECT tier INTO maptier FROM surf_map WHERE map_id = mapid;

    IF type < 0 THEN
        LEAVE exit_getrunpointvalue;
    END IF;

    SELECT COUNT(*) INTO place FROM surf_run WHERE map_id = mapid AND time <= run_time AND style = run_style AND run_type = type AND best_run = TRUE;
    SELECT COUNT(*) INTO total_completions FROM surf_run WHERE map_id = mapid AND style = run_style AND run_type = type AND best_run = TRUE;

    IF total_completions <= 0 THEN
        SET total_completions = 1;
    END IF;
    SET completionsbonus = FLOOR((total_completions / 2) * 0.6);

    IF maptier = 1 THEN
        SET tierMulti = 1.0;
    ELSEIF maptier = 2 THEN
        SET tierMulti = 1.53;
    ELSEIF maptier = 3 THEN
        SET tierMulti = 2.5;
    ELSEIF maptier = 4 THEN
        SET tierMulti = 3.8;
    ELSEIF maptier = 5 THEN
        SET tierMulti = 6.0;
    ELSEIF maptier = 6 THEN
        SET tierMulti = 8.5;
    END IF;

    IF place = 0 THEN
        SET place = 1;
    END IF;

    IF place = 1 THEN
        SET points = 1500 + completionsbonus;
    ELSEIF place = 2 THEN
        SET points = 1250 + completionsbonus;
    ELSEIF place = 3 THEN
        SET points = 1100 + completionsbonus;
    ELSEIF place = 4 THEN
        SET points = 1000 + completionsbonus;
    ELSEIF place = 5 THEN
        SET points = 900 + completionsbonus;
    ELSE

        SET percentile = (place / total_completions);

        IF percentile <= 0.05 THEN
            SET percentile_potential = 800;
            SET points = 600;
            SET bracket_min = 0;
            SET bracket_max = total_completions * 0.05;
        ELSEIF percentile <= 0.10 THEN
            SET bracket_max = total_completions * 0.05 + 1;
            SET bracket_max = total_completions * 0.1;
            SET percentile_potential = 600;
            SET points = 450;
        ELSEIF percentile <= 0.15 THEN
            SET bracket_max = total_completions * 0.1 + 1;
            SET bracket_max = total_completions * 0.15;
            SET percentile_potential = 450;
            SET points = 200;
        ELSEIF percentile <= 0.25 THEN
            SET bracket_max = total_completions * 0.15 + 1;
            SET bracket_max = total_completions * 0.25;
            SET percentile_potential = 200;
            SET points = 10;
        END IF;
        SET points = points + GREATEST(0, ROUND(percentile_potential - percentile_potential * LOG(place, bracket_max))) + completionsbonus;
    END IF;
    RETURN FLOOR(points * tierMulti);
END;


CREATE TRIGGER updateRunPointValue AFTER INSERT ON surf_run FOR EACH ROW
exit_updaterunpoints_trugger:BEGIN

    DECLARE place INT DEFAULT 0;
    DECLARE total INT DEFAULT 1;
    DECLARE runoffset INT DEFAULT 0;

    DECLARE worst_time FLOAT(12,4);

    IF NEW.run_type < 0 THEN
        LEAVE exit_updaterunpoints_trugger;
    END IF;

    SELECT COUNT(*) INTO place FROM surf_run WHERE map_id = NEW.map_id AND time <= NEW.time AND style = NEW.style AND run_type = NEW.run_type AND best_run = TRUE;
    SELECT COUNT(*) INTO total FROM surf_run WHERE map_id = NEW.map_id AND style = NEW.style AND run_type = NEW.run_type AND best_run = TRUE;

    -- if total < 250 then
    --  LEAVE exit_updaterunpoints_trugger;
    -- END IF;

    SET runoffset = (total * 0.25) + 1;
    
    SELECT time INTO worst_time FROM surf_run WHERE map_id = NEW.map_id AND style = NEW.style AND run_type = NEW.run_type AND best_run = TRUE ORDER BY time DESC LIMIT 1 OFFSET runoffset;
    call rerunPointValue(NEW.map_id, worst_time, NEW.style, NEW.run_type);

END;

修复方法

修改getRunPointValue函数中place>5的分支逻辑：

补全percentile>0.25的兜底分支，给低排名记录设置对应的积分规则和bracket_max值
所有bracket_max赋值后增加最小值校验，强制保证bracket_max >= 2，避免对数底数为0或1触发隐式除零
修复后的核心逻辑片段参考：

ELSE
    SET percentile = (place / total_completions);

    IF percentile <= 0.05 THEN
        SET percentile_potential = 800;
        SET points = 600;
        SET bracket_min = 0;
        SET bracket_max = GREATEST(2, total_completions * 0.05);
    ELSEIF percentile <= 0.10 THEN
        SET bracket_max = GREATEST(2, total_completions * 0.1);
        SET percentile_potential = 600;
        SET points = 450;
    ELSEIF percentile <= 0.15 THEN
        SET bracket_max = GREATEST(2, total_completions * 0.15);
        SET percentile_potential = 450;
        SET points = 200;
    ELSEIF percentile <= 0.25 THEN
        SET bracket_max = GREATEST(2, total_completions * 0.25);
        SET percentile_potential = 200;
        SET points = 10;
    ELSE
        -- 25%排名后的兜底逻辑，直接给基础分，不需要对数计算
        SET points = 10;
        SET bracket_max = total_completions;
    END IF;
    -- 仅当percentile_potential有值时才做对数加权计算，避免空值计算异常
    IF percentile_potential IS NOT NULL THEN
        SET points = points + GREATEST(0, ROUND(percentile_potential - percentile_potential * LOG(place, bracket_max))) + completionsbonus;
    ELSE
        SET points = points + completionsbonus;
    END IF;
END IF;

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

SQL存储函数getRunPointValue触发1365除零错误排查

问题描述 #

问题根因 #

现有完整代码 #

修复方法 #

问题描述

问题根因

现有完整代码

修复方法