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!

Alexey · Accepted Answer · 2017-04-25T07:20:22.623

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 ;

edited Apr 25 '17 at 07:20

answered Apr 24 '17 at 12:23

Alexey

2,388
1
16
32

Thanks for your answer. When i use your Set-Statement i got still an syntax error: missing semicolon. – Apr 24 '17 at 12:34
well try applying a semicolon at the end though it worked even without it for me – Alexey Apr 24 '17 at 12:40
Just nice to check...does your `show full processlist` contins event scheduler? – Alexey Apr 24 '17 at 12:55
Thanks for your reply! With your code i doesnt get a syntax error of the sql code in the workbench. When i compared your code i see that i lost a " in my syntax. But there comes the next problem. I set up the event with your code. In the workbench it runs without an error. But in my directory are no export files written. Do you have an idea? – Apr 25 '17 at 05:06
It works! After a server restart the event scheduler was disabled. So i have to setup the event scheduler by starting the server :) I got another question: I had 3 tables (with same structure) to export. So i could do your code 3 times for every table. Is the e better way to declare the 3 tablenames in a array and run it for every table? – Apr 25 '17 at 05:17
Updated my answer. If you'll have more tables don't forget to update both `elt` arrays and condition for `cnt` – Alexey Apr 25 '17 at 06:38
Wow, thank you very much! A last problem: When i have a look on the export csv files, the format is not optimal. So i need to set delimeter etc. like in my post in the beginning: FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' When i add this to the concat function and execute it, there is no sql syntax error but the export doesnt work. I think there is a problem using the quotes? – Apr 25 '17 at 07:05
updated again :) just a ton of escaping. don't forget to vote up for my answer :) – Alexey Apr 25 '17 at 07:20

MySQL event .csv outputfile with timestamp in name

1 Answers1