MySQL: Exporting Data using SELECT ... INTO OUTFILE

Now it is time to export your table data into a CSV file in a local file path from the MySQL workbench.
Now how will I do that?

It is simple in this article I'll explain step by step how to export data into a CSV file using the OUTFILE clause in MySQL.

Step 1: Choose the table you want to export

For this example, I will be using the employees and offices table and will extract salary details using equijoin and export that content to a CSV file.

Here are the tables themselves.

We will use equijoin on this and extract the salary details by matching office_id from the offices' table.

select  employee_id AS EID, e.first_name, e.last_name,e.salary from employees e, offices o where e.office_id = o.office_id;

Now that is done it is time to export.

Step 2: Exporting data

Now it is very simple we will be using an extra line after this query of equijoin.

select  employee_id AS EID, e.first_name, e.last_name,e.salary from employees e, offices o where e.office_id = o.office_id into OUTFILE 'D:/salary.csv';

Now when we run this query after adding INTO OUTFILE and File path within single quotes you will face this error.

Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

To solve this we will be using the secure file path MySQL has provided.

Step 3: Secure File Path

Simply execute this query which will, in turn, display you the file path which is the default where the exported data will be stored.

SHOW VARIABLES LIKE "secure_file_priv";

Now when we execute this command it will work for sure and your table content/data will be exported to the provided path.

select  employee_id AS EID, e.first_name, e.last_name,e.salary from employees e, offices o where e.office_id = o.office_id into OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/sal.csv';