MySQL event .csv outputfile with timestamp in name

Question

i have a MySQL database with 3 tables (log data). Each table gots the same structure.

Now i want to write an event that runs every 6 months and exports all rows in a csv file, who are older than a half year and delete them in the second. The filname should include the timestamp of export.

In the beginning i tried to export a table with a static name - that works well (here with smaller time intervalls for testing):

CREATE EVENT exportLog
ON SCHEDULE
EVERY 1 MINUTE
DO
SELECT *
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/exporttest.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM mytable WHERE timestamp < (NOW() - INTERVAL 5 DAY)

Now i try to handle this with a dynamic filename including the timestamp of export. So, that also the older file will never be overwritten. But this doesnt work. Can somebody help?

CREATE EVENT exportLog
ON SCHEDULE
EVERY 1 MINUTE
DO
BEGIN
SET @sql_stmt := concat("SELECT * FROM logtable INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/export_", DATE_FORMAT(now(),'%Y-%m-%d %H%i%s'),".csv'");
PREPARE extrct FROM @sql_stmt;
EXECUTE extrct;
DEALLOCATE PREPARE extrct;
END $$
DELIMITER;

Thanks for help!

Answer 1

DELIMITER $$
drop event if exists exportLog  $$
CREATE EVENT exportLog
ON SCHEDULE
EVERY 1 MINUTE
DO
BEGIN
declare cnt tinyint(1) default 1;
    table_loop: LOOP
        SET @sql_stmt := concat("SELECT *  INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/export_", elt(cnt, 'logtable', 'logtable1', 'logtable2'), DATE_FORMAT(now(),'%Y-%m-%d %H%i%s'),".csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\\\' LINES TERMINATED BY '\\n' FROM ", elt(cnt, 'logtable', 'logtable1', 'logtable2'));
        PREPARE extrct FROM @sql_stmt;
        EXECUTE extrct;
        DEALLOCATE PREPARE extrct;
        SET cnt = cnt + 1;

        IF cnt < 4 THEN
          ITERATE table_loop;
        END IF;
        LEAVE table_loop;
    END LOOP table_loop;

END $$
DELIMITER ;