MySQL存储过程创建报错：执行输入查询生成SQL的问题排查

问题背景 #

需要实现一个MySQL存储过程，接收字符串参数qry_str作为查询语句，通过该查询生成多条SQL并逐一执行。编写的存储过程执行时抛出语法错误：

Error Code: 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 'DECLARE curs CURSOR FOR stmt0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SE' at line 10

原错误代码：

DELIMITER $$

CREATE
    PROCEDURE `zzz_test`.`nest_query`(IN qry_str VARCHAR(65535))

    BEGIN
        DECLARE bDone INT;
        
        DECLARE qry VARCHAR(65535);
        SET @query0 = qry_str;
        PREPARE stmt0 FROM @query;
        DECLARE curs CURSOR FOR  stmt0;
        
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
        
        OPEN curs;
        SET bDone = 0;
        REPEAT
            FETCH curs INTO qry;
            SET @query = qry;
            PREPARE stmt FROM @query;
            EXECUTE stmt;
        UNTIL bDONE END REPEAT;
        CLOSE curs;
    END$$

DELIMITER ;

此前成功运行的固定查询存储过程参考：

DELIMITER $$

USE `zzz_test`$$

DROP PROCEDURE IF EXISTS `test2`$$

CREATE DEFINER=`root`@`%` PROCEDURE `test2`()
BEGIN
        DECLARE bDone INT;
        
        DECLARE qry VARCHAR(65535);
        DECLARE curs CURSOR FOR  SELECT CONCAT('INSERT INTO zzz_test.test2 SELECT "',TABLE_NAME,'" as tb_name, ... FROM zzz_test0.',TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='zzz_test0';
        
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
        
        OPEN curs;
        SET bDone = 0;
        REPEAT
            FETCH curs INTO qry;
            SET @query = qry;
            PREPARE stmt FROM @query;
            EXECUTE stmt;
        UNTIL bDONE END REPEAT;
        CLOSE curs;
    END$$

DELIMITER ;

错误原因 #

1. DECLARE语句位置违规：MySQL强制要求所有DECLARE声明（变量、游标、处理器）必须放在BEGIN块的最开头，不能穿插在SET、PREPARE等执行语句之后。原代码中SET @query0 = qry_str;和PREPARE stmt0 FROM @query;放在了DECLARE curs之前，触发语法错误。
2. 预处理变量引用错误：原代码中PREPARE stmt0 FROM @query;引用了未定义的@query，正确应该是@query0。
3. 游标绑定规则限制：MySQL不支持直接将游标绑定到预处理语句的结果集，必须通过临时表中转结果。

修正后的存储过程 #

DELIMITER $$

CREATE PROCEDURE `zzz_test`.`nest_query`(IN qry_str VARCHAR(65535))
BEGIN
    -- 所有DECLARE必须放在BEGIN块最顶部
    DECLARE bDone INT DEFAULT 0;
    DECLARE qry VARCHAR(65535);
    DECLARE temp_table_exists INT DEFAULT 0;

    -- 清理已存在的临时表（如果有）
    SELECT COUNT(*) INTO temp_table_exists 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'temp_qry_results';

    IF temp_table_exists = 1 THEN
        DROP TABLE temp_qry_results;
    END IF;

    -- 创建临时表存储查询生成的SQL语句
    CREATE TEMPORARY TABLE temp_qry_results (sql_stmt VARCHAR(65535));

    -- 执行传入的查询，将结果插入临时表
    SET @insert_sql = CONCAT('INSERT INTO temp_qry_results ', qry_str);
    PREPARE insert_stmt FROM @insert_sql;
    EXECUTE insert_stmt;
    DEALLOCATE PREPARE insert_stmt;

    -- 基于临时表创建游标
    DECLARE curs CURSOR FOR SELECT sql_stmt FROM temp_qry_results;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;

    -- 遍历游标执行每条SQL
    OPEN curs;
    REPEAT
        FETCH curs INTO qry;
        IF NOT bDone THEN
            SET @query = qry;
            PREPARE stmt FROM @query;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt; -- 及时释放预处理语句，避免内存泄漏
        END IF;
    UNTIL bDone END REPEAT;
    CLOSE curs;

    -- 清理临时表
    DROP TABLE temp_qry_results;
END$$

DELIMITER ;

需求可行性说明 #

该需求完全可以实现。核心解决思路是通过临时表中转预处理查询结果，绕开MySQL游标不能直接绑定预处理语句的限制。修正后的流程：

1. 将传入的查询语句执行结果存入临时表
2. 基于临时表创建游标遍历所有生成的SQL语句
3. 逐个预处理并执行这些SQL语句

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