开始后MySQL存储过程语法错误 [英] MySQL stored procedure syntax error after BEGIN

问题描述

我正在尝试重新创建存储过程(因为我无法编辑主体).我调用了SHOW CREATE PROCEDURE，以使用与原始存储过程相同的格式，但是当我尝试重新创建它时，出现以下错误:

I am attempting to recreate a stored procedure (since I can't edit the body). I called SHOW CREATE PROCEDURE to use the same format as the original stored procedure but when I attempt to recreate it I get the following errors:

错误1064(42000):您的SQL语法有错误；请查看与您的MySQL服务器版本相对应的手册，以获取在第11行的''附近使用的正确语法

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 11

错误1064(42000):您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册以获取正确的语法，以在第1行的'DECLARE organization_id BIGINT(20)UNSIGNED'附近使用

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 'DECLARE organization_id BIGINT(20) UNSIGNED' at line 1

错误1064(42000):您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册以获取正确的语法，以在第1行的"DECLARE lobby_pod_id BIGINT(20)UNSIGNED"附近使用

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 'DECLARE lobby_pod_id BIGINT(20) UNSIGNED' at line 1

代码如下:

CREATE DEFINER=`lms`@`10.0.0.%` PROCEDURE `create_organization`(
    IN admin_username VARCHAR(255),
    IN organization_name VARCHAR(100)
)
BEGIN
    DECLARE admin_user_id BIGINT(20) UNSIGNED;
    DECLARE organization_id BIGINT(20) UNSIGNED;
    DECLARE lobby_pod_id BIGINT(20) UNSIGNED;

    SELECT ID, account INTO admin_user_id, organization_id 
    FROM users 
    WHERE username = admin_username;

    INSERT INTO pods (`title`, `description`, `owner`, `scene`)
    VALUES (CONCAT(organization_name, " Village"),
            CONCAT("General meeting space and hub for ", organization_name, " students and teachers."),
            admin_user_id,
            " Village"
    );
END

我粘贴到 SQL Fiddle 并得到了相同的结果，尽管粘贴到

I pasted into SQL Fiddle and got the same result, although pasting into MySQL Syntax Check gave me the thumbs-up. I'm sure it's a simple miss but it isn't that obvious to me.

推荐答案

您缺少

如果使用mysql客户端程序定义包含分号字符的存储程序，则会出现问题.默认情况下，mysql本身会将分号识别为语句定界符，因此您必须临时重新定义定界符，以使mysql将整个存储的程序定义传递给服务器.

If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.

要重新定义mysql分隔符，请使用delimiter命令. [...]分隔符更改为//以启用整个 定义作为单个语句传递给服务器，然后 恢复到;在调用该程序之前.这将启用; 过程主体中使用的定界符，该定界符将传递给 服务器，而不是由mysql本身解释.

To redefine the mysql delimiter, use the delimiter command. [...] The delimiter is changed to // to enable the entire definition to be passed to the server as a single statement, and then restored to ; before invoking the procedure. This enables the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself.

由于可以存储proc定义和主体，语法chack可以帮助您，但是代码无法在客户端中正常运行.

Since the stored proc definition and body was ok, syntax chack gave you the thumbs up, but the code would not run properly in your client.

使用以下框架定义存储过程:

Use the following skeleton for defining a stored procedure:

delimiter //
create procedure ...
...
end
//
delimiter ;

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