Easy way to export data from csv files in MySQL

We all know that these days we have all types of GUI tools that can help us export data out of MySQL in various formats including csv format(comma separated values).  But in some cases we need to incorporate a data export via shell scripts/sql that are part of a home cooked ETL process(also this is done using less resources).  What is the basic syntax for use to do a data export from MySQL to a csv external file. 

SELECT col1,
       col2,
       col3
FROM TABLE_NAME INTO
OUTFILE '/tmp/file.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'n';
  • you can implement various business logic in your extract sql statement.
Note: -the user who will do the export must have the FILE grant. 
GRANT FILE ON *.* TO 'user'@'host';
The output file should look something like this: 
"10199","10019","10016","diff","0"
"10208","10057","10043","diff","0"
"12583","10025","10021","change","0"
"12641","22189","13015","min","10m"
"12645","22183","13073","min","10m"
"12646","22191","13074","min","10m"
"12649","22185","13019","min","10m"
To see how to import the csv file into MySQL follow this Article.

Copyright 2020. All Rights Reserved