Comma Separated Values recordsdata (CSV) are a method of transferring information between functions. Databases like MySQL and spreadsheet software program like Excel help importing and exporting by CSV, so you need to use CSV recordsdata to trade information between the 2.
CSV recordsdata are plaintext, in order that they’re naturally light-weight and simple to export from MySQL.
From The Database Server
In case you have entry to the server that MySQL is working on, you possibly can export a variety with the
INTO OUTFILE command.
SELECT id, column1, column2 FROM desk INTO OUTFILE '/tmp/mysqlfiles/desk.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'n'
This may output a CSV file to
/tmp/mysqlfiles/desk.csv, or wherever you configured it to. You’ll want to ensure the person working MySQL (normally
root) has possession and write entry to the listing.
You’ll additionally want to ensure the
secure_file_priv setting permits MySQL to entry that listing. This, by default, blocks learn and write entry from SQL queries. This can be a good factor; in case your code is weak to SQL injection, any potential attacker would solely have entry to MySQL, and never the remainder of the filesystem.
You may whitelist particular directories by enhancing your MySQL config file (normally situated at
/and so on/my.cnf) to incorporate:
[mysqld] secure-file-priv = "/tmp/mysqlfiles"
Which can enable MySQL to learn and write to
/tmp/mysqlfiles/ (which you’ll must create with
mkdir). As soon as MySQL can export recordsdata, you must be capable of run the question and output CSV recordsdata.
ENCLOSED BY setting, commas might be correctly escaped, e.g.,:
"3","Escape, this","additionally, this"
Which you’ll be able to take and import immediately into any spreadsheet program or different software program.
Remember the fact that the exported CSV file doesn’t embody column headings, however the columns might be in the identical order because the
SELECT assertion. Additionally, null values might be exported as
N, which is predicted conduct, however in the event you’d like to vary this you possibly can modify the choice by wrapping
ifnull(area, "") round your fields in your
From The MySQL Command Line
When you solely have command line entry to the MySQL occasion, and never entry to the server itself (comparable to when it’s not managed by you, within the case of Amazon RDS), the issue is slightly trickier. Whereas you need to use
FIELDS TERMINATED BY ',' on the server to generate a comma-separated record, the MySQL CLI will separate with tabs by default.
Merely enter a question from the command line, and pipe it to a file:
mysql -u root -e "choose * from database;" > output.tsv
As a result of MySQL output is separated with tabs, that is known as a TSV file, for “tab-separated values,” and may go instead of your CSV file in some applications like spreadsheet imports. Nevertheless it isn’t a CSV file, and changing it to 1 is difficult.
You could possibly merely substitute every tab with a comma, which might work however would trigger it to fail if there are commas within the enter information. When you’re solely 100% sure that there aren’t any commas in your TSV file (test with
grep), you possibly can substitute the tabs with
sed "s/t/,/g" output.tsv > output.csv
However in case you have commas in your information, you’ll have to make use of a for much longer regex:
sed "s/'/'/;s/t/","/g;s/^/"/;s/$/"/;s/n//g" output.tsv > output.csv
This may correctly escape fields with citation marks, which is able to remedy the comma downside.
Notice: the tab character
t just isn’t customary. On macOS and BSD, it’s not out there, which ends up in a multitude of each lowercase “t” inflicting
sed to insert faulty commas. To unravel this, you’ll want to make use of a literal tab character instead of
sed "s/ /,/g" output.tsv > output.csv
In case your enter information accommodates tabs, you’re out of luck, and should generate a CSV file your self with a scripting language.
Do It Manually With a Actual Programming Language
MySQL (and most databases) are designed to be interacted with, so that you doubtless have some type of programming language related to MySQL already. Most languages may write to disk, so you possibly can create your personal CSV output scripts by studying fields from the database immediately, escaping them correctly, and writing a comma-delimited file.
An instance in Python.