I would like to generate individual text files from an SQL query result. Given a table like:

is there a simple way to create a text file for each row containing fileContents and named fileName? Thanks in advance!

CodePudding user response：

You need in something like this (not tested, debugging needed):

CREATE PROCEDURE save_to_files ()
BEGIN
DECLARE not_done INT DEFAULT TRUE;
DECLARE cur CURSOR FOR 
    SELECT fileName, fileContents FROM tablename;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_done = FALSE;
OPEN cur;
FETCH cur INTO @fn, @fc;
WHILE not_done 
    SET @sql = CONCAT('SELECT ? INTO DUMPFILE "', @fn, '";');
    PREPARE stmt FROM @sql;
    EXECUTE stmt USING @fc;
    DROP PREPARE stmt;
    FETCH cur INTO @fn, @fc;
END WHILE;
CLOSE cur;
END