You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

MySQL存储过程编译与执行报Error Code 1064问题求助

avatar
AI 小北
2026-6-22
MySQL存储过程SodAnalysis的1064错误排查与修复

问题概述

尝试创建并执行名为SodAnalysis的MySQL存储过程，目标是遍历menu_input表，动态生成INSERT语句填充data_output表，但遇到以下问题：

  • 初始版本编译时持续报Error Code 1064，提示“SQL语法错误，第3行附近存在问题”
  • 调整代码后存储过程可成功创建，但调用时仍报Error Code 1064，提示“near 'NULL' at line 1”

相关表结构

menu_input表结构：

CREATE TABLE `menu_input` (
  `id` int NOT NULL AUTO_INCREMENT,
  `menu1` text,
  `menu2` text,
  `rischio` text,
  `categoria` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=63 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

初始版本代码及问题分析 

CREATE PROCEDURE SodAnalysis()
BEGIN
    DECLARE strFieldName1 VARCHAR(100);
    DECLARE strFieldName2 VARCHAR(100);
    DECLARE strSQL VARCHAR(100);
    DECLARE rischio VARCHAR(100); -- Assuming these parameters need to be declared
    DECLARE categoria VARCHAR(100); -- and assigned values before executing @strSQL

    -- Cursor to iterate through menu_input records
    DECLARE menu_cursor CURSOR FOR
    SELECT 
        menu1, 
        menu2, 
        rischio, 
        categoria
    FROM menu_input;

    OPEN menu_cursor;
    FETCH NEXT FROM menu_cursor INTO strFieldName1, strFieldName2, rischio, categoria; -- Adjusted to fetch @rischio and @categoria as well

    WHILE @@FETCH_STATUS = 0 DO
        -- Construct the SQL query with parameters A and B
        SET strSQL = CONCAT('
            INSERT INTO data_output 
            SELECT 
                c.*, 
                d.funzione AS funzione2, 
                d.processo AS processo2 
            FROM (
                SELECT 
                    a.*, 
                    b.funzione AS funzione1, 
                    b.processo AS processo1 
                FROM (
                    SELECT 
                        t1.id AS id1, 
                        t2.id AS id2, 
                        t1.utente_cod, 
                        t1.utente_des, 
                        t1.societa AS societa1, 
                        t1.societa_des AS societa_des1, 
                        t2.societa AS societa2, 
                        t2.societa_des AS societa_des2, 
                        t1.ruolo_cod AS ruolo_cod1, 
                        t1.ruolo_des AS ruolo_des1, 
                        t2.ruolo_cod AS ruolo_cod2, 
                        t2.ruolo_des AS ruolo_des2, 
                        t1.modulo AS modulo1, 
                        t2.modulo AS modulo2, 
                        t1.modulo_des AS modulo_des1, 
                        t2.modulo_des AS modulo_des2, 
                        t1.menu_funz AS menu_funz1, 
                        t2.menu_funz AS menu_funz2, 
                        t1.progr_cod AS progr_cod1, 
                        t2.progr_cod AS progr_cod2, 
                        @rischio AS rischio, 
                        @categoria AS categoria 
                    FROM 
                        data_input t1
                    INNER JOIN 
                        data_input t2 
                    ON 
                        t1.utente_cod = t2.utente_cod 
                    WHERE 
                        t1.utente_cod IN (
                            SELECT utente_cod 
                            FROM data_input 
                            WHERE menu_funz = @strFieldName1
                        ) 
                        AND t1.utente_cod IN (
                            SELECT utente_cod 
                            FROM data_input 
                            WHERE menu_funz = @strFieldName2
                        ) 
                        AND t1.menu_funz = @strFieldName1 
                        AND t2.menu_funz = @strFieldName2
                ) a 
                LEFT JOIN 
                    transcodifica_input b 
                ON 
                    a.menu_funz1 = b.MENU
            ) c 
            LEFT JOIN 
                transcodifica_input d 
            ON 
                c.menu_funz2 = d.MENU;
        ');

        -- Execute the constructed SQL query
        PREPARE stmt from StrSQL;
        SET @rischio = rischio;
        SET @categoria = categoria;
        SET @strFieldName1 = strFieldName1;
        SET @strFieldName2 = strFieldName2;
        EXECUTE stmt USING @rischio, @categoria, @strFieldName1, @strFieldName2;
        deallocate prepare stmt;

        -- Move to the next record
        FETCH NEXT FROM menu_cursor INTO strFieldName1, strFieldName2, rischio, categoria; -- Adjusted to fetch @rischio and @categoria as well
    END WHILE;

    -- Close and deallocate the cursor
    CLOSE menu_cursor;
    DEALLOCATE menu_cursor;
END;
GO

问题点

  1. 未修改DELIMITER：MySQL默认以;作为语句结束符，存储过程内部包含大量;，导致编译时提前终止解析，触发语法错误。
  2. 变量作用域错误：动态SQL中使用会话变量@rischio，但在PREPARE之后才赋值，此时SQL已编译完成，无法获取变量值。
  3. 变量长度不足：strSQL声明为VARCHAR(100)，拼接后的SQL远长于100字符，引发字符串截断。
  4. FETCH_STATUS用法错误：@@FETCH_STATUS是SQL Server语法，MySQL需用DECLARE CONTINUE HANDLER FOR NOT FOUND判断游标遍历状态。

调整后版本代码及问题分析 

-- Change the delimiter to $$ to avoid conflicts with semicolons in the procedure
DELIMITER $$

-- Create a new stored procedure named SodAnalysis
CREATE PROCEDURE SodAnalysis()

BEGIN

    -- Declare a variable to indicate when the cursor has finished reading all rows
    DECLARE done INT DEFAULT FALSE;

    -- Declare variables to hold values from the menu_input table
    DECLARE menu1 VARCHAR(255);
    DECLARE menu2 VARCHAR(255);
    DECLARE rischio VARCHAR(255);
    DECLARE categoria VARCHAR(255);

    -- Declare a variable to hold the dynamic SQL string
    DECLARE str VARCHAR(255);

    -- Define a cursor to iterate over the rows in the menu_input table
    DECLARE cur CURSOR FOR SELECT 
        menu1, 
        menu2, 
        rischio, 
        categoria
    FROM menu_input;

    -- Declare a handler to set the done variable to TRUE when the cursor reaches the end of the result set
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Open the cursor
    OPEN cur;

    -- Start a loop to read each row from the cursor
    read_loop: LOOP

        -- Fetch the next row into the variables
        FETCH cur INTO menu1, menu2, rischio, categoria;

        -- If no more rows are left, exit the loop
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Set variables for dynamic SQL
        SET @a = menu1;
        SET @b = menu2;
        SET @c = rischio;
        SET @d = categoria;

        -- Construct the dynamic SQL query string
        SET @str = CONCAT('
            INSERT INTO data_output 
            SELECT 
                c.*, 
                d.funzione AS funzione2, 
                d.processo AS processo2 
            FROM (
                SELECT 
                    a.*, 
                    b.funzione AS funzione1, 
                    b.processo AS processo1 
                FROM (
                    SELECT 
                        t1.id AS id1, 
                        t2.id AS id2, 
                        t1.utente_cod, 
                        t1.utente_des, 
                        t1.societa AS societa1, 
                        t1.societa_des AS societa_des1, 
                        t2.societa AS societa2, 
                        t2.societa_des AS societa_des2, 
                        t1.ruolo_cod AS ruolo_cod1, 
                        t1.ruolo_des AS ruolo_des1, 
                        t2.ruolo_cod AS ruolo_cod2, 
                        t2.ruolo_des AS ruolo_des2, 
                        t1.modulo AS modulo1, 
                        t2.modulo AS modulo2, 
                        t1.modulo_des AS modulo_des1, 
                        t2.modulo_des AS modulo_des2, 
                        t1.menu_funz AS menu_funz1, 
                        t2.menu_funz AS menu_funz2, 
                        t1.progr_cod AS progr_cod1, 
                        t2.progr_cod AS progr_cod2, 
                        ', @c,' AS rischio, 
                        ', @d,' AS categoria 
                    FROM 
                        data_input t1
                    INNER JOIN 
                        data_input t2 
                    ON 
                        t1.utente_cod = t2.utente_cod 
                    WHERE 
                        t1.utente_cod IN (
                            SELECT utente_cod 
                            FROM data_input 
                            WHERE menu_funz = ', @a,'
                        ) 
                        AND t1.utente_cod IN (
                            SELECT utente_cod 
                            FROM data_input 
                            WHERE menu_funz = ', @b,'
                        ) 
                        AND t1.menu_funz = ', @a,' 
                        AND t2.menu_funz = ', @b,'
                ) a 
                LEFT JOIN 
                    transcodifica_input b 
                ON 
                    a.menu_funz1 = b.MENU
            ) c 
            LEFT JOIN 
                transcodifica_input d 
            ON 
                c.menu_funz2 = d.MENU;
        ');

        -- Prepare and execute the dynamic SQL statement
        PREPARE stmt FROM @str;
        EXECUTE stmt using @a, @b, @c, @d;

        -- Deallocate the prepared statement
        DEALLOCATE PREPARE stmt;

    END LOOP;

    -- Close the cursor
    CLOSE cur;

END $$

-- Restore the delimiter to semicolon
DELIMITER ;

问题点

  1. 字符串拼接未处理引号：直接拼接变量到SQL中，若变量为字符串类型会缺失引号，若为NULL则生成NULL AS rischio等非法语法，触发“near 'NULL'”错误。
  2. 动态SQL变量长度不足：str声明为VARCHAR(255)，拼接后的SQL远超该长度，导致字符串截断。
  3. EXECUTE USING用法错误：动态SQL中未使用占位符?，EXECUTE stmt using @a, @b, @c, @d无实际作用，反而可能引发参数不匹配错误。

修复后的存储过程代码 

DELIMITER $$

CREATE PROCEDURE SodAnalysis()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE menu1 TEXT;
    DECLARE menu2 TEXT;
    DECLARE rischio TEXT;
    DECLARE categoria TEXT;

    -- 使用足够长度的变量存储动态SQL
    DECLARE strSQL LONGTEXT;

    DECLARE cur CURSOR FOR 
        SELECT menu1, menu2, rischio, categoria FROM menu_input;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO menu1, menu2, rischio, categoria;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 使用占位符?代替直接拼接变量，避免引号和NULL问题
        SET strSQL = CONCAT('
            INSERT INTO data_output 
            SELECT 
                c.*, 
                d.funzione AS funzione2, 
                d.processo AS processo2 
            FROM (
                SELECT 
                    a.*, 
                    b.funzione AS funzione1, 
                    b.processo AS processo1 
                FROM (
                    SELECT 
                        t1.id AS id1, 
                        t2.id AS id2, 
                        t1.utente_cod, 
                        t1.utente_des, 
                        t1.societa AS societa1, 
                        t1.societa_des AS societa_des1, 
                        t2.societa AS societa2, 
                        t2.societa_des AS societa_des2, 
                        t1.ruolo_cod AS ruolo_cod1, 
                        t1.ruolo_des AS ruolo_des1, 
                        t2.ruolo_cod AS ruolo_cod2, 
                        t2.ruolo_des AS ruolo_des2, 
                        t1.modulo AS modulo1, 
                        t2.modulo AS modulo2, 
                        t1.modulo_des AS modulo_des1, 
                        t2.modulo_des AS modulo_des2, 
                        t1.menu_funz AS menu_funz1, 
                        t2.menu_funz AS menu_funz2, 
                        t1.progr_cod AS progr_cod1, 
                        t2.progr_cod AS progr_cod2, 
                        ? AS rischio, 
                        ? AS categoria 
                    FROM 
                        data_input t1
                    INNER JOIN 
                        data_input t2 
                    ON 
                        t1.utente_cod = t2.utente_cod 
                    WHERE 
                        t1.utente_cod IN (
                            SELECT utente_cod 
                            FROM data_input 
                            WHERE menu_funz = ?
                        ) 
                        AND t1.utente_cod IN (
                            SELECT utente_cod 
                            FROM data_input 
                            WHERE menu_funz = ?
                        ) 
                        AND t1.menu_funz = ? 
                        AND t2.menu_funz = ?
                ) a 
                LEFT JOIN 
                    transcodifica_input b 
                ON 
                    a.menu_funz1 = b.MENU
            ) c 
            LEFT JOIN 
                transcodifica_input d 
            ON 
                c.menu_funz2 = d.MENU;
        ');

        -- 赋值会话变量，用于EXECUTE USING
        SET @rischio_val = rischio;
        SET @categoria_val = categoria;
        SET @menu1_val = menu1;
        SET @menu2_val = menu2;

        PREPARE stmt FROM strSQL;
        -- 按占位符顺序传递参数
        EXECUTE stmt USING @rischio_val, @categoria_val, @menu1_val, @menu2_val, @menu1_val, @menu2_val;
        DEALLOCATE PREPARE stmt;

    END LOOP;

    CLOSE cur;
END $$

DELIMITER ;

修复要点

  1. 调整变量类型与长度：将动态SQL变量改为LONGTEXT避免截断，menu1等变量改为TEXT匹配表字段类型。
  2. 使用占位符传递参数：用?作为占位符，解决字符串引号缺失和NULL值导致的语法错误。
  3. 匹配参数传递顺序：根据占位符在SQL中的出现顺序，在EXECUTE USING中正确传递对应变量。
  4. 保留正确游标逻辑：使用CONTINUE HANDLER确保游标遍历完成后正常退出循环。

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.06.22 23:35:56