Export MySQL query result to CSV

This is just a simple example of how to save the result of MySQL query into CSV file. At the bottom of the post there's an example for the opposite process - import from a CSV file.

SELECT * FROM users 
INTO OUTFILE '/tmp/exptest.csv' # the path and the name of the CSV file
FIELDS TERMINATED BY ',' # The colums separator. Usually comma(, - eu standart) or semicolon(; - us standart)
ENCLOSED BY '"' # The column content wrapper
LINES TERMINATED BY '\n'; # New line symbol. Usualy "\n" on Unix or "\r\n" on Windows

Some time ago I had a problem with an export from a table where the database encoding was latin1 and the inserted data was utf8 encoded so the soulution was to convert the selected data do the encoding of the table:

SELECT CAST(`first_name` AS CHAR CHARACTER SET latin1), CAST(`family_name` AS CHAR CHARACTER SET latin1) FROM `users`;

Import from CSV file via terminal:

mysqlimport --ignore-lines=1 --fields-terminated-by=, --fields-optionally-enclosed-by='\"' --local -u mysqluser -p /tmp/example.csv

Note that the first line is ignored. This is useful when the CSV file contains header line. Import from CSV file via query:

LOAD DATA INFILE '/path/to/file.csv' 
INTO TABLE forms 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Skip the last line to import the first line.