Comma Separated Value Files (CSV) they are a way to transfer data between applications. Databases like MySQL and spreadsheet software like Excel support import and export via CSV, so you can use CSV files to exchange data between the two.
CSV files are plain text, so they are naturally lightweight and easy to export from MySQL.
From the database server
If you have access to the server on which MySQL is running, 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 generate a CSV file to /tmp/mysqlfiles/table.csv, or where you have configured it. You will need to make sure that the user running MySQL (generally mysql O root) has ownership and write access to the directory.
You will also need to make sure that secure_file_priv Configuration enables MySQL to access that directory. This, by default, blocks read and write access to 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 configuration file (generally located in /etc/my.cnf) to include:
[mysqld] secure-file-priv = "/tmp/mysqlfiles"
Which will allow MySQL to read and write to /tmp/mysqlfiles/ (what should you create with mkdir). Once MySQL can export files, should be able to run the query and generate CSV files.
With the ENCLOSED BY setting, commas will be correctly escaped, as an example:
"3","Escape, this","also, this"
That you can take and import directly into any spreadsheet program or other software.
Please note that the exported CSV file does not include column headers, but the columns will be in the same order as the SELECT statement. At the same time, null values will be exported as N, what is the expected behavior, but if you want to change this, you can modify the selection by wrapping ifnull(field, "") around their fields in their SELECT statement.
From the MySQL command line
If you only have command line access to the MySQL instance, and you don't have access to the server itself (like when you don't manage it, in the case of Amazon RDS), the problem is a bit more complex. While you can use FIELDS TERMINATED BY ',' on the server to generate a comma separated list, MySQL CLI will be tabbed by default.
Just enter a query from the command line and pipe it to a file:
mysql -u root -e "select * from database;" > output.tsv
Because the MySQL output is tabbed, this is called a TSV file, for “tab-separated values”, and it may work instead of your CSV file in some programs, like spreadsheet imports. But it is not a CSV file and converting it to one is tricky.
You could just replace each tab with a comma, which would work but cause it to fail if there are commas in the input data. If you are absolutely sure that there are no commas in your TSV file (consult with grep), you can replace the tabs with sed:
sed "s/t/,/g" output.tsv > output.csv
But if you have commas in your data, you will have to use a much longer regular phrase:
sed "s/'/'/;s/t/","/g;s/^/"/;s/$/"/;s/n//g" output.tsv > output.csv
This will correctly escape the quoted fields, what will solve the problem of commas.
Note: the tab character t it is non-standard. On macOS and BSD, is not enabled, which generates a disorder of each “t” lowercase causing sed to insert wrong commas. To solve this, you must use a literal tab character instead of t:
sed "s/ /,/g" output.tsv > output.csv
If your input data contains tabs, you are 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 interact with him, so you probably already have some kind of programming language connected to MySQL. Most languages can also write to disk, so you can create your own CSV output scripts by reading the database fields directly, escaping them correctly and writing a comma delimited file.
