Database

How To Produce Dynamic Filename Using MySQL OUTFILE (Example: Date)

In previous article i have introduced you how to Export MySQL Database to an Excel File using a single simple query. Many people asks how to produce dynamic file name that can be used as archive for database, for example: 19June2016-21.59.csv . This is the query:

SET @sql_text =
CONCAT (
“SELECT * FROM tablename into outfile ‘C:/”, DATE_FORMAT(NOW(), ‘%d%M%Y-%H.%i’),”.csv’
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’
“);

PREPARE s1 FROM @sql_text;
EXECUTE s1;
DROP PREPARE s1;

It works?, isn’t it?… 🙂

Just replace the tablename with your table, and  %d%M%Y-%H.%i with your desired date format.

*) Notes: The above codes will be very helpful if you combine it with MySQL Scheduled Event where you can automatically export an excel file as a backup of your database server in specific desired time (once a day, once a week, every hour, etc)

Leave a Reply

1 Comment

[…] *) Notes: if you want to use dynamic file name instead of myfile.csv, then you should read this article […]