MYSQL存储过程：变量声明和条件语句

Question

我查看了许多教程、手册和文档，但仍然无法使其工作。

我正在尝试使用phpMyAdmin创建存储过程。

我似乎找不到错误，SQL错误信息太模糊了...

CREATE PROCEDURE insertToonOneShot(IN locale CHAR(2), IN name VARCHAR(16), IN realm VARCHAR(24), IN faction CHAR(1), IN toon_level INT, IN class_name INT)
BEGIN
DECLARE @realmID INT;
DECLARE @classID INT;
DECLARE @toonID INT;
SET @realmID = SELECT id FROM realms WHERE realms.name = realm;
SET @classID = SELECT id FROM classes WHERE classes.name = class_name;
IF NOT @realmID IS NULL AND NOT @classID IS NULL AND @toonID IS NULL THEN
INSERT INTO 
toon (`locale`, `name`, `realm_id`, `faction`, `level`, `class_id`)
VALUES
(locale, name, @realmID, faction, toon_level, @classID);
END IF;
END;

我现在遇到的错误是：

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near @realmID INT; DECLARE @classID INT; DECLARE @toonID INT; SET @rea at line 3

这可能是我所做过的最令人沮丧的事情之一...

我看过许多在线教程，其中一些显示在变量声明中使用@符号，其他人则不使用，甚至有些使用VAR而不是DECLARE。什么是正确的语法？...

- Vigs

Answer 1

这个问题及其回答似乎混淆了会话变量（以@为前缀）和过程变量（没有前缀）。有关更多信息，请参见此问题的答案。

被接受的解决方案通过使用会话变量来解决错误，但它可能会引入与变量作用域相关的问题。例如，如果在过程之外定义了一个名为realmID的变量，则该变量的值将在运行过程时被覆盖。

解决这个问题的正确方法是只使用过程变量。这些变量在过程开始时不带@前缀进行声明(DECLARE)。

DELIMITER $$

CREATE PROCEDURE insertToonOneShot(
    IN locale CHAR(2),
    IN name VARCHAR(16),
    IN realm VARCHAR(24),
    IN faction CHAR(1),
    IN toon_level INT,
    IN class_name INT
)
BEGIN
    DECLARE realmID INT;
    DECLARE classID INT;

    SELECT id INTO realmID FROM realms WHERE realms.name = realm LIMIT 1;
    SELECT id INTO classID FROM classes WHERE classes.name = class_name LIMIT 1;
    IF realmID IS NOT NULL AND classID IS NOT NULL THEN
        INSERT INTO toon (`locale`, `name`, `realm_id`, `faction`, `level`, `class_id`)
        VALUES (locale, name, realmID, faction, toon_level, classID);
    END IF;
END$$

DELIMITER ;

- miken32

Answer 2

当你有一个子查询时，它需要括号。这些代码行：

SET @realmID = SELECT id FROM realms WHERE realms.name = realm;
SET @classID = SELECT id FROM classes WHERE classes.name = class_name;

应该是：

SET @realmID = (SELECT id FROM realms WHERE realms.name = realm);
SET @classID = (SELECT id FROM classes WHERE classes.name = class_name);

或者说，更好的是，你不需要 set ：

SELECT @realmID := id FROM realms WHERE realms.name = realm;
SELECT @classID := id FROM classes WHERE classes.name = class_name;

- Gordon Linoff

---

我实现了 @realmID := id... 方法。但是在第三行仍然收到相同的错误。它似乎不喜欢 DECLARE 语句。 - Vigs

@Gordon是说我们不需要声明数据类型吗？ - Avinash Raj

我们需要使用@符号吗？在我的存储过程中，声明时没有@。 - Avinash Raj

@AvinashRaj . . . OP在问题中使用了会话变量，因此此答案也使用它们。您可以在此处阅读有关差异的更多信息：https://dev.mysql.com/doc/refman/5.6/en/user-variables.html。此代码应适用于本地和会话变量。 - Gordon Linoff

Answer 3

这就是解决方法：

CREATE PROCEDURE insertToonOneShot(IN locale CHAR(2), IN name VARCHAR(16), IN realm VARCHAR(24), IN faction CHAR(1), IN toon_level INT, IN class_name VARCHAR(12))
BEGIN
SELECT @realmID := id FROM realms WHERE realms.name = realm;
SELECT @classID := id FROM classes WHERE classes.name = class_name;
SELECT @toonID := id FROM toon WHERE toon.name = name AND toon.realm_id = @realmID;
IF NOT @realmID IS NULL AND NOT @classID IS NULL AND @toonID IS NULL
THEN 
INSERT INTO toon (`locale`, `name`, `class_id`, `realm_id`, `faction`, `level`)
VALUES (locale, name, @classID, @realmID, faction, toon_level);
END IF;
END;
//

显然，声明语句是不必要的...谁会知道呢？

感谢Gordon Linoff为我指明方向。

- Vigs

---

在使用以@为前缀的会话变量和不带前缀的过程变量之前，请注意区别。这两者之间的区别在于，每次调用过程时，过程变量都将被重新初始化为NULL，而特定于会话的变量则不会。想象一下在父过程中使用相同的会话变量的情况。 - Himalaya Garg