MySQL道普請便り

第2回　MySQLにはじめてのデータを入れてみる

MySQL, SQL, 郵便番号

この記事を読むのに必要な時間：およそ 6.5 分

サンプルデータその3，KEN_ALL.CSV

みなさんはKEN_ALL.CSVというファイルをご存知でしょうか。日本郵便株式会社が公開している，CSV形式の日本全国の郵便番号が記録されたファイルです。好き嫌いはあるかも知れませんが，今度はこのファイルをMySQLのテーブルに読み込ませてみたいと思います。

KEN_ALL.CSVはこちらのページからダウンロードできます。「⁠都道府県一覧」の「全国一括」というものがそれです。

図1 ダウンロードページ

図1 ダウンロードページ

$ wget http://www.post.japanpost.jp/zipcode/dl/kogaki/zip/ken_all.zip
$ unzip ken_all.zip
$ iconv -f Shift_JIS -t UTF-8 KEN_ALL.CSV > /tmp/KEN_ALL_UTF8.CSV

このファイルは非常に残念ながら文字コードがShift_JISであり，半角カナも使われている素敵仕様ですので，iconvコマンドを使用して文字コードをUTF-8に変更したものを作っておきます。半角カナの有無に関わらず，日本語が含まれている場合は文字コードはUTF-8を推奨します（MySQL上では文字コード名がutf8になります，また，utf8の拡張であるutf8mb4のほうが更にお勧めです⁠）⁠。

mysql> CREATE DATABASE zipcode CHARACTER SET utf8mb4;
mysql> CREATE TABLE zipcode.zipcode(
    ->   code varchar(12) NOT NULL,
    ->   old_zipcode varchar(5) NOT NULL,
    ->   zip_code varchar(7) NOT NULL,
    ->   prefecture_kana varchar(255) NOT NULL,
    ->   city_kana varchar(255) NOT NULL,
    ->   town_kana varchar(255) NOT NULL,
    ->   prefecture varchar(128) NOT NULL,
    ->   city varchar(128) NOT NULL,
    ->   town varchar(128) NOT NULL
    -> ) DEFAULT CHARACTER SET= utf8mb4;

データの意味は郵便番号データの説明 - 日本郵便に記載されています。とりあえず，10カラム目以降は見ていて面白いサンプルデータとは言えないので省略してしまいます。

mysql> SET character_set_server= utf8mb4;
mysql> SET NAMES utf8mb4;
mysql> use zipcode
mysql> LOAD DATA INFILE '/tmp/KEN_ALL_UTF8.CSV' INTO TABLE zipcode.zipcode FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (code, old_zipcode, zip_code, prefecture_kana, city_kana, town_kana, prefecture, city, town, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy);
Query OK, 123823 rows affected (1.54 sec)
Records: 123823  Deleted: 0  Skipped: 0  Warnings: 0

2つのSETステートメントは，サーバサイドの文字コードとクライアントサイドの文字コードを合わせています。my.cnfなどで全てきれいに統一されていればいいのですが，ここではyumコマンドでインストールしたまま何の編集もしていないため，データベースとテーブルをutf8mb4で作成したにも関わらず，これらの設定を通さないとUTF-8の文字が化けてしまいます。クライアント側，サーバ側，データベース，テーブル，4つの文字コードは原則同じものに揃えましょう。

テーブルのカラム数とCSVファイルのカラム数が合わなくなったため，LOAD DATA INFILEステートメントの中でカラム数の差異を吸収しています（CSVにだけ存在する10～15カラム目は@dummyというユーザー変数に格納され，テーブルにはロードされません。この記述をしない場合，カラムの数が合わないという内容のワーニングまたはエラーが表示されます⁠）⁠。これもこんな構文を憶える必要は全くないと思いますので，そういうもんだと思ってデータをロードしてあげてください。

このCSVファイルに触ったことのある方はご存知かと思いますが，このデータは全く正規化されておらず，テーブル分割の練習をするのにとても丁度良いサンプルになります。

mysql> SELECT zip_code, prefecture, prefecture_kana, city, city_kana, town, town_kana FROM zipcode.zipcode ORDER BY zip_code LIMIT 3;
+----------+------------+-----------------------+-----------------+-----------------------------+--------------------------------------+-----------------------------------------------+
| zip_code | prefecture | prefecture_kana       | city            | city_kana                   | town                                 | town_kana                                     |
+----------+------------+-----------------------+-----------------+-----------------------------+--------------------------------------+-----------------------------------------------+
| 0010000  | 北海道     | ﾎｯｶｲﾄﾞｳ               | 札幌市北区      | ｻｯﾎﾟﾛｼｷﾀｸ                   | 以下に掲載がない場合                 | ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ                               |
| 0010010  | 北海道     | ﾎｯｶｲﾄﾞｳ               | 札幌市北区      | ｻｯﾎﾟﾛｼｷﾀｸ                   | 北十条西（１～４丁目）               | ｷﾀ10ｼﾞｮｳﾆｼ(1-4ﾁｮｳﾒ)                           |
| 0010011  | 北海道     | ﾎｯｶｲﾄﾞｳ               | 札幌市北区      | ｻｯﾎﾟﾛｼｷﾀｸ                   | 北十一条西（１～４丁目）             | ｷﾀ11ｼﾞｮｳﾆｼ(1-4ﾁｮｳﾒ)                           |
+----------+------------+-----------------------+-----------------+-----------------------------+--------------------------------------+-----------------------------------------------+

