Use SELECT...INTO OUTFILE to export a text file.

When exporting data from a MySQL database, you are allowed to use a SELECT statement containing an export definition for the data export operation. The file is created on the server host, so you must have file write permission (FILE permission) to use this syntax. "SELECT... .INTO OUTFILE 'filename'" form of the SELECT statement writes the selected rows to a file, and filename cannot be a pre-existing file.SELECT.... The basic format of the SELECT...INTO OUTFILE statement is as follows:

SELECT columnlist  FROM table WHERE condition  INTO OUTFILE 'filename'  [OPTIONS]

You can see SELECT columnlist FROM table WHERE condition for a query statement, the query results to return to meet the specified conditions of one or more records; INTO OUTFILE statement is the role of the previous SELECT statement to query the results of the export to the name of " filename" in the external file; [OPTIONS] for the optional parameter options, OPTIONS part of the syntax, including FIELDS and LINES clauses, the possible values are as follows:

FIELDS TERMINATED BY 'value': sets the separator character between fields, which can be a single or multiple characters, and by default is a tab (\t).
FIELDS [OPTIONALLY] ENCLOSED BY 'value': set the enclosing character of the field, it can only be a single character, if the OPTIONALLY keyword is used, only character data fields such as CHAR and VERCHAR are enclosed.
FIELDS ESCAPED BY 'value': set how to write or read special characters, can only be a single character, that is, set the escape character, the default value is "\".
LINES STARTING BY 'value': set the character at the beginning of each line of data, can be single or multiple characters, by default no character is used.
LINES TERMINATED BY 'value': set the character at the end of each line of data, can be a single or multiple characters, the default value is "\n".

Both the FIELDS and LINES clauses are self-selecting, but if both are specified, FIELDS must precede LINES.

Using the SELECT.... ...INTO OUTFILE statement to dump a table to the server very quickly. If you want to create a result file on some client host other than the server host, you cannot use the SELECT... INTO OUTFILE statement. . INTO OUTFILE, you should use "MySQL -e "SELECT ..." MySQL -e "SELECT ..." > file_name" should be used to generate the file.

SELECT... .INTO OUTFILE is the complement of LOAD DATA INFILE, and the syntax used for the OPTIONS portion of the statement includes some of the FIELDS and LINES clauses that are used in conjunction with the LOAD DATA INFILE statement.

[Example 11.10] Use SELECT.... ...INTO OUTFILE to export records from the person table in the test_db database to a text file, the SQL statement is as follows:

mysql> SELECT *  FROM test_db.person INTO OUTFILE 'D:/';

The following error message is reported after the statement is executed:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

This is because MySQL has permission restrictions on the exported directory by default, which means that you need to specify the directory when you use the command line to do the export. So what is the specified directory?

The command to query the specified directory is as follows:

show global variables like '%secure%';

The results of the implementation are as follows:

+-------------------------+-----------------------------------------------+
| Variable_name           | Value                                         |
+-------------------------+-----------------------------------------------+
|require_secure_transport | OFF                                           |
|secure_file_priv         | D:\ProgramData\MySQL\MySQL Server 9.0\Uploads\|
+-------------------------+-----------------------------------------------+

Because of the secure_file_priv configuration, it must be exported to the D:\ProgramData\MySQL\MySQL Server 9.0\Uploads\ directory, which is the specified directory. If you want to customize the export path, you need to modify the configuration file. Open the path D:\ProgramData\MySQL\MySQL Server 9.0, open the file with Notepad and search for the following code:

secure-file-priv="D:/ProgramData/MySQL/MySQL Server 9.0/Uploads"

Add # comment out before the above code and add the following:

secure-file-priv="D:/"

The results are shown in Figure 11.1.

After restarting the MySQL server, use SELECT... again. ...INTO OUTFILE to export the records from the person table in the test_db database to a text file with the following SQL statement:

mysql>SELECT *  FROM test_db.person INTO OUTFILE 'D:/';
Query OK, 1 row affected (0.01 sec)

