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.
SELECT id, column1, column2 FROM table INTO OUTFILE '/tmp/mysqlfiles/table.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'n'
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
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:
[mysqld] secure-file-priv = "/tmp/mysqlfiles"
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.
ENCLOSED BY setting, commas will be properly escaped, e.g.,:
"3","Escape, this","also, this"
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
From The MySQL Command Line
If you only have command line access to the MySQL instance, and not access to the server itself (such as when it’s not managed by you, in the case of Amazon RDS), the problem is a little trickier. While you can use
FIELDS TERMINATED BY ',' on the server to generate a comma-separated list, the MySQL CLI will separate with tabs by default.
Simply enter a query from the command line, and pipe it to a file:
mysql -u root -e "select * from database;" > output.tsv
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 "s/t/,/g" output.tsv > output.csv
But if you have commas in your data, you’ll have to use a much longer regex:
sed "s/'/'/;s/t/","/g;s/^/"/;s/$/"/;s/n//g" output.tsv > output.csv
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
sed "s/ /,/g" output.tsv > output.csv
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.
- › 4 Reasons You Should Still Keep an Optical Drive Around
- › You Can Try Hundreds of ’90s PalmPilot Apps in Your Browser
- › 10 MacBook Features You Should Be Using
- › The Excellent Kasa Smart Plug Is Just $7 Right Now
- › How to Install Steam on a Chromebook
- › This Gaming PC With a Core i5 & RTX 3060 Is $950 Today