Export csv¶

Here is two way to export data to csv

SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

The output folder must be mysql-files by default.

(I haven’t tested the following)

mysql your_database --password=foo < my_requests.sql | sed 's/\t/,/g' > out.csv

Source

Select all then export with column name script¶

Found from https://stackoverflow.com/a/8950118

How to use it : ./exportCSV DBNAME TABLENAME

DBNAME="$1"
TABLE="$2"

FNAME=export_$(date +%Y.%m.%d)-$DBNAME_$TABLE.csv

#(1)creates empty file and sets up column names using the information_schema
mysql $DBNAME -B -e "SELECT COLUMN_NAME FROM information_schema.COLUMNS C WHERE table_name = '$TABLE';" | awk '{print $1}' | grep -iv ^COLUMN_NAME$ | sed 's/^/"/g;s/$/"/g' | tr '\n' ',' > $FNAME

#(2)appends newline to mark beginning of data vs. column titles
echo "" >> $FNAME

#(3)dumps data from DB into /var/mysql/tempfile.csv
mysql $DBNAME -B -e "SELECT * INTO OUTFILE '/var/lib/mysql-files/tempfile.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' FROM $TABLE;"

#(4)merges data file and file w/ column names
cat /var/lib/mysql-files/tempfile.csv >> $FNAME

#(5)deletes tempfile
rm -rf /var/lib/mysql-files/tempfile.csv