MySQL存储过程执行报1064语法错误，求修正方案

问题场景 #

调用存储过程时触发SQL语法错误：

AED8C8C1CAC0 SQL (1064): 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 'on best_avg(tbl_name)' at line 1

该存储过程目标是删除当前数据库所有基表中包含多个字段的非主键索引。尝试过两种修改：用ALTER TABLE替换DROP INDEX、直接执行DROP INDEX（因表名/索引名为字符串失败），需要排查报错原因并给出正确实现。

原存储过程代码：

delimiter &&
 CREATE PROCEDURE dropping_indexes_excpt_for_primaryone()
 BEGIN 
    DECLARE done INT DEFAULT 0;

    DECLARE tbl_name VARCHAR(200) DEFAULT '';
    
    DECLARE all_tables_cursor CURSOR for 
     (SELECT TABLE_NAME
      FROM information_schema.tables
      WHERE table_schema = DATABASE()
      AND table_type = 'BASE TABLE');
    
    DECLARE CONTINUE handler FOR NOT FOUND SET done = 1;
    
    OPEN all_tables_cursor;
    tables_loop: LOOP
        fetch all_tables_cursor INTO tbl_name;
        if done then
            leave tables_loop;
        END if;
        BLOCK2: BEGIN 
        DECLARE index_name VARCHAR(200) DEFAULT '';
        
        DECLARE done_attr INT DEFAULT 0;
        
        DECLARE all_indexes_cursor CURSOR FOR 
            (SELECT index_name  FROM information_schema.STATISTICS AS inf
                WHERE TABLE_NAME = tbl_name AND table_schema= DATABASE() AND inf.index_name <>'PRIMARY' 
                GROUP BY inf.index_name
                HAVING COUNT(*)>1
            );
            
        DECLARE CONTINUE handler FOR NOT FOUND SET done_attr = 1;
        
        OPEN all_indexes_cursor; 
        indexes_loop: LOOP
            fetch all_indexes_cursor INTO index_name;
            if done_attr then leave indexes_loop;
            END if;
            
            SET @tmp = CONCAT('drop index ',index_name, ' on ', tbl_name);
            PREPARE dropping FROM @tmp;
            EXECUTE dropping;
            DEALLOCATE PREPARE dropping;        
            END loop indexes_loop;
        close all_indexes_cursor;
        END BLOCK2;     
        END loop tables_loop;
    close all_tables_cursor;
    END;
&&
        
DROP PROCEDURE dropping_indexes_excpt_for_primaryone    
CALL dropping_indexes_excpt_for_primaryone()

报错原因 #

1. 标识符未加反引号：如果表名或索引名包含特殊字符（如空格、MySQL关键字），直接拼接SQL会导致语法解析错误，报错中的异常片段就是因为标识符未被正确识别。
2. 分隔符未重置：原代码创建存储过程后未将分隔符改回默认的;，可能导致后续调用语句执行异常。

正确实现方案 #

核心修改是给表名、索引名添加反引号，同时优化存储过程的执行安全性：

DELIMITER &&
CREATE PROCEDURE dropping_indexes_excpt_for_primaryone()
BEGIN 
    DECLARE done INT DEFAULT 0;
    DECLARE tbl_name VARCHAR(200) DEFAULT '';
    
    -- 遍历当前库所有基表
    DECLARE all_tables_cursor CURSOR FOR 
        SELECT TABLE_NAME
        FROM information_schema.tables
        WHERE table_schema = DATABASE()
          AND table_type = 'BASE TABLE';
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN all_tables_cursor;
    tables_loop: LOOP
        FETCH all_tables_cursor INTO tbl_name;
        IF done THEN
            LEAVE tables_loop;
        END IF;
        
        BLOCK2: BEGIN 
            DECLARE index_name VARCHAR(200) DEFAULT '';
            DECLARE done_attr INT DEFAULT 0;
            
            -- 筛选当前表中包含多个字段的非主键索引
            DECLARE all_indexes_cursor CURSOR FOR 
                SELECT index_name  
                FROM information_schema.STATISTICS AS inf
                WHERE TABLE_NAME = tbl_name 
                  AND table_schema = DATABASE() 
                  AND inf.index_name <> 'PRIMARY' 
                GROUP BY inf.index_name
                HAVING COUNT(*) > 1;
                
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_attr = 1;
            
            OPEN all_indexes_cursor; 
            indexes_loop: LOOP
                FETCH all_indexes_cursor INTO index_name;
                IF done_attr THEN 
                    LEAVE indexes_loop;
                END IF;
                
                -- 给表名、索引名添加反引号，避免语法错误
                SET @tmp = CONCAT('DROP INDEX `', index_name, '` ON `', tbl_name, '`');
                PREPARE dropping FROM @tmp;
                EXECUTE dropping;
                DEALLOCATE PREPARE dropping;        
            END LOOP indexes_loop;
            CLOSE all_indexes_cursor;
        END BLOCK2;     
    END LOOP tables_loop;
    CLOSE all_tables_cursor;
END;
&&
DELIMITER ;

-- 安全删除旧存储过程（如果存在）
DROP PROCEDURE IF EXISTS dropping_indexes_excpt_for_primaryone;
-- 调用存储过程
CALL dropping_indexes_excpt_for_primaryone();

关键修改说明 #

• 添加反引号：用`包裹index_name和tbl_name，确保特殊字符或关键字类型的标识符能被MySQL正确解析。
• 重置分隔符：添加DELIMITER ;将SQL分隔符恢复默认，避免后续语句执行异常。
• 安全删除逻辑：用DROP PROCEDURE IF EXISTS替代直接删除，避免存储过程不存在时触发报错。

内容的提问来源于stack exchange，提问作者ХеллБой