在MySQL中创建存储过程时出现SQL语法错误 [英] SQL syntax error when creating a stored procedure in MySQL

问题描述

尝试在mysql中创建存储过程时，我很难定位错误.

I have a hard time locating an error when trying to create a stored procedure in mysql.

如果我独立运行过程的每一行，那么一切都很好.

If I run every single line of the procedure independently, everything works just fine.

CREATE PROCEDURE cms_proc_add_child 
(
    param_parent_id INT, param_name CHAR(255),
    param_content_type CHAR(255)
)
BEGIN
    SELECT @child_left := rgt FROM cms_tree WHERE id = param_parent_id;
    UPDATE cms_tree SET rgt = rgt+2 WHERE rgt >= @child_left;
    UPDATE cms_tree SET lft = lft+2 WHERE lft >= @child_left;
    INSERT INTO cms_tree (name, lft, rgt, content_type) VALUES 
    (
        param_name,
        @child_left,
        @child_left+1,
        param_content_type
    );
END

我收到以下(有用的)错误:

I get the following (helpful) error:

错误1064(42000):您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册以获取正确的语法，以在第3行的

ERROR 1064 (42000): 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 '' at line 3

我只是不知道从哪里开始调试，因为这些行中的每一行都是正确的.

I just don't know where to start debugging, as every single one of these lines is correct.

您有什么建议吗?

推荐答案

由于第3行包含第一个;，也许您的定界符有问题.

As line 3 contains the first ; perhaps you have a problem with your delimiters.

请参见 http://dev.mysql.com/doc/refman/5.0/en/stored-programs-defining.html

DELIMITER //
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
    SET @x = 0;
    REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END//
DELIMITER ;

这篇关于在MySQL中创建存储过程时出现SQL语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！