mysql> CREATE TABLE zipcode.prefecture_kana(
    ->   prefecture varchar(128) NOT NULL,
    ->   prefecture_kana varchar(255) NOT NULL,
    ->   PRIMARY KEY(prefecture)
    -> ) DEFAULT CHARACTER SET= utf8mb4;

mysql> INSERT INTO zipcode.prefecture_kana SELECT DISTINCT prefecture, prefecture_kana FROM zipcode;

mysql> ALTER TABLE zipcode DROP COLUMN prefecture_kana, ADD CONSTRAINT FOREIGN KEY fkey_prefecture (prefecture) REFERENCES prefecture_kana(prefecture);

mysql> SELECT zip_code, prefecture, prefecture_kana, city, city_kana, town, town_kana FROM zipcode.zipcode NATURAL JOIN zipcode.prefecture_kana ORDER BY zip_code LIMIT 3;
+----------+------------+-----------------------+-----------------+-----------------------------+--------------------------------------+-----------------------------------------------+
| zip_code | prefecture | prefecture_kana       | city            | city_kana                   | town                                 | town_kana                                     |
+----------+------------+-----------------------+-----------------+-----------------------------+--------------------------------------+-----------------------------------------------+
| 0010000  | 北海道     | ﾎｯｶｲﾄﾞｳ               | 札幌市北区      | ｻｯﾎﾟﾛｼｷﾀｸ                   | 以下に掲載がない場合                 | ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ                               |
| 0010010  | 北海道     | ﾎｯｶｲﾄﾞｳ               | 札幌市北区      | ｻｯﾎﾟﾛｼｷﾀｸ                   | 北十条西（１～４丁目）               | ｷﾀ10ｼﾞｮｳﾆｼ(1-4ﾁｮｳﾒ)                           |
| 0010011  | 北海道     | ﾎｯｶｲﾄﾞｳ               | 札幌市北区      | ｻｯﾎﾟﾛｼｷﾀｸ                   | 北十一条西（１～４丁目）             | ｷﾀ11ｼﾞｮｳﾆｼ(1-4ﾁｮｳﾒ)                           |
+----------+------------+-----------------------+-----------------+-----------------------------+--------------------------------------+-----------------------------------------------+

たとえばこの通り，prefectureカラムとprefecture_kanaカラムは1対1で対応していますので，簡単に外部テーブルに切り出すことができます。切り離したテーブルはJOINで結合することで同じ結果に戻すことができるので，JOINの練習ができます。

ちなみに，prefecture_kanaテーブルを切り出したのと同じように読み仮名だけを切り出していくと，town_kanaテーブルを作ろうとしたところで「旭町」（⁠あさひまち，あさひちょう，の両方があります）がユニーク制約に引っかかって，ぐぬぬぬぬプライマリーキーは漢字と読み両方にしないとダメか……cityとcity_kanaはたまたま衝突するものがなかっただけでプライマリーキーを変更しておくべきか……など，テーブル設計の練習のようなことができたりもします。

更に郵便番号を1番多く持っている都道府県はどこか，第20位はどの都道府県か（MySQLにRANK関数はありませんので，なにがしかの方法で上手くやる必要がありますが，SQLだけで全て終わらせる必要はありません⁠）⁠。あるいは「サ」で始まる市町村を1番多く持つ都道府県が持つ郵便番号のうち最小のものはどれか……などなど，JOINとGROUP BYやサブクエリーを駆使した問い合わせ，また，それらに上手く効くインデックスを考える……など，（⁠データが多少身近なものなので）楽しみ方は色々あります。

更なるサンプルデータを求めて

3つのサンプルデータを紹介してきましたが，いずれもMySQLの操作に慣れてくると飽きが生じます（vmstat以外は生成され続ける類のものではありませんし，vmstatも単一のテーブルでしかないのであまり長い間楽しめるかというと今一つです⁠）⁠。

MySQLの勉強のためのサンプルデータと考えた場合，「⁠そのマシンだけで生成できること（マシンを起動しているだけで手に入るもの⁠）⁠」⁠「⁠（⁠時系列的に）継続的に取り続けられること」「⁠他の情報とのリレーションシップを持つもの」がお勧めです。1つ目は，外部要因が関係してくると手間がかかるからです（逆に言うと，それを取得するのに必要な手間が充分小さければ，それも適していると思います⁠）⁠。2つ目は，データが積み重なることで状況が変わってくることを体験するためです。100行のテーブルならどんなクエリーでもある程度の時間で返ってきますが，10億行のテーブルでは1バイトのソートが致命的な遅さをもたらすこともあります。3つ目は，やはりリレーショナルデータベースの醍醐味はJOIN，サブクエリー，GROUP BYを使って縦横無尽にデータを抽出することですので，「⁠この時間にCPUがスパイクしている，他のリソースの様子はどうだった？」「⁠MySQLのテンポラリーテーブルの作成回数とブロック単位のI/Oの回数に相関はあるのか？」など，日本語で命題を定義し，それをSQLに落としていく，という楽しみのためです。

この3つを満たすというとやはりリソース情報になるのかなと思っていますが，何かもっと面白そうな題材があったら教えてください。

MySQL, SQL, 郵便番号

著者プロフィール

yoku0825

GMOメディア株式会社のDBA。日本MySQLユーザ会員。主に地雷担当。主食はMySQLだがPercona Server，MariaDB，InfiniDBにMroongaストレージエンジンなど，mysqldでありさえすれば雑食。

Twitter：@yoku0825