Dumping MySQL query results out to a CSV (MySQL)

Category: MySQL

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

Details

Snippet

SELECT fields FROM table
[WHERE filter]
INTO OUTFILE 'filepath'
FIELDS TERMINATED BY 'delimiter'
ENCLOSED BY 'enclosure'
LINES TERMINTED BY 'EOL'

Usage Example

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;