You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

MariaDB 10.3存储过程WHILE循环遍历多元素时崩溃问题

avatar
云上老周
2026-7-23
MariaDB 10.3存储过程循环超时问题

需求背景

开发游戏随机奖励生成的存储过程，核心逻辑如下：

  • 从options表获取1-5的随机循环次数
  • 循环对应次数，每次生成一个随机物品
  • 将结果存入临时表后返回，供PHP调用

核心逻辑说明

  1. 生成循环数量：通过RAND()生成0-1随机数，匹配options表中reward count类别的概率范围，得到1-5的整数@loopquantity，为空时默认设为1
  2. 循环生成物品：使用WHILE循环，初始@loopstart=1，循环条件@loopstart <= @loopquantity，每次循环后自增@loopstart
  3. 返回结果：创建临时表results，循环插入生成的物品，最后查询返回

完整存储过程代码 

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `mission_get_unidentified_rewards`()
BEGIN 
        SET @loopstart = 1;
        
        select loopquantity into @loopquantity
        from (select cast(option_name as int) loopquantity, 
                     CAST(SUBSTRING_INDEX(option_value,'|',1) as decimal(18,4)) AS random_number_start,
                     CAST(SUBSTRING_INDEX(option_value,'|',-1) as decimal(18,4)) AS random_number_end
              from options
              where option_class = 'reward count') rr
        inner join (select cast(rand() as decimal(18,4)) luckynumber) rl on rl.luckynumber > rr.random_number_start and rl.luckynumber <= rr.random_number_end;
        
        if @loopquantity is null then set @loopquantity = 1; end if;
        
        DROP TEMPORARY TABLE IF EXISTS results;
        CREATE TEMPORARY TABLE results (
            item_id INT default null,
            item_name VARCHAR(50) default null,
            item_description VARCHAR(500) default null,
            rarity VARCHAR(10) default null,
            type VARCHAR(10) default null,
            icon VARCHAR(100) default null,
            item_level int default null,
            quantity int default null,
            loopquantity int default null
        );

        WHILE @loopstart <= @loopquantity DO
            insert into results
            with rarity_rates as (
                select option_name rarity, 
                CAST(SUBSTRING_INDEX(option_value,'|',1) as decimal(18,4)) AS random_number_start,
                CAST(SUBSTRING_INDEX(option_value,'|',-1) as decimal(18,4)) AS random_number_end
                from options
                where option_class = 'reward rarity'
            ),
            rarity_luckynum as (
                select cast(rand() as decimal(18,4)) luckynumber
            ),
            rarity as (
                select rarity
                from rarity_rates rr
                inner join rarity_luckynum rl on rl.luckynumber > rr.random_number_start and rl.luckynumber <= rr.random_number_end
            ),
            type_rates as (
                select option_name `type`, 
                CAST(SUBSTRING_INDEX(option_value,'|',1) as decimal(18,4)) AS random_number_start,
                CAST(SUBSTRING_INDEX(option_value,'|',-1) as decimal(18,4)) AS random_number_end
                from options
                where option_class = 'reward type'
            ),
            type_luckynum as (
                select cast(rand() as decimal(18,4)) luckynumber
            ),
            item_type as (
                select `type`
                from type_rates rr
                inner join type_luckynum rl on rl.luckynumber > rr.random_number_start and rl.luckynumber <= rr.random_number_end
            ),
            item_level as (
                select item_level
                from (
                    select option_name item_level, 
                           CAST(SUBSTRING_INDEX(option_value,'|',1) as decimal(18,4)) AS random_number_start,
                           CAST(SUBSTRING_INDEX(option_value,'|',-1) as decimal(18,4)) AS random_number_end
                    from options
                    where option_class = 'reward count'
                ) rr
                inner join (select cast(rand() as decimal(18,4)) luckynumber) rl on rl.luckynumber > rr.random_number_start and rl.luckynumber <= rr.random_number_end

            )
            select gi.item_id, gi.item_name, gi.item_description, gi.rarity, gi.type, gi.icon, item_level, 1 quantity, @loopquantity
            from rarity ra
            cross join item_type it
            cross join item_level il
            inner join game_items gi on ra.rarity = gi.rarity and it.type = gi.type and item_name like 'unidentified%';

            SET @loopstart = @loopstart + 1;
        END WHILE;
        select * from results;
END$$
DELIMITER ;

问题现象

  • 当@loopquantity=1时，存储过程正常运行
  • 当@loopquantity>1时，执行超时并报错：

分析时发现2个错误。
无法识别的语句类型（位置0附近的"WHILE"）
无法识别的语句类型（位置2584附近的"END"）
MySQL提示：#2006 - MySQL server has gone away

