MySQL Store Output to CSV

“Despite being a relational database, the MySQL engine allows for a margin of flexibility, allowing one to load and export data from various sources. One such format is CSV format.”

In this tutorial, you will learn various methods and techniques for exporting MySQL output into a CSV file.

Let’s dive in.

What is CSV?

Before discussing how to export data in CSV format, let us explore what CSV is and why it matters to us.

Comma Separated Value refers to a type of ASCII text file format where values are divided by a defined delimiter type. In most cases, each value is typically separated by a comma.

CSV is easy for machines to read as all you need is to determine the delimiter and format the data into other formats. It offers advantages such as the ability to handle large amounts of data and readability.

Create Sample Table

To illustrate how we can export data into CSV, let us create a sample table with sample data. An example query is provided below:

We can verify the table is created, and we have the sample data:

SELECT
*
FROM
employees;

Resulting table:

Select Data and Save to CSV File

We can save the output of the above command into a CSV file as shown in the command below:

SELECT
*
FROM
employees INTO OUTFILE './employees.csv';

The query above should save the results of the select statement into the specified csv file. You can also specify the absolute path available in your system.

Keep in mind that the recent MySQL version will prevent you from writing to file if the –secure-priv-file option is enabled.

You can check our tutorial on how to disable this feature.

Export Table With Column Headers

If you wish to include the column headers in the CSV file, you can run the code as:

The query above uses the union statement to organize and group the column headers into the CSV file.

Export Results Into CSV Using MySQL Dump

We can also use MySQL dump to save the table into a CSV file. The command syntax is as shown:

mysqldump -u [username] -p -t -T/path/to/directory [database] [tableName] --fields-terminated-by=','

Consider the example code below:

mysqldump -u root -p -t -T employees_table.csv tests employees --fields-terminated-by=','

Closing

In this article, we explored the various methods and techniques you can use to export a MySQL result into a CSV file.

Thanks for reading && Happy coding!!