從MySQL直接導出CSV

Original post: http://anothermysqldba.blogspot.com/2013/07/export-csv-directly-from-mysql.html

首先這是另一篇博客在這裡：

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

但是，因為我看到這個上forums.mysql.com發布，我想我會給長一點的例子。

因此，在這個例子中我使用的是世界數據庫。 它是可供免費下載，在這裡：

• 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