How does MySQL export files with date format and mysql export date format?

How does MySQL export files with date format and mysql export date format?

A netizen asked how to use only SQL statements in MySQL to export files with date format. I thought it was a bit interesting, so I tried it. The exported file uses select into outfile, but the values following OUTFILE cannot use variables, so they can only be implemented using dynamic SQL statements. The user table is the test table. The statement is as follows:

mysql> SET @SqlScript= CONCAT("SELECT * INTO OUTFILE '/tmp/sql_out_", DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), ".txt' FROM `user`;");

Query OK, 0 rows affected (0.01 sec)

mysql> PREPARE Sql_Text FROM @SqlScript;

Query OK, 0 rows affected (0.00 sec)

Statement prepared

mysql> EXECUTE Sql_Text;

Query OK, 33 rows affected (0.00 sec)

mysql> 

Note the following when selecting into outfile: Make sure that the current account has the write permission for the corresponding directory. Otherwise, a Permission error occurs. "Can't create/write to file 'xxxxx' (Errcode: 13-Permission denied )"

In addition, the SQL syntax and knowledge points of MySQL prepare statements are as follows:

PREPARE statement_name FROM preparable_ SQL _statement;/* definition */

EXECUTE statement_name [USING @ var_name [, @ var_name]...];/* EXECUTE the preprocessing statement */

{DEALLOCATE | DROP} PREPARE statement_name/* delete definition */;

The PREPARE statement is used to PREPARE a statement and specify the name statement_name. This statement will be referenced later. The statement name is case insensitive. Preparable_ SQL _statement can be a text string or a user variable containing the statement text. The text must be expressed as a single SQL statement, rather than multiple statements. In this statement ,'? The 'character can be used to identify a parameter. When executed, it indicates that the data value is bound to the query. '? 'Characters should not be enclosed in quotation marks, even if you want to combine them with string values. Parameter tags can only be used where data values should appear, rather than SQL keywords, identifiers, and so on.

If you can use tools such as shell scripts, there are still many ways to export data files with date format. Do not expand too much here.