mysql - 一些变量保持为空 - 存储过程MySQL

Question

我正在使用 InnoDB 数据库编写一个小型浏览器游戏。为了自动化发生的事情，当你 build 一座新房子时，我编写了以下过程。它的问题是，除了“cycle_offset”之外的每个声明的变量在第一个 SELECT 之后仍然为 NULL，因此接下来的三个选择在不传递 NULL 的情况下无法执行。

DELIMITER //
DROP PROCEDURE IF EXISTS create_building //
CREATE PROCEDURE create_building(IN userID INT, IN buildingID INT)
BEGIN
--declare variables
DECLARE cycle_offset INT;
DECLARE res_1 INT;
DECLARE am_1 INT;
DECLARE res_2 INT;
DECLARE am_2 INT;
DECLARE res_3 INT;
DECLARE am_3 INT;
DECLARE u_am_1 INT;
DECLARE u_am_2 INT;
DECLARE u_am_3 INT;

--fill the values that belong to the certain type of building
SELECT  cycles * 10,
        res_1, 
        am_1, 
        res_2, 
        am_2, 
        res_3, 
        am_3 
INTO    cycle_offset,
        res_1,
        am_1, 
        res_2,
        am_2, 
        res_3, 
        am_3
FROM    build_cost 
WHERE   building = buildingID;

--select resources from user-fortune that were given in the build-cost
SELECT amount INTO u_am_1 FROM fortune WHERE resource = res_1;
SELECT amount INTO u_am_2 FROM fortune WHERE resource = res_2;
SELECT amount INTO u_am_3 FROM fortune WHERE resource = res_3;
--for each resource: if the resource is not null and the user has more resources left than needed 
--remove the "price" from the user's fortune
IF 
    ((res_1 IS NOT NULL) AND 
    (u_am_1 >= am_1))
THEN
    UPDATE fortune
    SET amount = (u_am_1 - am_1)
    WHERE resource = res_1 AND
          user = userID;
END IF;

IF 
    ((res_1 IS NOT NULL) AND 
    (u_am_2 >= am_2))
THEN
    UPDATE fortune
    SET amount = (u_am_2 - am_2)
    WHERE resource = res_2 AND
          user = userID;
END IF;

IF 
    ((res_1 IS NOT NULL) AND 
    (u_am_3 >= am_3))
THEN
    UPDATE fortune
    SET amount = (u_am_3 - am_3)
    WHERE resource = res_3 AND
          user = userID;
END IF;

--add the building into the users town

INSERT INTO town (user, building, cycle_start, level, in_construction)
VALUES (userID, 
        buildingID, 
        SEC_TO_TIME(FLOOR((TIME_TO_SEC(CURRENT_TIME)+5)/10) * 10 + cycle_offset), 
        1,
        1);

--debugging purposes:
INSERT INTO debug (a,b,c,d,e,f,g,h,i,j,k,l) VALUES (userID, buildingID, cycle_offset, res_1, am_1, res_2, am_2, res_3, am_3, u_am_1, u_am_2, u_am_3);
END //

调试表总是显示 userID、buildingID 和 Cycle_offset 不为空，其余为空。您可能认为“build_cost”表是空的，但单独运行查询会给出以下结果(如预期):

SELECT  cycles * 10, res_1, am_1, res_2, am_2, res_3, am_3 
FROM    build_cost 
WHERE   building = 1;

+-------------+-------+------+-------+------+-------+------+
| cycles * 10 | res_1 | am_1 | res_2 | am_2 | res_3 | am_3 |
+-------------+-------+------+-------+------+-------+------+
|           0 |     4 |   50 |     5 |   50 |  NULL | NULL |
+-------------+-------+------+-------+------+-------+------+

如果我运行包含此查询的过程，为什么循环、res_1、am_1、res_2 和 am_2 的值没有存储在我声明的变量中？

Answer 1

我在 MySQL 解释的调试器中看到

DECLARE cycle_offset INT;
DECLARE res_1 INT;
DECLARE am_1 INT;
DECLARE res_2 INT;
DECLARE am_2 INT;
DECLARE res_3 INT;
DECLARE am_3 INT;

SELECT  cycles * 10,
        res_1, 
        am_1, 
        res_2, 
        am_2, 
        res_3, 
        am_3 
INTO    cycle_offset,
        res_1,
        am_1, 
        res_2,
        am_2, 
        res_3, 
        am_3
FROM    build_cost 
WHERE   building = buildingID;

作为

SELECT  cycles * 10,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL
INTO    cycle_offset,
        res_1,
        am_1, 
        res_2,
        am_2, 
        res_3, 
        am_3
FROM    build_cost 
WHERE   building = buildingID;

因为它，至少看起来像这样，无法区分变量和表列(如果它们的名称相等)。

不过还是谢谢你的努力