带有时间戳的MySQL事件.csv输出文件(MySQL event .csv outputfile with timestamp in name)

带有时间戳的MySQL事件.csv输出文件(MySQL event .csv outputfile with timestamp in name)

我有一个带有3个表（日志数据）的MySQL数据库。 每张桌子都采用相同的结构。

现在我想编写一个每6个月运行一次的事件，并导出一个csv文件中的所有行，这些行比半年早，并在第二个时间删除它们。 电影名称应包括出口时间戳。

一开始我试图用一个静态名称导出一个表格 - 效果很好（这里用较小的时间间隔进行测试）：

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)

现在我尝试使用包含导出时间戳的动态文件名来处理此问题。 所以，旧的文件也不会被覆盖。 但这没有用。 有人可以帮忙吗？

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;

感谢帮助！

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!

最满意答案

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 ; 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 ;