How to export a table in mysql?

Export method: Through the "SELECTI...INTO OUTFILE" statement, use the "SELECT column name FROM table [WHERE statement] INTO OUTFILE 'target file' [OPTIONS]" format statement to export the contents of the table into A text file.

#By importing and exporting data tables, data can be moved between the MySQL database server and other database servers. Exporting refers to copying the data of the MySQL data table to a text file. There are many ways to export data. This section mainly introduces the use of SELECTI...INTO OUTFILE statement to export data.

In MySQL, you can use the SELECTI...INTO OUTFILE statement to export the contents of a table to a text file. The basic format of the SELECT...INTO OUTFILE statement is as follows:

SELECT 列名 FROM table [WHERE 语句] INTO OUTFILE '目标文件'[OPTIONS]

This statement uses SELECT to query the required data and INTO OUTFILE to export the data. Among them, the target file is used to specify the file to which the queried records are exported. What needs to be noted here is that the target file cannot be an existing file.

• [OPTIONS] is an optional parameter option. The syntax of the OPTIONS part includes FIELDS and LINES clauses. Its commonly used values ​​are:

• FIELDS TERMINATED BY 'String': Set the string as the delimiter between fields, which can be single or multiple characters. By default, it is the tab character '\t'.

• FIELDS [OPTIONALLY] ENCLOSED BY 'Character': Set characters to enclose character fields such as CHAR, VARCHAR, and TEXT. If OPTIONALLY is used, it can only be used to enclose character fields such as CHAR and VARCHAR.

• FIELDS ESCAPED BY 'Character': Set how to write or read special characters. It can only be a single character, that is, set the escape character. The default value is '\'.

• LINES STARTING BY 'String': Set the character at the beginning of each line, which can be a single or multiple characters. By default, no characters are used.

• LINES TERMINATED BY 'String': Set the character at the end of each line, which can be single or multiple characters. The default value is '\n'.

Note: Both FIELDS and LINES clauses are optional, but if both are specified, FIELDS must be placed before LINES.

Example 1

The following uses the SELECT...INTO OUTFILE statement to export the records in the person table in the test database. The SQL statement and running results are as follows:

mysql> SELECT * FROM test.person INTO OUTFILE 'C://ProgramData/MySQL/MySQL Server 5.7/Uploads/person.txt';
Query OK, 5 rows affected (0.05 sec)

Then find the person.txt file according to the exported path. The file content is as follows:

1    Java 12
2    MySQL     13
3    C      15
4    C++  22
5    Python     18

The person table data is exported successfully.

Note: The following error may occur during export:

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

This is because MySQL limits the export path of data. MySQL import and export files can only be imported and exported to files in the path specified by the secure-file-priv variable.

There are the following 2 solutions:

1) First use the show variables like '%secure%'; statement to view the secure-file-priv variable configuration.

mysql> show variables like '%secure%' \G
*************************** 1. row ***************************
Variable_name: require_secure_transport
        Value: OFF
*************************** 2. row ***************************
Variable_name: secure_auth
        Value: ON
*************************** 3. row ***************************
Variable_name: secure_file_priv
        Value: C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\
3 rows in set, 1 warning (0.04 sec)

The value of secure_file_priv specifies the path to the MySQL import and export file. Modify the export file path in the SQL statement to the specified path of the variable, and then perform the import and export operations. You can also modify the value of secure-file-priv in the my.ini configuration file and then restart the service.

2) If the secure_file_priv value is NULL, export is prohibited. You can add the secure_file_priv= setting path statement in the my.ini file under the MySQL installation path, and then restart the service.

Example 2

Use the SELECT...INTO OUTFILE statement to export the records in the person table in the test database to a text file, using the FIELDS option and LINES option, Fields are required to be separated by, and character data must be enclosed in double quotes. Each record starts with -. The SQL statement is as follows:

SELECT * FROM test.person INTO OUTFILE 'C:/person.txt'
    FIELDS TERMINATED BY '\、' OPTIONALLY ENCLOSED BY '\"' LINES STARTING BY '\-'
TERMINATED BY '\r\n';

Among them:

• FIELDS TERMINATED BY ', ': indicates that the fields are separated by,;

• ENCLOSED BY '\"': Indicates that each field is enclosed in double quotes;

• LINES STARTING BY '\-': Indicates that each line starts with -;

• TERMINATED BY '\r\n' means that each line ends with a carriage return and line feed, ensuring that each record occupies one line.

person.txt file The content is as follows:

-1、"Java"、12
-2、"MySQL"、13
-3、"C"、15
-4、"C++"、22
-5、"Python"、18

You can see that each record starts with -, each data is separated by,, and all field values ​​​​are included in double quotes.

Recommended tutorial: mysql video tutorial

The above is the detailed content of How to export a table in mysql?. For more information, please follow other related articles on the PHP Chinese website!