Comma Separated Values files (CSV) are a way of transferring data between applications. Databases like MySQL and spreadsheet software like Excel support importing and exporting through CSV, so you can use CSV files to exchange data between the two.
CSV files are plaintext, so they’re naturally lightweight and easy to export from MySQL.
From The Database Server
If you have access to the server that MySQL is running on, you can export a selection with the INTO OUTFILE command.
This will output a CSV file to /tmp/mysqlfiles/table.csv, or wherever you configured it to. You’ll need to make sure the user running MySQL (usually mysql or root) has ownership and write access to the directory.
You’ll also need to make sure the secure_file_priv setting allows MySQL to access that directory. This, by default, blocks read and write access from SQL queries. This is a good thing; if your code is vulnerable to SQL injection, any potential attacker would only have access to MySQL, and not the rest of the filesystem.
You can whitelist specific directories by editing your MySQL config file (usually located at /etc/my.cnf) to include:
Which will allow MySQL to read and write to /tmp/mysqlfiles/ (which you’ll have to create with mkdir). Once MySQL can export files, you should be able to run the query and output CSV files.
With the ENCLOSED BY setting, commas will be properly escaped, e.g.,:
Which you can take and import directly into any spreadsheet program or other software.
Keep in mind that the exported CSV file doesn’t include column headings, but the columns will be in the same order as the SELECT statement. Also, null values will be exported as N, which is expected behavior, but if you’d like to change this you can modify the selection by wrapping ifnull(field, “") around your fields in your SELECT statement.
From The MySQL Command Line
Simply enter a query from the command line, and pipe it to a file:
Because MySQL output is separated with tabs, this is called a TSV file, for “tab-separated values,” and may work in place of your CSV file in some programs like spreadsheet imports. But it isn’t a CSV file, and converting it to one is complicated.
You could simply replace each tab with a comma, which would work but would cause it to fail if there are commas in the input data. If you’re entirely 100% certain that there are no commas in your TSV file (check with grep), you can replace the tabs with sed:
But if you have commas in your data, you’ll have to use a much longer regex:
This will properly escape fields with quotation marks, which will solve the comma problem.
Note: the tab character t is not standard. On macOS and BSD, it’s not available, which leads to a mess of every lowercase “t” causing sed to insert erroneous commas. To solve this, you’ll need to use a literal tab character in place of t:
If your input data contains tabs, you’re out of luck, and will have to generate a CSV file yourself with a scripting language.
Do It Manually With a Real Programming Language
MySQL (and most databases) are designed to be interacted with, so you likely have some sort of programming language connected to MySQL already. Most languages can also write to disk, so you can create your own CSV output scripts by reading fields from the database directly, escaping them properly, and writing a comma-delimited file.
An example in Python.
