mysql - 创建 MySQL 函数时出错 (1064)

Question

phpMyAdmin: 3.5.7
MySQL: 5.5.29

我希望用户能够根据数据库中的帖子编制多个不同的列表。以下函数应该用于将帖子添加到这样的列表中。它检查用户提供的列表 ID 是否与给定的用户 ID 匹配，然后检查列表是否未满，然后将帖子添加到列表。

DELIMITER //

CREATE FUNCTION addToFav(inputKid INT, inputUid INT, listID INT, listLimit INT)
RETURNS VARCHAR(300)

BEGIN
DECLARE resultMsg VARCHAR(300);
DECLARE listExists INT; SET listExists = (SELECT COUNT(*) FROM fav_lists WHERE fav_list_id=listID AND uid=inputUid AND active=1);

IF 1 > listExists THEN SET resultMsg = 'LIST NON-EXISTANT';
ELSE 
    DECLARE listSize INT; 
        SET listSize = (SELECT COUNT(*) FROM fav_links WHERE fav_list_id=listID AND active=1);
    IF listSize = listLimit THEN SET resultMsg = 'LIST FULL';
        ELSE IF listSize > listLimit THEN SET resultMsg ='dbErr: LIST OVER LIMIT';
        ELSE 
            REPLACE INTO fav_links (kid, fav_list_id, active) VALUES (inputKid, listID, 1);
            SET resultMsg ='SUCCESS';
        END IF;
END IF;


RETURN resultMsg;
END //

DELIMITER ;

我将此代码输入到 phpMyAdmin 的 SQL 控制台，它返回

#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 'DECLARE listSize INT; SET listSize = (SELECT COUNT(*) FROM fav_links W' at line 10

现在，我是否应该将“错误”行移上去，代码如下所示:

DELIMITER //

CREATE FUNCTION addToFav(inputKid INT, inputUid INT, listID INT, listLimit INT)
RETURNS VARCHAR(300)

BEGIN
DECLARE listSize INT; SET listSize = (SELECT COUNT(*) FROM fav_links WHERE fav_list_id=listID AND active=1);
DECLARE resultMsg VARCHAR(300);
DECLARE listExists INT; SET listExists = (SELECT COUNT(*) FROM fav_lists WHERE fav_list_id=listID AND uid=inputUid AND active=1);
[...]

此版本指向相同的错误，但在另一行:

#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 'DECLARE resultMsg VARCHAR(300); DECLARE listExists INT; SET listExists = (SELEC' at line 6

这让我相信问题可能是由于错误使用定界符引起的，但这就是我现在所知道的。 PhpMyAdmin 在 SQL 插入字段之后有一个定界符字段，我尝试将其留空，填充 ; 和 //，但没有任何区别。

什么可能导致此问题，我该如何解决？

Answer 1

您应该将变量声明放在顶部。我还自由地重写了一些代码:

DELIMITER //

CREATE FUNCTION addToFav(inputKid INT, inputUid INT, listID INT, listLimit INT)
RETURNS VARCHAR(300)

BEGIN
DECLARE resultMsg VARCHAR(300);
DECLARE listSize INT; 

IF (NOT EXISTS (SELECT 1 FROM fav_lists WHERE fav_list_id=listID AND uid=inputUid AND active=1)) THEN 
    SET resultMsg = 'LIST NON-EXISTANT';
ELSE 
    SELECT COUNT(*) INTO listSize FROM fav_links WHERE fav_list_id=listID AND active=1;
    IF (listSize = listLimit) THEN 
        SET resultMsg = 'LIST FULL';
    ELSEIF (listSize > listLimit) THEN 
        SET resultMsg ='dbErr: LIST OVER LIMIT';
    ELSE 
        BEGIN
            REPLACE INTO fav_links (kid, fav_list_id, active) VALUES (inputKid, listID, 1);
            SET resultMsg ='SUCCESS';
        END;
    END IF;
END IF;

RETURN resultMsg;
END //

DELIMITER ;

如果您只想知道某物是否存在，请不要使用 COUNT(*)。