Sure all GUI DB tools can do export of result of query into CSV file. But what if you have to do this from command line? There is a simple way perform that task. Here is an example:
SELECT *
INTO OUTFILE '/tmp/products.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM products
When you run that query all records from the table products will be dumped into file /tmp/products.csv. All fields of that dump will be delimited by comma, enclosed by double quotes and escaped by two slashes. Each row will be ended by end of the line character.
Sure you can change delimiter, enclose, escape symbols as well as add any others SQL statements such JOIN, WHERE, LIMIT ORDER etc. Enjoy!
UPDATED: Don’t forget to drop the export file before re-export data. Otherwise you’ll get the error:
ERROR 1086 (HY000): File '/tmp/products.csv' already exists
[via Electrictoolbox]