Sunday, September 13, 2009

Export MySQL query result to a CSV file


SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;


http://tlug.dnho.net/node/209
http://dev.mysql.com/doc/refman/4.1/en/select.html

Another example:

SELECT v.id,v.name,t.name,d.name INTO OUTFILE '/root/tr_villages.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM villages v JOIN taluqs t ON v.taluq_id=t.id JOIN districts d ON t.district_id=d.id
WHERE t.district_id IN (163,164,165,166);