MySQL Export file to CSV

To use a ResultSet across applications or to share a ResultSet between different developers/users, MySQL Export to CSV is the preferred way. By using this option, we can write the ResultSet to a CSV file which is easy to edit, parse and import from if needed.

MySQL Output to CSV – Example

Consider we have a table “Student”:

Student Table

Let’s start from the simplest. Export all rows from the “Student” table to “export1.csv”:

MySQL Select to CSV – “Student” table to “export1.csv”

ResultSet:

export1.csv

Do note that the .csv file does not have column names. MySQL export does not write column names by default. So, we have to select the column names along with the ResultSet to write in the exported file.

MySQL Select to CSV – with column names

ResultSet:

export2.csv

MySQL Export to CSV with FIELDS TERMINATED BY

In the exported file, the separation between columns is marked with the TAB character by default. We can change this using “FIELDS TERMINATED BY” clause as shown below:

MySQL Export to CSV with FIELDS TERMINATED BY

ResultSet:

export3.csv

Since the separation between fields is marked by “,”, any comma(“,”) inside the values are escaped with a prefix “\”.

MySQL Export to CSV with ENCLOSED BY

“ENCLOSED BY” is another fruitful option which enables enclosing the whole value with a mentioned character.

MySQL Export to CSV with ENCLOSED BY

ResultSet:

export4.csv

Since the column values are enclosed now, there is no need to prefix escape character before “,” (comma). But, if the enclosing character itself is present within the values, then that should be escaped.

Similarly, the escaping character itself can be changed by using “ESCAPED BY” clause. The termination of each row by default is marked with “\n”, which can also be changed using “LINES TERMINATED BY” clause.MySQL Workbench – Querychat

MySQL Export to CSV – Example with all options

Let’s look at an example where all the discussed options are utilized:

MySQL Export to CSV with all options

ResultSet:

export5.csv