Export CSV directly from MySQL

First another blog post about this is here:

• http://ariejan.net/2008/11/27/export-csv-directly-from-mysql/

 But since I saw this posted on the forums.mysql.com I thought I would give a little longer example.

So for this example I am using the World database. It is available free to download here:

• http://dev.mysql.com/doc/index-other.html


mysql>desc City;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+



mysql> SELECT ID, Name, CountryCode , District , Population
 INTO OUTFILE '/tmp/City_data.csv'
 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 ESCAPED BY '\\'
 LINES TERMINATED BY '\n'
 FROM City ;
Query OK, 4079 rows affected (0.04 sec)



# more /tmp/City_data.csv
1,"Kabul","AFG","Kabol",1780000
2,"Qandahar","AFG","Qandahar",237500
3,"Herat","AFG","Herat",186800
4,"Mazar-e-Sharif","AFG","Balkh",127800
5,"Amsterdam","NLD","Noord-Holland",731200
6,"Rotterdam","NLD","Zuid-Holland",593321
7,"Haag","NLD","Zuid-Holland",440900
8,"Utrecht","NLD","Utrecht",234323
9,"Eindhoven","NLD","Noord-Brabant",201843