早上好，
关于MariaDB 10.3存储过程我正在尝试生成一个过程，它可以根据过程中的逻辑输出一个项目表。
对于我正在设计的一个游戏，该过程应该向玩家输出随机奖励。为了生成随机项目，我有一个选项表，其中包含在创建随机列表期间使用的参数。
该程序应做到：
1.从数据库中获取从1到5的随机数。
1.循环遍历生成的总数量，每次生成一个随机项
1.返回所有项目的列表（返回到php，我将从那里调用proc）

1：生成数量

称为“@loopquantity”的变量描述了应该创建一个项目作为玩家奖励的次数。这些计数被存储在这样的表中：
| 项目计数|随机数范围|
| --------------|--------------|
| 1| 0| 0.59|
| 二|0.59| 0.84|
| 三|0.84| 0.965|
| 四个|0.965| 0.99|
| 五|0.99| 1.0|
(if你想测试，这里创建+插入）：

CREATE TABLE IF NOT EXISTS `options` (
  `option_id` int(11) NOT NULL AUTO_INCREMENT,
  `option_class` varchar(100) NOT NULL,
  `option_name` varchar(500) NOT NULL,
  `option_value` varchar(999) NOT NULL,
  `sorter` int(11) DEFAULT NULL,
  PRIMARY KEY (`option_id`)
) ENGINE=InnoDB AUTO_INCREMENT=180 DEFAULT CHARSET=latin1;

/*!40000 ALTER TABLE `options` DISABLE KEYS */;
INSERT INTO `options` (`option_id`, `option_class`, `option_name`, `option_value`, `sorter`) VALUES
    (169, 'reward rarity', 'basic', '0|.8', 0),
    (170, 'reward rarity', 'rare', '.8|.95', 1),
    (171, 'reward rarity', 'unique', '.95|1', 2),
    (172, 'reward type', 'weapon', '0|.33', 1),
    (173, 'reward type', 'armor', '0.33|.66', 2),
    (174, 'reward type', 'gadget', '.66|1', 3),
    (175, 'reward count', '1', '0|0.29', 1),
    (176, 'reward count', '2', '0.29|0.84', 2),
    (177, 'reward count', '3', '0.84|0.965', 3),
    (178, 'reward count', '4', '0.965|.99', 4),
    (179, 'reward count', '5', '.99|1', 5);
/*!40000 ALTER TABLE `options` ENABLE KEYS */;

使用MariaDB的兰德（）函数，我可以生成一个0到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' and option_name != '1' 
) 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 @loopquantity时，它将始终返回1到5之间的整数。到目前为止一切顺利。（之所以从选项表中获取维度，而不是用代码来做，是因为我可以在游戏运行时轻松更改奖励计数机会，而不需要更改代码）。

2：循环遍历项目

现在我将使用while循环来迭代实际的项创建。首先，我通过声明SET @loopstart = 1;创建一个循环int变量。然后，我添加一个while循环，如下所示：

WHILE @loopstart <= @loopquantity DO
    [create the item]
    SET @loopstart = @loopstart + 1;
 END WHILE;

3：返回数据

为了返回数据，我创建了一个名为results的临时表，while循环的每次迭代都可以在其中插入。然后过了一会儿，我就做select * from results。
create看起来像这样：

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
);

完整的proc创建代码（从phpmyadmin导出）

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时，proc按预期工作。当@loopquantity大于1时，将花费更长的时间并产生错误：

分析过程中发现2个错误。

无法识别的语句类型。（位置0处的“WHILE”附近）
无法识别的语句类型。（靠近位置2584处的“END”）

MySQL说：文件

2006 - MySQL服务器已经消失

所以while循环很大，只要数量是1。当它需要循环超过1时，它就会中断。
当@loopquantity为> 1时，proc /代码中断。我能说的最好的是，while循环不确定地进行，因此MariaDB超时。1次迭代的代码运行以毫秒为单位，因此每次迭代不会花费多秒。我也看不出程序有什么问题;单次运行完美运行的事实向我表明，一切都在运行。
有没有人知道为什么当需要进行多次迭代时while会中断？

编辑：

为了完整起见，下面是game_items表的create和值：

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：第二次世界大战中使用的美国军用战斗刀，刀刃6.75英寸，黄铜指节手柄。'，1，NULL，NULL，' Weapon '，' Knife '，' basic '，1，10）;

Also, I have tried to switch the WHILE to a FOR as its numerical between 1 and 5, but i cant get that to work at all. Proc wont save with that.