Since the INTO OUTFILE clause is specified, SELECT saves the values of the three fields queried to the C:\ file. Open the file with the following contents:

1    Green        21    Lawyer
2    Suse         22    dancer
3    Mary         24    Musician
4    Willam       20    sports man
5    Laura        25    \N
6    Evans        27    secretary
7    Dale         22    cook
8    Edison       28    singer
9    Harry        21    magician
10   Harriet      19    pianist

By default, MySQL uses tabs (\t) to separate different fields that are not surrounded by other characters. By default, when NULL is encountered, "\N" is returned, which represents the null value, where the backslash (\) represents the escape character; if you use the By default, when NULL is encountered, "\N" is returned, representing the null value, where the backslash (\) represents the escape character; if the ESCAPED BY option is used, N is preceded by the specified escape character.

[Example 11.11] Use the SELECT.... .INTO OUTFILE statement to export the records in the test_db database person table to a text file, using the FIELDS option and the LINES option, requiring that fields be separated by commas, that all field values be enclosed in double quotes, and defining the escape character as a single quote "\'". The SQL statement is as follows:

SELECT * FROM  test_db.person INTO OUTFILE "D:/"
FIELDS 
TERMINATED BY ','
ENCLOSED BY '\"'
ESCAPED BY '\''
LINES 
TERMINATED BY '\r\n';

This statement will import all records from the person table into a text file in the D drive directory.

"FIELDS TERMINATED BY ','" means that fields are separated by commas; "ENCLOSED BY '\"'" means that each field is caused by double quotes;" ESCAPED BY '\'" means that the system's default escape character is replaced by single quotes; "LINES TERMINATED BY '\r\n'" means that each line is terminated by a carriage return line feed to ensure that each record occupies one line.

After successful execution, a file is generated under the D drive. Open the file with the following contents:

"1","Green","21","Lawyer"
"2","Suse","22","dancer"
"3","Mary","24","Musician"
"4","Willam","20","sports man"
"5","Laura","25",'N'
"6","Evans","27","secretary"
"7","Dale","22","cook"
"8","Edison","28","singer"
"9","Harry","21","magician"
"10","Harriet","19","pianist"

As you can see, all field values are induced by double quotes; the null value in the fifth record is represented as "N", i.e., single quotes are used to replace the backslash escape character.

Example 11.12] Use the SELECT.... .INTO OUTFILE statement to export the records in the test_db database person table to a text file, using the LINES option, requiring that each row of records begin with the string ">" and end with the string "<end>", the SQL statement is as follows. ", the SQL statement is as follows:

SELECT * FROM  test_db.person INTO OUTFILE "D:/"
LINES 
STARTING BY '> '
TERMINATED BY '<end>';

After the statement is executed successfully, a file is generated under the D drive. Open the file with the following contents:

"1","Green","21","Lawyer"
"2","Suse","22","dancer"
"3","Mary","24","Musician"
"4","Willam","20","sports man"
"5","Laura","25",'N'
"6","Evans","27","secretary"
"7","Dale","22","cook"
"8","Edison","28","singer"
"9","Harry","21","magician"
"10","Harriet","19","pianist"

You can see that although all the field values are exported to a text file, all the records do not have a line break, which occurs because the TERMINATED BY option replaces the system's default line breaks. If you want to display line breaks, you need to modify the export statement:

SELECT * FROM  test_db.person INTO OUTFILE "D:/"
LINES 
STARTING BY '> '
TERMINATED BY '<end>\r\n';

After executing the statement, the line feed displays each record with the following result:

> 1    Green        21    Lawyer <end>
> 2    Suse         22    dancer <end>
> 3    Mary         24    Musician <end>
> 4    Willam       20    sports man <end>
> 5    Laura        25    \N <end>
> 6    Evans        27    secretary <end>
> 7    Dale         22    cook <end>
> 8    Edison       28    singer <end>
> 9    Harry        21    magician <end>
> 10   Harriet      19    pianist <end>