Exporting data in MySQL using INTO OUTFILE

In this article, we will see how to export the content of a table or query into an external file. We will rely on phpMyAdmin to export the content of a table. And we will use the SELECT INTO OUTFILE statement to export the result of a query.

First, let’s create a Contacts table with the following structure and data:

Code to create Contacts table

We can execute the following command to verify the content of the Contacts table:

Command to retrieve the Contacts table rows

Result:

Table Contacts content

MySQL select into outfile

MySQL allows us to export the result of a query to an external file. For example, let’s make a query that returns the name and phone number of the Contacts table, and we will export them to a contacts.csv file:

Script to export name and phone fields to an external file

When executing the command, the contacts.csv file will be created in the specified path. If we consult its contents we will find the names and phone numbers of the Contacts table:

Contacts.csv file content

Analyzing a bit the structure of the query, we see that the fields to be consulted are first indicated. Then the path and the name of the external file. Finally, we have to indicate the table where the data will be consulted.

There are optional sentences that can help us customize the structure of the external file. For example, we can enclose the strings in quotes, and we can separate the values with a comma.

Script to export name and phone fields to an external file with a new format

Result:

Contacts.csv file with a new format content

Exporting data with phpMyAdmin

Exporting a table to an external file is easier.

For example, if we open our database with phpMyAdmin, we just have to locate the Contacts table in the left panel and click on it so we can see its contents and options:

Contacts table in left panel

Now, there is an Export button in the top bar. This button will allow us to export the content of the table to an external file:

Top bar Export button

To export the content of the table, we just have to click on the Export button, choose the format of the external file, and press the Go button:

MySQL INTO OUTFILE options

The Custom Export method allows us to customize the external file. For example, we can change the name of the table, the name of the columns, the character we use to separate the values, to enclose the strings, among other options.

The portability of data has always been fundamental when we develop any programming project. Either to make external backups or to migrate tables or databases, data portability is essential.