sábado, 13 de julio de 2013

Exportar CSV directamente desde MySQL

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

Primera otro post sobre esto aquí:
Pero desde que vi esta publicado en el forums.mysql.com pensé que iba a dar un ejemplo poco más.

Así que para este ejemplo estoy usando la base de datos mundial. Está disponible para descargar gratis aquí:

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