How to export query results in MySQL

MySQL is a relational database management system that is widely used. In the data management process, query result export is a very common requirement. By exporting data, we can use other tools or programs to conduct in-depth analysis and processing of the data.

This article will introduce how to export query results in MySQL.

Preparation

Before exporting the query results, you need to ensure that the query has been executed and the results have been obtained. If you haven't executed the query yet, execute the query first.

Export results as CSV file

CSV (Comma Separated Values) file is a universal spreadsheet file format that can be easily used and opened in many different programs. You can use MySQL's own SELECT INTO OUTFILE statement to export the results to a CSV file.

The following is a sample code:

SELECT column1, column2, column3
INTO OUTFILE '/path/to/result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table_name
WHERE condition;

Explain each parameter:

• ##column1, column2, column3: Column names that need to be exported .
• '/path/to/result.csv': Exported file path and file name.
• FIELDS TERMINATED BY ',': Field separator, comma is used here.
• OPTIONALLY ENCLOSED BY '"': Field quotation mark, double quotes are used here.
• LINES TERMINATED BY '\n': Line separation character, use newline character here.
• table_name: The name of the table to be queried.
• WHERE condition: Optional query condition.

Note: The export path must be a local path on the MySQL server, not the client path. If the export path is a remote path on the MySQL server, you need to mount the path on the server.

Export results as text files

In MySQL, you can also export query results as text files. Similar to exporting CSV, you only need to change the file extension to

.txt That's it.

Here's the sample code:

SELECT column1, column2, column3
INTO OUTFILE '/path/to/result.txt'
FROM table_name
WHERE condition;

Note: When exporting a text file, you don't need to specify field and line separators because each resulting line will wrap.

Conclusion

This article explains how to export query results in MySQL. You can use the

SELECT INTO OUTFILE statement to export the results to a CSV or text file. This method can help Your efforts to improve data management and analysis.

The above is the detailed content of How to export query results in MySQL. For more information, please follow other related articles on the PHP Chinese website!