Export mySQL table records to individual files

user_18367963 0
Viewed 43

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

fileName fileContents
name1.txt "text1"
name2.txt "text2"
... ...
name100.txt "text100"

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

mysql sql
1 Answers

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
user_101387340
Related