How to export query results in mysql

MySQL is a free, open source relational database management system. During development or use, we usually need to export query results. This article will introduce how to export query results in MySQL.

1. Export to file

In MySQL, we can use the SELECT INTO OUTFILE command to export query results to a file. The syntax is as follows:

SELECT column_name(s)
INTO OUTFILE 'file_path'
FROM table_name
[WHERE condition];

Among them, column_name(s) is the column name to be queried, and file_path is the absolute path and file name of the exported file. If the file does not exist, a new file will be automatically generated. table_name is the name of the table to be queried, and condition is an optional query condition.

For example, if we want to export all records of the name and age fields in the students table, we can use the following command:

SELECT name, age
INTO OUTFILE '/usr/local/mysql/data/student.txt'
FROM students;

The above command will export the query results to /usr/local/mysql/data /student.txt file, separated by tabs.

2. Export to CSV file

If the file we need to export is a comma-delimited CSV file, we can use the following command:

SELECT column_name(s)
FROM table_name
WHERE condition
INTO OUTFILE 'file_path'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Where, FIELDS TERMINATED BY ',' means using commas to separate fields, ENCLOSED BY '"' means using double quotes to include fields, LINES TERMINATED BY '\n' means using newlines to separate lines.

For example, we want to export students All records in the table are stored in the /usr/local/mysql/data/student.csv file. You can use the following command:

SELECT *
FROM students
INTO OUTFILE '/usr/local/mysql/data/student.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

3. Export to Excel file

Except In addition to exporting to text files, MySQL also supports exporting query results to Excel files. To achieve this function, we need to use MySQL's external tools such as mysqldump or navicat.

For example, we can use the navicat tool to export the query Export the results to an Excel file:

Select the data table to be exported, and select Tools > Data Transfer in the menu bar, as shown in the figure:

In the pop-up Data Transfer window, select the exported data table and output format (Excel format), as shown in the figure:

Click the Start button and select the location of the export file. and file name, you can export the query results to an Excel file.

Summary

MySQL provides a variety of ways to export query results, and we can choose the appropriate export method according to our needs. Use SELECT The INTO OUTFILE command is the most basic way to export query results to a file. By specifying parameters such as delimiters and line end characters, we can format the export results into text, CSV or Excel files.

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!