Jun 10, 2022 by Robert Gravelle

A CSV is a Comma-Separated Values file, which allows data to be saved in a tabular format. It's long been the preferred format for transferring data between databases. More recently, Internet-driven formats such as XML and JSON have also gained much traction. CSV files are well suited to databases because they represent table data exceptionally well and can be used with just about any spreadsheet program, such as Microsoft Excel or Google Spreadsheets. In today's blog, we'll be taking a look at a few ways to export table data to CSV in MySQL.

Using the Command Line

Most relational databases, MySQL included, provide commands to export and import to and from CSV.

Make sure that you start your MySQL server instance with the –secure-file-priv option. It sets the directory where MySQL imports and exports data using statements such as LOAD DATA and SELECT INTO FILE. You can see the current setting using the command:

SHOW VARIABLES LIKE "secure_file_priv"  

All that's left to do now is select the data and specify the location of the output file. Here's a statement that outputs an entire table:

TABLE tableName 
INTO OUTFILE 'path/outputFile.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ''
LINES TERMINATED BY '\n';

You can also filter the data as you would in any SELECT query. Here's an example that filters both columns and values:

SELECT columnName, ...
FROM tableName
WHERE columnName = 'value'
LIMIT 1000
INTO OUTFILE 'path/outputFile.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ''
LINES TERMINATED BY '\n';

Want to include column headers? That's easily done using the UNION statement:

(SELECT 'columnHeading', ...)
UNION
(SELECT column, ...
FROM tableName
INTO OUTFILE 'path-to-file/outputFile.csv’'
FIELDS ENCLOSED BY '"' 
TERMINATED BY ','
ESCAPED BY '"'
LINES TERMINATED BY '\n')

Using mysqldump

mysqldump is a command line utility provided by MySQL for exporting tables, databases, and entire servers. Moreover, it can also be utilized for backup and recovery. Issue the following command in a command prompt/terminal to export a table:

mysqldump -u [username] -p -t -T/path/to/directory [database] [tableName] --fields-terminated-by=,

Using Navicat's Export Wizard

Navicat 16 for MySQL comes with a very powerful export (and import) wizard, which can export data in multiple formats, including .xlsx, .json, and .sql. To start the export wizard, select the corresponding table, right-click – > Export Wizard, and select the format:

You can choose to export one table, the entire database, or anything in between:

You can also select exactly which fields you want, if you're not interested in all the columns:

Navicat supports a wealth of options, such as including headers, delimiters, error handlers, and more:

Conclusion

CSV is not the perfect format, and does have limitations. For example, you cannot save data types or formulas in this format. That being said, CSV is still a very important data transfer format; one that every DBA should be familiar with.