Export data from MySQL to CSV file using SQL query- SQL Hacks

Export data from MySQL to CSV

Sometimes it's required to store the data from your database table to our local machine. To do this operation we can use phpmyadmin export feature too, but what if you want to get the data from the database to your CSV file using MySQL command line. In this blog post, I will explain how to do it just by writing one query.

But before starting that make sure that you have set your global 'local_infile'=on, if not then please visit this link to know about it-https://codingclown.blogspot.com/2020/06/import-data-from-csv-file-to-mysql.html.

Step 1: Select your database where the table is stored from where you want to copy the data.

Step 2: Check whether the table is not empty using the command-

             

             Run the command: Select * from table_name;

Step 3: Type this command to copy the data from your table to your local machine.

           

Run the command:    select * into outfile "C:/ProgramData/MySQL/MySQL Server        8.0/Uploads/Book1.csv" fields terminated by "," optionally enclosed by " ' " lines                terminated by  "\n" from table_name;

Here Book1.csv is my CSV file, make sure there is no file in this location with the same name.