Export result of query from mysql console to CSV file

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]

Published by

Michael Stepanov

Site owner and admin :)

2 thoughts on “Export result of query from mysql console to CSV file”

  1. Thanks for bringing back some very old memories!

    I have used this many years ago, in combination with the related LOAD DATA INFILE or LOAD DATA LOCAL INFLINE SQL statements, to do the reverse, which may look a little something like this:

    LOAD DATA LOCAL INFILE ‘/tmp/products.csv’
    INTO TABLE products
    FIELDS TERMINATED BY ‘,’
    ENCLOSED BY ‘”‘
    ESCAPED BY ‘\’
    LINES TERMINATED BY ‘\n’

Leave a Reply

Your email address will not be published. Required fields are marked *