Sometimes, you want to query a database and dump the result out to a CSV so that you can process the results with simple text tools like awk and sed.
This post shows you how to tell MySQL to write the results of a query out to a named file in CSV format, as well as how to deal with an increasingly common error response when secure files is enabled
SELECT fields FROM table [WHERE filter] INTO OUTFILE 'filepath' FIELDS TERMINATED BY 'delimiter' ENCLOSED BY 'enclosure' LINES TERMINTED BY 'EOL'
SELECT id,name,address FROM people WHERE active=1 INTO OUTFILE '/var/lib/mysql-files/people.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINTED BY '\n' -- Or for the same query, but a tab delimited CSV SELECT id,name,address FROM people WHERE active=1 INTO OUTFILE '/var/lib/mysql-files/people.csv' FIELDS TERMINATED BY '\t' ENCLOSED BY '"' LINES TERMINTED BY '\n' -- If you receive the error message -- The MySQL server is running with the --secure-file-priv option so it cannot execute this statement -- It means that you're trying to write the CSV file into a directory that MySQL isn't configured -- to write into. You can check valid directories with one of the following commands (depending on MySQL version) SHOW VARIABLES LIKE "secure_file_priv"; SELECT @@global.secure_file_priv;