Home > Database > Mysql Tutorial > body text

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

WBOY
Release: 2023-09-03 15:53:02
forward
1198 people have browsed it

如何将数据导出到文件名包含文件创建时间戳的 CSV 文件?

#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)
Copy after login

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"
Copy after login

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!

source:tutorialspoint.com
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template