推测WHILE循环陷入无限循环导致超时，单次迭代耗时极短，无法定位问题；尝试改用FOR循环也无法创建存储过程。

补充信息

game_items表结构及测试数据 

CREATE TABLE IF NOT EXISTS `game_items` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  `created_by` int(11) NOT NULL DEFAULT 0,
  `item_name` varchar(50) DEFAULT NULL,
  `item_description` varchar(250) DEFAULT NULL,
  `faction` int(11) DEFAULT NULL ,
  `icon` varchar(150) DEFAULT NULL,
  `flags` text DEFAULT NULL ,
  `type` varchar(50) DEFAULT NULL ,
  `subtype` varchar(50) DEFAULT NULL ,
  `rarity` varchar(50) DEFAULT NULL ,
  `level_requirement` int(11) DEFAULT NULL,
  `vendor_value` int(11) DEFAULT NULL,
  PRIMARY KEY (`item_id`)
)

INSERT INTO game_items (created_by, item_name, item_description, faction, icon, flags, type, subtype, rarity, level_requirement, vendor_value) VALUES
(0, 'M3 trench knife', 'M3 trench knife: U.S. military combat knife used in World War II, with a 6.75-inch blade and a brass knuckle handle.', 1, NULL, NULL, 'Weapon', 'Knife', 'basic', 1, 10);
问题排查与修复方案

核心问题定位

  1. 会话变量残留干扰：使用的@loopstart、@loopquantity是会话级变量，会话结束前不会销毁。若之前执行残留了变量值，会导致循环条件异常，比如@loopstart未正确初始化，或@loopquantity被意外修改。
  2. CTE查询逻辑错误：item_level的CTE错误使用了option_class='reward count'（应为等级配置分类，如reward level），导致无法获取有效等级值，可能引发后续逻辑异常。
  3. CTE无结果导致空插入：若rarity、item_type等CTE未匹配到数据，INSERT语句无数据插入，但循环仍继续；若CTE返回多行结果，CROSS JOIN会生成笛卡尔积，单次插入大量数据拖慢执行。

修复步骤

1. 改用局部变量替代会话变量

局部变量仅在存储过程执行期间有效，避免会话残留影响：

-- 替换原有变量声明
DECLARE loopstart INT DEFAULT 1;
DECLARE loopquantity INT DEFAULT 1;

-- 修改变量赋值语句
SELECT loopquantity INTO loopquantity
FROM (...) rr
INNER JOIN (...) rl ON ...;

IF loopquantity IS NULL OR loopquantity <1 OR loopquantity>5 THEN 
    SET loopquantity =1; 
END IF;

-- 循环条件和自增语句修改为局部变量
WHILE loopstart <= loopquantity DO
    -- ... 内部逻辑 ...
    SET loopstart = loopstart +1;
END WHILE;

2. 修正item_level的CTE分类错误

将item_level的CTE中option_class改为正确的等级配置分类：

item_level as (
    select item_level
    from (
        select option_name item_level, 
               CAST(SUBSTRING_INDEX(option_value,'|',1) as decimal(18,4)) AS random_number_start,
               CAST(SUBSTRING_INDEX(option_value,'|',-1) as decimal(18,4)) AS random_number_end
        from options
        where option_class = 'reward level' -- 修正为等级分类
    ) rr
    inner join (select cast(rand() as decimal(18,4)) luckynumber) rl on rl.luckynumber > rr.random_number_start and rl.luckynumber <= rr.random_number_end
)

3. 为CTE添加默认值，避免空结果

用COALESCE为CTE查询添加默认值，确保不会返回空结果：

rarity as (
    SELECT COALESCE((SELECT rarity FROM rarity_rates rr INNER JOIN rarity_luckynum rl ON rl.luckynumber > rr.random_number_start AND rl.luckynumber <= rr.random_number_end), 'basic') AS rarity
)

同理处理item_type和item_level的CTE。

4. 限制单次插入仅一行数据

在SELECT语句末尾添加LIMIT 1，确保每次循环只插入一个物品：

select gi.item_id, gi.item_name, gi.item_description, gi.rarity, gi.type, gi.icon, item_level, 1 quantity, loopquantity
from rarity ra
cross join item_type it
cross join item_level il
inner join game_items gi on ra.rarity = gi.rarity and it.type = gi.type and item_name like 'unidentified%'
LIMIT 1; -- 添加此行

