MySQL – Export the Resultset to CSV file

In SQL Server, you can use BCP command to export the result set to a csv file. In MySQL too, You can export data from a table or result set as a csv file in many methods. Here are two methods. Both let you export a resultset to CSV in a few steps.

Method 1 : Make use of Work Bench

If you are using Work Bench as a querying tool, you can make use of it’s Export option in the result window. Run the following code in Work Bench

SELECT db_names FROM mysql_testing;

The result will be shown in the result windows. There is an option called “File”. Click on it and it will prompt you a window to save the resultset to CSV or another format (Screen shot attached to show how file option can be used). Choose the directory and type out the name of the file.

Method 2: Export the Resultset to CSV With the OUTFILE Command

You can do the export using a query with OUTFILE command as shown below

SELECT db_names FROM mysql_testing
INTO OUTFILE 'C:/testing.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

After the execution of the above code, you can find a file named testing.csv in C drive of the server.

Things to Know About INTO OUTFILE

INTO OUTFILE writes the file on the database server, not on your own computer, so the C drive in this example is the drive of the server. Your account needs the FILE privilege, and the secure_file_priv setting decides which folder MySQL is allowed to write to. Run SHOW VARIABLES LIKE ‘secure_file_priv’; to see it. MySQL also refuses to overwrite a file that already exists, so pick a new name each time or delete the old file first. If you need the file on your own machine, the Workbench export in Method 1 is the easier route.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.