How can I export data to a CSV file whose file name contains the file creation timestamp?

#Sometimes we need to export data to a CSV file named with the timestamp of when the file was created. This can be done with the help of MySQL prepared statements. To illustrate this, we use the following example -

Example

The query in the following example will export data from table "student_info" to A CSV file with a timestamp in its name.

mysql> SET @time_stamp = DATE_FORMAT(NOW(),'_%Y_%m_%d_%H_%i_%s');
Query OK, 0 rows affected (0.00 sec)

mysql> SET @FOLDER = 'C:/mysql/bin/mysql-files';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @FOLDER = 'C:/mysql/bin/mysql-files/';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @PREFIX = 'Student15';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @EXT = '.CSV';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @Command = CONCAT("SELECT * FROM Student_info INTO OUTFILE '",@FOLDER, @PREFIX, @time_stamp, @EXT,"' FIELDS ENCLOSED BY '\"' TERMINATED BY ';'
ESCAPED BY '\"'"," LINES TERMINATED BY '\r';");
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM @command;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute stmt;
Query OK, 6 rows affected (0.07 sec)

The above query will create a CSV file named "student_2017_12_10_18_52_46.CSV", i.e. a CSV file with timestamp values, with the following data -

101;"YashPal";"Amritsar";"History"
105;"Gaurav";"Chandigarh";"Literature"
125;"Raman";"Shimla";"Computers"
130;"Ram";"Jhansi";"Computers"
132;"Shyam";"Chandigarh";"Economics"
133;"Mohan";"Delhi";"Computers"

The above is the detailed content of How can I export data to a CSV file whose file name contains the file creation timestamp?. For more information, please follow other related articles on the PHP Chinese website!