Saturday, July 13, 2013

のMySQLから直接CSVをエクスポート

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

最初にこのことについて別のブログ記事はここにある:
しかし、以来、私は、これはに掲載見forums.mysql.com私はもう少し例を与えるだろうと思った。

したがって、この例のために私は世界のデータベースを使用しています。 ここからダウンロードして自由に利用可能です:

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