郵政省の郵便番号を取り込みたい(MySQL)

MySQLは使い出したばっかりです。
いろいろなサイトを参考に、
データベース、テーブル、フィールドの作成、SELECT、UPDATE文等は、一通り使ってみたのですが、

大きなデータベースから検索等をしたいと思い、郵政省のダウンロードしたCSVからMySQLにインポートしたいと思います。

http://www.post.japanpost.jp/zipcode/download.html

13104,"160 ","1600005","トウキョウト","シンジュクク","アイズミチョウ","東京都","新宿区","愛住町",0,0,0,0,0,0
のように、ダブルクォートで囲まれており、

CSVファイル内には、フィールド名はありません。
フィールドに関しては、
http://www.post.japanpost.jp/zipcode/dl/readme.h …
に掲載されています。

これをインポートして、Mysqlのtestデータベースの、zip_codeというテーブルを作ろうと思います。

この場合、どういった順序で作業をすれば、データベースにすることができますでしょうか？
コマンドラインで行おうと思います。

フィールドの定義は先に行っていなければならないのでしょうか？フィールドの定義が終わってから、なにかしらのコマンドで、データベースに流し込むことができるのでしょうか？

No.5 回答者： locoyama 回答日時： 2005/03/09 23:26

＞大阪府が文字化けしています。

私も、この症状が出たのですが、設定ファイルをいじったり、何度かmysqlをインストール
している内に、よく分からないのですが直ってしまいました。
一度mysqlの文字コードをお使いのシステムの文字コードに設定ファイルで変更してみてはどうでしょうか？
（例）
[mysqld]
default-character-set=sjis（ご使用になられてる文字コード）
[mysql]
default-character-set=sjis（ご使用になられてる文字コード）
あまり、まともな答えでなくてすみませんです。

この回答へのお礼

回答ありがとうございます。
無事に望み通りの出力ができました。

どれが解決策になったかはわかりませんが、my.cnfで漢字コードを明示的にujisにしたこと、

また、nkfではなく、テキストエディタでeucにして保存したこと等で、

無事に文字化けすることなく、出力することができました。locoyamaさんのおかげで、Mysql、データベースがとてもおもしろいものになりました。

これからはPHPによる、ブラウザをインターフェースとした、データベースを趣味の範囲で作ってみたいと思います。

長い間おつき合いいただきまして、ありがとうございました。

お礼日時：2005/03/10 09:44

No.4 ベストアンサー 回答者： locoyama 回答日時： 2005/03/09 21:02

テーブルの構造を見てみると、zip_codeテーブルのＩＤがユニークキーになっているようですが、

それに対応する、ＣＳＶのフィールドは
全国地方公共団体コード(JIS X0401、X0402)………　半角数字
でしょうか？
もしそうなっているのであれば、そのデータに重複したデータが含まれてませんでしょうか？
フィールドのユニークキーで違反が発生した場合、deleteされてしまいます。

この回答への補足

何度もありがとうございます、本当に助かります。
結論から申し上げると、zip_codeテーブルのIDフィールドから主キーを削除すると全て取り込まれました。

よくよく見ると、重複した数値ばかりでした。ここがユニークなものだと思って、主キーを設定していました。

無事に12万近くのレコードが取り込まれて、郵便番号から住所が0.2秒で割り出されるのに、ちょっと感激しているのですが、

mysql> select 7zip,todou_huken_kanji, shiku_chouson_kanji,choiki_kanji from zip_
code where 7zip=5300001;
+---------+-------------------+---------------------+--------------+
| 7zip | todou_huken_kanji | shiku_chouson_kanji | choiki_kanji |
+---------+-------------------+---------------------+--------------+
| 5300001 | 大阪 復 大阪市北区 | 梅田 |
+---------+-------------------+---------------------+--------------+
1 row in set (0.29 sec)

のように、大阪府が文字化けしています。

また、漢字を含んだ文字列から、データベースを検索してもヒットしません。

mysql> select * from zip_code where todou_huken_kanji='大阪府';
Empty set (0.25 sec)

mysql> select * from zip_code where todou_huken_kanji='東京都';
Empty set (0.25 sec)

mysql> select * from zip_code where todou_huken_kanji='北海道';
Empty set (0.25 sec)

ダウンロードしたCSVは、シフトJISが使われており、nkfによって、EUCに変換したものをインポートしました。

/etc/my.cnf（設定ファイル）には特に漢字コードのようなものは書かれていないようです。

もう少しなのですが、漢字コードの取り扱いでマズイ点があるのでしょうか？

もう少しお付き合い頂ければ幸いです。

補足日時：2005/03/09 21:56

No.3 回答者： locoyama 回答日時： 2005/03/09 18:48

何度もすみません。

No2の補足です。
[root@dell ~]# mysqlimport -v -u root -r --fields-terminated-by=, zip zip_code.csv
zip_code.csvの部分を、絶対パスで入力してもだめですか？
私の場合、-Lのオプションを付けずに、以下でインポート出来たものですから・・・
C:\mysql\bin\mysqlimport -u root -r --fields-terminated-by=, test C:\zip_code.csv

