MySQL is a popular open source enterprise ready database. There are many tools available for interacting with this database, but as with most enterprise databases most access happens via Structured Query Language. There are a few options to export data to CSV from MySQL. Some require third part tools, while another uses a command line tool (useful on Unix machines) and a final option via SQL. Lets take a look at the last two options:
MySQL command line tool (on Unix):
mysql -u exampleuser -p letmein exampledb -B -e "select * from \'person\';" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csv
So what does all of that mean?
- mysql : the executable file (should be where MySQL is installed)
- -u exampleuser : a switch and option for the username to execute the SQL with
- -p letmein : a switch and option for the password of the user
- exampledb : the database to run the SQL against
- -B : instructs that the output should be tab delimited (we will convert this to commas later in the command)
- -e “the sql statement here” : the SQL statement to run returning your data
- | sed ’s/\t/”,”/g;s/^/”/;s/$/”/;s/\n//g’ : sed is a Unix stream processor, essentially allow for transformations in this case. Here we have four sed commands that change the tabs to double quotes, adds double quotes to the beginning and end of each line and adds a new line marker at the end of each line.
- > filename.csv : outputs the results to the file named filename.csv
MySQL SQL Syntax:
SELECT column_a,column_b,column_c+column_d INTO OUTFILE '/tmp/result.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table;
The MySQL select statement is using an INTO OUTFILE command to output the results of the SQL select into a file (in this case /tmp/result.csv).
A couple of notes:
- In both cases the file is saved to a location local to where the database is being ran from (on the same machine, or to a mount/drive available to the machine).
- The header row will need to be added to both files (which can be done using any text editor or spreadsheet program).
Postgres:
Postgres is another popular open source enterprise ready database. Similar in nature to MySQL, there are many third party tools (including PGAdmin from Postgres) for managing and administering the database. The best bet for exporting CSV data from Postgres is by using a Postgres specific SQL command called COPY:
COPY (SELECT column_a,column_b FROM whatever) TO '/tmp/dump.csv' WITH CSV HEADER
This command is extremely fast and produces a properly formatted CSV file including the headers.
留言列表
Banana-Pi, 樹梅派應用 
