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

MYSQL Stored Procedures: Variable Declaration and Conditional Statements

提问by Vigs

I have looked over numerous tutorials, manuals and documentations, but I still can not get this to work.

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

I am trying to create a stored procedure using phpMyAdmin.

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

I cant seem to find the errors here, the sql errors are so vague...

我似乎无法在这里找到错误，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;

The error I am getting right now is:

我现在得到的错误是：

#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

#1064 - 你的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在 @realmID INT 附近使用的正确语法；声明@classID INT; 声明@toonID INT; 在第 3 行设置 @rea

Probably one of the more frustrating things I have ever had to do...

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

I have seen many tutorials online that show using the @ symbol in variable declaration, and others not using it, I have even seen some that use VAR instead of DECLARE. What is the right syntax?...

我在网上看到很多教程显示在变量声明中使用 @ 符号，而其他教程没有使用它，我什至看到一些使用 VAR 而不是 DECLARE。什么是正确的语法？...

采纳答案by Vigs

This does the trick:

这是诀窍：

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

Apparently the declare statements were not required... Who would have known?

显然不需要声明语句......谁会知道？

Thanks to Gordon Linoff for pointing me in the right direction.

感谢 Gordon Linoff 为我指明了正确的方向。

回答by Gordon Linoff

When you have a subquery, it needs to have parentheses. These lines:

当你有一个子查询时，它需要有括号。这些线路：

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

Should be:

应该：

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

Or, better yet, you don't need the set:

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

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

回答by miken32

Old question, but I think it's worth mentioning that it seems to be confusing session variables, which are prefixed with @, with procedural variables, which are not.

老问题，但我认为值得一提的是，它似乎混淆了以 为前缀的会话变量，而@程序变量则不是。

The accepted solution resolves the error, but could introduce problems related to variable scope, if a variable is defined outside the procedure and then used inside. The correct way to resolve this is to use only procedural variables:

接受的解决方案解决了错误，但可能会引入与变量作用域相关的问题，如果在过程外部定义变量然后在内部使用。解决此问题的正确方法是仅使用过程变量：

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 ;