No.2 回答者： locoyama 回答日時： 2005/03/09 18:37

検索してみた所、以下のような物が出てきました。

http://www.mysql.gr.jp/mysqlml/mysql/msg/4161
マニュアルによりますと・・・
-L, --local
クライアントから入力ファイルを読み取る。デフォルトでは、localhost（デフォルトホスト）に接続した場合、テキストファイルはサーバにあると想定される。
という事らしいのですが、試してみてください。

参考URL：http://www.mysql.gr.jp/mysqlml/mysql/msg/4161

この回答への補足

-Lオプションをつけることでインポートすることができました。そのときのコマンドラインは

mysqlimport -L -v -u root -r --fields-terminated-by=',' --fields-enclosed-by='"' zip zip_code.csv.euc
です。

結果は
Connecting to localhost
Selecting database zip
Loading data from LOCAL file: zip_code.csv.euc into zip_code
zip.zip_code: Records: 121155 Deleted: 118607 Skipped: 0 Warnings: 0
Disconnecting from localhost

と、上の数値からわかるように、2548のレコードしかデータベース入っていません。

mysql> select * from zip_code;
＜省略＞
2548 rows in set (0.02 sec)

流れていく都道府県を見ていると、どの都道府県も入っているように見えるのですが、
この98％の欠落している理由がわかりません。

mysql> select todou_huken_kanji,shiku_chouson_kanji,choiki_kanji from zip_code where 7zip=9071801;

+-------------------+------------------------+--------------+
| todou_huken_kanji | shiku_chouson_kanji | choiki_kanji |
+-------------------+------------------------+--------------+
| 沖縄県 | 八重山郡与那国町 | 与那国 |
+-------------------+------------------------+--------------+
1 row in set (0.01 sec)

と、登録されているデータに関しては問題ありません。

フィールドに問題があれば、warningやskipに数が入ると思うのですが、なぜだかおわかりになるでしょうか？

補足日時：2005/03/09 20:45

No.1 回答者： locoyama 回答日時： 2005/03/09 06:17

ＣＳＶファイルにフィールド名は必要有りません。

まず、testデータベースへzip_codeテーブルを作成します。
この時にインポートするＣＳＶのフィールド数と合わせておきます。
次に、mysqlimport.exeを使用して、ＣＳＶファイルの内容を取り込みます。
コマンドプロンプトに以下のように打ち込みます。（お使いの環境によって任意に変更して下さい）
C:\mysql\bin\mysqlimport -u root -r --fields-terminated-by=, test C:\zip_code.csv
注意なのですが、インポートするファイルの拡張子（.csv）の前の
ファイル名はテーブル名と同じにしなければなりません。
がんばって下さい。

この回答への補足

インポートを試みました。

zipデータベースに、zip_codeテーブルを作りました。
CSVファイルには15個のフィールドがあるので、

mysql> show fields from zip_code;
+---------------------+------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+------------------------+------+-----+---------+-------+
| id | mediumint(10) unsigned | | PRI | 0 | |
| 5zip | varchar(5) | YES | | NULL | |
| 7zip | varchar(7) | YES | | NULL | |
| todou_huken_kana | text | YES | | NULL | |
| shiku_chouson_kana | text | YES | | NULL | |
| choiki_kana | text | YES | | NULL | |
| todou_huken_kanji | text | YES | | NULL | |
| shiku_chouson_kanji | text | YES | | NULL | |
| choiki_kanji | text | YES | | NULL | |
| etc1 | tinyint(1) unsigned | YES | | NULL | |
| etc2 | tinyint(1) unsigned | YES | | NULL | |
| etc3 | tinyint(1) unsigned | YES | | NULL | |
| etc4 | tinyint(1) unsigned | YES | | NULL | |
| etc5 | tinyint(1) unsigned | YES | | NULL | |
| etc6 | tinyint(1) unsigned | YES | | NULL | |
+---------------------+------------------------+------+-----+---------+-------+
と15個のフィールドを作成しました。

[root@dell ~]# cd /root
[root@dell ~]# mysqlimport -v -u root -r --fields-terminated-by=, zip zip_code.csv
Connecting to localhost
Selecting database zip
Loading data from SERVER file: /root/zip_code.csv into zip_code
mysqlimport: Error: Can't get stat of '/root/zip_code.csv' (Errcode: 13), when using table: zip_code

と、詳細モードでインポートしてみたのですが、
データベース名、テーブル名まではあっていると思うのですが、その先でつまづいているようです。

補足情報としては
[root@dell ~]# file zip_code.csv
zip_code.csv: Non-ISO extended-ASCII text, with CRLF, NEL line terminators

[root@dell ~]# ls -l | grep zip_code.csv
-rwxr-xr-x 1 root root 14086750 3月 9 17:44 zip_code.csv

です。

mysqlのユーザ名rootには、パスワードは設定していません。よろしくお願い致します。

補足日時：2005/03/09 18:04