How ofter were you asked by a client for a CSV (or excel) file with data from their app? I get asked that question quite often, so I wanted make the process as easy as possible. And guess what? You can create CSV files directly from MySQL with just one query!
Let’s say you want to export the id, name and email fields from your users table to a CSV file. Here is your code:
SELECT id, name, email INTO OUTFILE '/tmp/result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ‘\\’
LINES TERMINATED BY '\n'
FROM users WHERE 1
Well, if you know MySQL, you’ll know how to customize this query to spit out the the right data. Your csv file can be found in /tmp/result.csv
Make sure your MySQL server has write permissions to the location where you want to store your results file.