修复后的完整存储过程示例 

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `mission_get_unidentified_rewards`()
BEGIN 
        DECLARE loopstart INT DEFAULT 1;
        DECLARE loopquantity INT DEFAULT 1;
        
        SELECT loopquantity INTO loopquantity
        FROM (SELECT CAST(option_name AS INT) loopquantity, 
                     CAST(SUBSTRING_INDEX(option_value,'|',1) AS DECIMAL(18,4)) AS random_number_start,
                     CAST(SUBSTRING_INDEX(option_value,'|',-1) AS DECIMAL(18,4)) AS random_number_end
              FROM options
              WHERE option_class = 'reward count') rr
        INNER JOIN (SELECT CAST(RAND() AS DECIMAL(18,4)) luckynumber) rl ON rl.luckynumber > rr.random_number_start AND rl.luckynumber <= rr.random_number_end;
        
        IF loopquantity IS NULL OR loopquantity < 1 OR loopquantity >5 THEN 
            SET loopquantity = 1; 
        END IF;
        
        DROP TEMPORARY TABLE IF EXISTS results;
        CREATE TEMPORARY TABLE results (
            item_id INT DEFAULT NULL,
            item_name VARCHAR(50) DEFAULT NULL,
            item_description VARCHAR(500) DEFAULT NULL,
            rarity VARCHAR(10) DEFAULT NULL,
            type VARCHAR(10) DEFAULT NULL,
            icon VARCHAR(100) DEFAULT NULL,
            item_level INT DEFAULT NULL,
            quantity INT DEFAULT NULL,
            loopquantity INT DEFAULT NULL
        );

        WHILE loopstart <= loopquantity DO
            INSERT INTO results
            WITH rarity_rates AS (
                SELECT option_name rarity, 
                CAST(SUBSTRING_INDEX(option_value,'|',1) AS DECIMAL(18,4)) AS random_number_start,
                CAST(SUBSTRING_INDEX(option_value,'|',-1) AS DECIMAL(18,4)) AS random_number_end
                FROM options
                WHERE option_class = 'reward rarity'
            ),
            rarity_luckynum AS (
                SELECT CAST(RAND() AS DECIMAL(18,4)) luckynumber
            ),
            rarity AS (
                SELECT COALESCE((SELECT rarity FROM rarity_rates rr INNER JOIN rarity_luckynum rl ON rl.luckynumber > rr.random_number_start AND rl.luckynumber <= rr.random_number_end), 'basic') AS rarity
            ),
            type_rates AS (
                SELECT option_name `type`, 
                CAST(SUBSTRING_INDEX(option_value,'|',1) AS DECIMAL(18,4)) AS random_number_start,
                CAST(SUBSTRING_INDEX(option_value,'|',-1) AS DECIMAL(18,4)) AS random_number_end
                FROM options
                WHERE option_class = 'reward type'
            ),
            type_luckynum AS (
                SELECT CAST(RAND() AS DECIMAL(18,4)) luckynumber
            ),
            item_type AS (
                SELECT COALESCE((SELECT `type` FROM type_rates rr INNER JOIN type_luckynum rl ON rl.luckynumber > rr.random_number_start AND rl.luckynumber <= rr.random_number_end), 'Weapon') AS `type`
            ),
            item_level AS (
                SELECT COALESCE((SELECT item_level FROM (
                    SELECT option_name item_level, 
                           CAST(SUBSTRING_INDEX(option_value,'|',1) AS DECIMAL(18,4)) AS random_number_start,
                           CAST(SUBSTRING_INDEX(option_value,'|',-1) AS DECIMAL(18,4)) AS random_number_end
                    FROM options
                    WHERE option_class = 'reward level'
                ) rr INNER JOIN (SELECT CAST(RAND() AS DECIMAL(18,4)) luckynumber) rl ON rl.luckynumber > rr.random_number_start AND rl.luckynumber <= rr.random_number_end), 1) AS item_level
            )
            SELECT gi.item_id, gi.item_name, gi.item_description, gi.rarity, gi.type, gi.icon, il.item_level, 1 quantity, loopquantity
            FROM rarity ra
            CROSS JOIN item_type it
            CROSS JOIN item_level il
            INNER JOIN game_items gi ON ra.rarity = gi.rarity AND it.type = gi.type AND gi.item_name LIKE 'unidentified%'
            LIMIT 1;

            SET loopstart = loopstart + 1;
        END WHILE;
        SELECT * FROM results;
END$$
DELIMITER ;

验证建议

  1. 执行存储过程前，先执行SET @loopstart = NULL; SET @loopquantity = NULL;清理会话变量
  2. 检查options表中reward rarity、reward type、reward level的配置，确保概率范围覆盖0-1区间
  3. 测试loopquantity为2-5的情况，观察执行时间和返回结果是否符合预期

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.07.23 04:12:03