MySQL存储过程中DECLARE CheckExists int触发#1064错误如何解决？

问题原因 #

你遇到的#1064语法错误是未修改SQL默认分隔符导致的。MySQL默认使用;作为语句结束标识，直接执行存储过程创建代码时，数据库会把DECLARE CheckExists int;末尾的;识别为整个CREATE PROCEDURE语句的结束点，后续代码无法被正常解析，因此抛出语法错误。

解决方法 #

按照如下规则调整执行逻辑即可：

• 执行存储过程创建语句前，先临时修改分隔符为其他不会和内部代码冲突的符号，比如//
• 存储过程代码结束后，再将分隔符改回默认的;

修正后完整可运行代码 #

-- 临时修改分隔符为//
DELIMITER //

CREATE DEFINER=`root`@`localhost` PROCEDURE `INSERT_JP_USERS`(
    IN `Email_Param` VARCHAR(50),
    IN `First_Name_Param` VARCHAR(50),
    IN `Middle_Name_Param` VARCHAR(50),
    IN `Last_Name_Param` VARCHAR(50),
    IN `Gender_Param` VARCHAR(50),
    IN `Phone_Number_Param` VARCHAR(50),
    IN `Summary_Param` VARCHAR(50),
    IN `Experience_Param` INT,
    IN `Profile_Img_Param` VARCHAR(50),
    OUT `Result_Param` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

DECLARE CheckExists int;

SELECT count(*) INTO CheckExists FROM jp_users WHERE email = Email_Param;

IF (CheckExists > 0) THEN

UPDATE jp_users SET first_name = First_Name_Param, middle_name = Middle_Name_Param, last_name = Last_Name_Param,
gender = Gender_Param, phone_number = Phone_Number_Param, summary = Summary_Param, experience = Experience_Param, profile_img = Profile_Img_Param WHERE email = Email_Param;

SET Result_Param = 0;

SELECT Result_Param;

ELSE 

INSERT INTO jp_users (email, first_name, middle_name, last_name, gender, phone_number, summary, experience, profile_img)
 
VALUES (Email_Param, First_Name_Param, Middle_Name_Param, Last_Name_Param, Gender_Param, Phone_Number_Param, Summary_Param, Experience_Param, Profile_Img_Param);

SELECT COUNT(*) INTO Result_Param FROM jp_users WHERE email = Email_Param;

SELECT Result_Param;
 
END IF;

END //

-- 改回默认分隔符
DELIMITER ;

内容的提问来源于stack exchange，提问作者selkarimi