首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >创建MySQL函数时出错(1064)

问创建MySQL函数时出错(1064)
EN

Stack Overflow用户
提问于 2014-03-06 09:46:11
回答 2查看 981关注 0票数 2
代码语言:javascript
复制
phpMyAdmin: 3.5.7
MySQL: 5.5.29

我希望用户能够从数据库中的帖子中编译几个不同的列表。下面的函数应该用于将post添加到这样的列表中。它检查用户提供的列表id是否与给定的用户ID匹配,然后检查列表是否已满,然后将post添加到list。

代码语言:javascript
复制
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控制台,它将返回

代码语言:javascript
复制
#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

现在,如果我将“错误”一行移动起来,代码如下所示:

代码语言:javascript
复制
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);
[...]

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

代码语言:javascript
复制
#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在insertion字段之后有一个分隔符字段,我尝试将该字段保留为空,使用;和//填充,所有这些都不起作用。

是什么引起了这个问题,我该如何解决呢?

EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2014-03-06 09:55:10

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

代码语言:javascript
复制
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(*)。

票数 2
EN

Stack Overflow用户

发布于 2014-03-06 09:54:22

在MySQL中尝试下面的代码,同时使用ELSE Code there doesn't given space betweenandIF`。同时在顶部声明变量

代码语言:javascript
复制
DELIMITER //

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

BEGIN

    DECLARE resultMsg VARCHAR(300);
    DECLARE listExists INT; 
    DECLARE listSize 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 
        SET listSize = (SELECT COUNT(*) 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 
                REPLACE INTO fav_links (kid, fav_list_id, active) VALUES (inputKid, listID, 1);
                SET resultMsg ='SUCCESS';
            END IF;
    END IF;

RETURN resultMsg;
END //

DELIMITER ;
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/22220601

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档