MariaDB存储过程：Concat循环中@ID不可见及JSON构造报错处理

问题根源 #

你遇到的语法错误，本质是动态SQL拼接时变量未被正确解析。用CONCAT()拼接SQL字符串时，如果直接把@ID写进字符串里，MariaDB会把它当作普通文本，而不是去读取变量的实际值。比如你写CONCAT('SELECT ... WHERE idInstrument = ', @ID)，这里的@ID会被当成字符串字面量，导致SQL语句语法错误或者逻辑不符。

可行解决方案 #

1. 用预处理语句+占位符（推荐，安全又可靠） #

不要直接把变量拼进SQL字符串，改用占位符?，再通过EXECUTE ... USING传递变量值。这种方式既能保证变量被正确识别，还能避免SQL注入风险。

示例代码：

DELIMITER //
CREATE PROCEDURE process_instruments()
BEGIN
    DECLARE outer_done BOOLEAN DEFAULT FALSE;
    DECLARE inner_done BOOLEAN DEFAULT FALSE;
    DECLARE current_instrument INT; -- 存储当前要查询的idInstrument值
    SET @data_json = '[]';

    -- 外层循环逻辑
    WHILE NOT outer_done DO
        -- 内层循环逻辑
        WHILE NOT inner_done DO
            -- 此处省略获取current_instrument值的逻辑
            
            -- 用预处理语句执行动态SQL
            PREPARE get_id_stmt FROM 'SELECT id INTO @ID FROM your_table WHERE idInstrument = ?';
            EXECUTE get_id_stmt USING current_instrument;
            DEALLOCATE PREPARE get_id_stmt;

            -- 构造JSON并追加到结果
            SET @data_json = JSON_ARRAY_APPEND(@data_json, '$', JSON_OBJECT('instrument_id', @ID));

            -- 更新内层循环终止条件（示例）
            -- SET inner_done = (current_instrument >= 100);
        END WHILE;
        SET inner_done = FALSE;
        -- 更新外层循环终止条件（示例）
        -- SET outer_done = (...);
    END WHILE;
END //
DELIMITER ;

2. 若必须拼接字符串，确保变量值被正确插入 #

如果一定要用CONCAT拼接，要根据变量类型处理：

• 数值类型变量：直接拼接变量值即可
• 字符串类型变量：需要手动添加引号（不推荐，易引发SQL注入）

示例：

-- 数值类型idInstrument的拼接写法
SET @dynamic_sql = CONCAT('SELECT id INTO @ID FROM your_table WHERE idInstrument = ', current_instrument);
PREPARE stmt FROM @dynamic_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 字符串类型idInstrument的拼接写法（需加引号）
SET @dynamic_sql = CONCAT('SELECT id INTO @ID FROM your_table WHERE idInstrument = ''', current_instrument, '''');
PREPARE stmt FROM @dynamic_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

3. 确认变量作用域 #

动态SQL是在会话上下文执行的，无法访问存储过程内用DECLARE定义的局部变量。所以必须用会话级变量（带@前缀，比如@ID），或者通过EXECUTE ... USING传递局部变量（像方案1里的current_instrument）。

注意事项 #

• 每次执行完预处理语句后，一定要用DEALLOCATE PREPARE释放资源，避免内存占用过高。
• 优先用占位符方案，不要直接拼接变量到SQL字符串，防止SQL注入漏洞。
• 测试时可以先打印拼接好的动态SQL（比如SELECT @dynamic_sql;），快速排查语法错误。

内容的提问来源于stack exchange，提问作者Fernando Garcia - Nandostyle