使用动态SQL将Blob列保存到文件的问题求助

问题原因 #

• 存储过程中声明的eml是局部变量，动态SQL语句（PREPARE/EXECUTE）无法直接访问存储过程的局部作用域变量，直接写SELECT eml INTO DUMPFILE会触发语法错误。
• 使用用户变量@eml时，未将局部变量eml的值赋值给@eml，导致@eml为空，最终导出空文件。

修正后的代码 #

DROP PROCEDURE IF EXISTS SearchAndExport;
DELIMITER //
CREATE PROCEDURE SearchAndExport(IN search VARCHAR(255), IN limitOutput INT)
BEGIN
    -- Handlers
    DECLARE eof INT DEFAULT FALSE;
    
    -- Outputs
    DECLARE emailId CHAR(36);
    DECLARE eml LONGBLOB;
    
    -- Cursor / Search Select
    -- TODO: This is a sample only for debug
    DECLARE emails CURSOR FOR 
      SELECT Id,RawContent FROM Email
      LIMIT limitOutput;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET eof = TRUE;

    OPEN emails;
    SELECT CONCAT('SearchTextInContentAndDump: Search ''',search,'''. Limit: ',limitOutput,'. Saving files in ',@@global.secure_file_priv) AS log;
    emailLoop: LOOP
        FETCH emails INTO emailId, eml;
        IF eof THEN 
            LEAVE emailLoop;
        END IF;
        SELECT CONCAT('Saving email: ',@@global.secure_file_priv,emailId) AS status;
        
        -- 关键：将局部变量eml赋值给用户变量@eml，让动态SQL可以访问
        SET @eml = eml;
        -- 使用QUOTE函数转义文件名，避免特殊字符导致的SQL语法错误或注入风险
        SET @savesql := CONCAT('SELECT @eml INTO DUMPFILE ', QUOTE(CONCAT(@@global.secure_file_priv, emailId, '.eml')));
        PREPARE dyn FROM @savesql;
        EXECUTE dyn;
        DEALLOCATE PREPARE dyn;

    END LOOP;
    
    CLOSE emails;

END //
DELIMITER ;
CALL SearchAndExport('Test',5);

关键修改点 #

1. 变量传递：添加SET @eml = eml;，将存储过程局部变量eml的值传递给全局用户变量@eml，确保动态SQL能获取到Blob内容。
2. 文件名安全处理：使用QUOTE()函数包裹文件路径，自动转义路径中的特殊字符（如单引号），避免SQL语法错误和注入风险。

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