MySQL 6.0.11存储过程IF ELSEIF报1064语法错误如何解决？

核心错误原因 #

你的报错核心是IF分支结构的嵌套和配对逻辑完全混乱，具体问题如下：

• 第一个WEB分类判断的IF块中，内层插入逻辑结束后多写了一个END;，直接提前终止了存储过程的顶层BEGIN块，导致后续的ELSE语法完全非法，这就是你看到1064报错的直接原因
• IF配对逻辑错位：记录不存在走INSERT、存在走UPDATE的ELSE分支，错误和最外层的分类判断IF配对，实际应该和同层级的「记录是否存在」判断的IF配对
• 冗余嵌套：多处无意义的BEGIN-END块大幅提升了结构混乱度
• 逻辑笔误：SMS分类的SmsStrategyPicks更新条件错误写了WSCategory = 'WEB'，导致该分支永远不会触发
• 多余结束符：代码末尾多了3个冗余的END，导致BEGIN和END的数量不匹配

修正后完整代码 #

DELIMITER $$

USE `marketprice_nse`$$

DROP PROCEDURE IF EXISTS `webandsmsnotificationsinsert`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `webandsmsnotificationsinsert`(
    IN WSNUid VARCHAR(20), 
    IN WSNColType VARCHAR(20), 
    IN WSCategory VARCHAR(10),
    IN WSNOptType VARCHAR(20)
)
BEGIN
    -- 先判断UID非空
    IF WSNUid != '' THEN
        -- 处理WEB类通知
        IF WSCategory = 'WEB' THEN
            -- 判断WEB通知表是否存在当前UID记录
            IF (SELECT COUNT(*) FROM tbl_webnotifications WHERE uid = WSNUid) = 0 THEN
                -- 不存在则插入
                IF WSNOptType = 'WebIntradayPicks' THEN
                    INSERT INTO tbl_webnotifications(uid,WebIntradayPicks,WebAlerts,WebStrategyPicks)
                    VALUES (WSNUid,WSNColType,'False','False'); 
                ELSEIF WSNOptType = 'WebAlerts' THEN
                    INSERT INTO tbl_webnotifications(uid,WebIntradayPicks,WebAlerts,WebStrategyPicks)
                    VALUES (WSNUid,'False',WSNColType,'False');
                ELSEIF WSNOptType = 'WebStrategyPicks' THEN
                    INSERT INTO tbl_webnotifications(uid,WebIntradayPicks,WebAlerts,WebStrategyPicks)
                    VALUES (WSNUid,'False','False',WSNColType); 
                END IF;
            ELSE
                -- 存在则更新
                IF WSNOptType = 'WebIntradayPicks' THEN
                    UPDATE tbl_webnotifications SET WebIntradayPicks = WSNColType WHERE UID = WSNUid;
                ELSEIF WSNOptType = 'WebAlerts' THEN
                    UPDATE tbl_webnotifications SET WebAlerts = WSNColType WHERE UID = WSNUid;
                ELSEIF WSNOptType = 'WebStrategyPicks' THEN
                    UPDATE tbl_webnotifications SET WebStrategyPicks = WSNColType WHERE UID = WSNUid;
                END IF;         
            END IF;
        -- 处理SMS类通知
        ELSEIF WSCategory = 'SMS' THEN
            -- 判断SMS通知表是否存在当前UID记录
            IF (SELECT COUNT(*) FROM tbl_smsnotifications WHERE uid = WSNUid) = 0 THEN
                -- 不存在则插入
                IF WSNOptType = 'SmsIntradayPicks' THEN
                    INSERT INTO tbl_smsnotifications(uid,SmsIntradayPicks,SmsAlerts,SmsStrategyPicks)
                    VALUES (WSNUid,WSNColType,'False','False');
                ELSEIF WSNOptType = 'SmsAlerts' THEN
                    INSERT INTO tbl_smsnotifications(uid,SmsIntradayPicks,SmsAlerts,SmsStrategyPicks)
                    VALUES (WSNUid,'False',WSNColType,'False'); 
                ELSEIF WSNOptType = 'SmsStrategyPicks' THEN
                    INSERT INTO tbl_smsnotifications(uid,SmsIntradayPicks,SmsAlerts,SmsStrategyPicks)
                    VALUES (WSNUid,'False','False',WSNColType); 
                END IF;
            ELSE
                -- 存在则更新
                IF WSNOptType = 'SmsIntradayPicks' THEN
                    UPDATE tbl_smsnotifications SET SmsIntradayPicks = WSNColType WHERE UID = WSNUid;   
                ELSEIF WSNOptType = 'SmsAlerts' THEN
                    UPDATE tbl_smsnotifications SET SmsAlerts = WSNColType WHERE UID = WSNUid;  
                ELSEIF WSNOptType = 'SmsStrategyPicks' THEN
                    UPDATE tbl_smsnotifications SET SmsStrategyPicks = WSNColType WHERE UID = WSNUid;  
                END IF;
            END IF;
        END IF;
    END IF;
END$$

DELIMITER ;

额外说明 #

你之前看到的1305警告属于正常提示：第一次删除存储过程时，库中没有对应存储过程就会抛出该警告，不影响后续创建逻辑。

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