在 MySQL 中创建存储过程时出现 SQL 语法错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/639089/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL syntax error when creating a stored procedure in MySQL
提问by Pierre Spring
I have a hard time locating an error when trying to create a stored procedure in mysql.
尝试在 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:
我收到以下（有用的）错误：
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
ERROR 1064 (42000)：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在第 3 行的 '' 附近使用的正确语法
I just don't know where to start debugging, as every single one of these lines is correct.
我只是不知道从哪里开始调试，因为这些行中的每一行都是正确的。
Do you have any tips?
你有什么建议吗？
回答by Uwe Mesecke
As line 3 contains the first ;perhaps you have a problem with your delimiters.
由于第 3 行包含第;一个，因此您的分隔符可能有问题。
See http://dev.mysql.com/doc/refman/5.0/en/stored-programs-defining.html
见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 ;
回答by Joel Coehoorn
You never declare your @child_left variable.
你永远不会声明你的 @child_left 变量。
回答by Joel Coehoorn
Thanks, near '' at line 3was my problem and the delimiter statement fixed it! I always want things to make sense and this does. As the '' indicates it's at the end of the procedure, but no END statement was found thus the syntax error. And I wondered why I kept seeing a lot of people using the delimiter statement. I see the light!
谢谢，第 3 行的 '' 附近是我的问题，分隔符语句修复了它！我总是想让事情变得有意义，而这确实如此。由于 '' 表示它在过程的末尾，但没有找到 END 语句，因此语法错误。我想知道为什么我一直看到很多人使用分隔符语句。我看见了光！
