KIC

郵政公社だった頃のメモがでてきたのでうｐ

日本全国の市区町村名をデータベース化してみる。

郵政公社のWebページに行くと、住所と郵便番号の対応させたデータベースって言うのがある。これが、csv形式のデータで提供されている。

以下のページより、全国一括のファイルをダウンロード
http://www.post.japanpost.jp/zipcode/dl/oogaki.html

落としたら解凍。

CSV形式のファイルなので、そのままデータベースに投入することもできる。

ファイルの一行目はこんな感じになっている。↓
01101,"060 ","0600000","ﾎﾂｶｲﾄﾞｳ","ｻﾂﾎﾟﾛｼﾁﾕｳｵｳｸ","ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ","北海道","札幌市中央区","以下に掲載がない場合",0,0,0,0,0,0

３カラム目が郵便番号
７カラム目が都道府県名
８カラム目が市区町村名
９カラム目に細かい地名

全国の市区町村名が必要なわけだから、７と８カラム目があればOKっぽい。
群馬県にあった東村みたいに同一県内に同一名称の市町村が複数存在していることもあるかもしれないので、郵便番号を主キーにしてみる。（９カラム目は使わない）

※平成の大合併で東村はすべて廃止になりました。

就活の最中にちょちこちょこと作っていたシステムのデータベース設計を失敗したので作り直す。

データベースで扱う文字コードはsjis
リレーション名はcategory
リレーションスキーマは
category(cID,cName,cTop,cMiddle,cBottom,ruling)

で、DB作ってテーブル作るまでのSQLは以下の通り。 mysql> create database dbName character set sjis ; Query OK, 1 row affected (0.00 sec) mysql> use dbName ; Database changed mysql> create table category(cID int not null,cName char(80),cTop char (80),cMiddle char(80),cBottom char(80),ruling char(3),primary key(cID)) ; Query OK, 0 rows affected (0.00 sec) mysql> show fields from category ; +---------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+----------+------+-----+---------+-------+ | cID | int(11) | NO | PRI | | | | cName | char(80) | YES | | NULL | | | cTop | char(80) | YES | | NULL | | | cMiddle | char(80) | YES | | NULL | | | cBottom | char(80) | YES | | NULL | | | ruling | char(3) | YES | | NULL | | +---------+----------+------+-----+---------+-------+ 6 rows in set (0.00 sec)

リレーション名がカテゴリだけど、実際はカテゴリ構造とカテゴリ名とそのIDのリレーション
一応、第一正規形になってると思うのだが、このままだと様々な異状が発生するらしいので本当は高次の正規化、第三正規形までしないとだめらしい。

ちなみに、このテーブルには以下の様なクエリを使ってデータを投入する。 insert into category values(2078297648,"暗号と認証", "コンピュータテクノジー","セキュリティ","暗号と認証","小") ;

カテゴリリレーションを作るときのメモ

上記の実質カテゴリ構造＋カテゴリ名リレーションをカテゴリ構造リレーションとカテゴリ名リレーションに分割するときには、カテゴリ名が重複する可能性があるために、カテゴリ名を主キーやユニークキーにすると重複が発生し問題あり。

稼動しているMySQLサーバに外部のコンピュータより接続を試みた。しかし、接続できない。

root@localhost

すっかり、忘れていた。
立てたばかりのサーバに外部PCから接続しようと考えていたのだが、この場合サーバへ接続する際のユーザは「root@localhost」ではなく「root@172.24.25.99」になってしまう。
※172.24.25.99は外部PCのIPアドレス

立てたばかりのMySQLのデータベースmysqlのuserテーブルのuserとHostの値をみると以下のようになっていて・・・

mysql> use mysql Database changed mysql> select user,Host from user ; +------+-----------+ | user | Host | +------+-----------+ | root | localhost | +------+-----------+ 1 row in set (0.00 sec)

localhost以外からの接続はダメ
というわけで、すべての権限をもつユーザを作ってみた。

mysql> grant all privileges on *.* to -> USERNAME identified by 'PASSWORD' ; Query OK, 0 rows affected (0.14 sec) mysql> select user,Host from user ; +----------+-----------+ | user | Host | +----------+-----------+ | USERNAME | % | | root | localhost | +----------+-----------+ 2 rows in set (0.00 sec)

なにをしたいかというと、時間-時間を求めたいわけだ。

以下のような表があるとする(jikanの型はDATETIME)

idが5であるタップルの時間と現在の時間の差を求めたいとき、TIMEDIFF()という関数を使えばいい。(TIMEDIFF()関数はMySQL 4.1.1以降じゃないと使えない )

↓使用例

↑現在の時刻をnow()を使ってだして、(select jikan from t1 where id=5)でidが5の時刻を求める。んで、これらの値をtimediff()関数に入れると、計算される。

MySQLでTEXT型な属性を主キーにしようとしていたら、以下のようなメッセージが出て怒られてしまった。

BLOB型やTEXT型な属性を主キーにする場合は、キーの長さを指定してやらなくてはいけないらしい。

たとえば、属性が(ID,名前,性別,年齢)からなるテーブルを作るとする(下線が引いてあるのは主キー)主キーとなるIDの型はTEXT型。

以下のように、主キーの長さを指定してやる。

ちなみに上記のクエリだと以下のようなテーブルができる。

MySQL4.1の壁については日本MySQLユーザ会のFAQに書かれている。

MySQLのサーバのキャラクターセットがUTF-8
クライアントのキャラクターセットもUTF−8
にして、MySQL5.0.26を入れてみた。

んで、Windowsのコマンドプロンプトからサーバに接続して、マルチバイト文字を含んだクエリをしてみたら、どうも上手くいかない。どうやら、文字コードが自動で変換されてしまう上に文字化けが発生しているために上手くいかないようだ。

というわけで、MySQL4．1の壁にぶつかった。

そもそも、今回立てたMySQLサーバはJAVAで作ったアプリケーションからデータを入れたり出したりする為のもので、この出し入れが問題なければOKというはずなんだけど、Windowsのコマンドプロンプトからもデータを見たかったり、その他いくつかの環境からも見る予定だったので、どうしようかと。

とりあえず、JAVAからのデータの出し入れに関してはこのままでOK。
その他、クライアントからは、たとえばWindowsからであるならば

みたいな、コマンドを入力して一回一回文字コードを指定してやることにした。