BEGIN 后 MySQL 存储过程语法错误

Question

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

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

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

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 并得到了相同的结果，尽管粘贴到 MySQL Syntax Check 让我竖起了大拇指。我敢肯定这是一个简单的失误，但对我来说并不是那么明显。

Answer 1

您缺少 delimiter存储过程定义前后的定义:

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.

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.

由于存储过程定义和主体没问题，syntax chack 给了你竖起大拇指，但代码无法在你的客户端中正常运行。

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

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