MySQLでfgetcsv()（CSV取り込み）

Tweet

Category MySQL, php 2009/08/22

データ交換のフォーマットとしてCSVはそれなりに歴史があります。

MySQLも古くからCSV形式でのデータのエクスポート、インポート機能を持っています。
（load data infiile 構文、mysqlimport コマンド）

これがよくできていて、次のようなことができます。

1. 区切り文字を指定できる。
2. 囲み文字を指定できる。
3. エスケープ文字を指定できる。
4. 指定した最初のn行をスキップする。
5. 行末文字（デフォルト’\n’）を指定できる。
6. 重複の無視（INSERT IGNORE）、重複したら置換（REPLACE）のどちらでもできる。
7. CSVのデータの並び順は、データベースのテーブルと同じでなくても大丈夫。
8. CSVファイルはクライアント側にあってもサーバー側にあっても大丈夫。

fgetcsv()より高機能ですね。

では、実演コーナー

郵政公社の郵便番号CSVを取り込んでみます。
http://www.post.japanpost.jp/zipcode/download.html

lzh形式ファイルをダウンロード、解凍。郵便番号データは12万件ほどあります。CSVファイルはShift_jisなのでエディタなり何かのツールなりでUTF-8に文字コードを変換。（文字コードはデータベースの設定に合わせて。）

テーブルは仮にこんなので。

create table postaldata (
    id int not null primary key auto_increment,
    c1 varchar(10) not null,
    c2 varchar(10) not null,
    c3 varchar(10) not null,
    c4 varchar(20) not null,
    c5 varchar(100) not null,
    c6 varchar(100) not null,
    c7 varchar(20) not null,
    c8 varchar(100) not null,
    c9 varchar(100) not null,
    c10 tinyint not null,
    c11 tinyint not null,
    c12 tinyint not null,
    c13 tinyint not null,
    c14 tinyint not null,
    c15 tinyint not null
);

CSVファイルの内容を、そのままデータベースへ。

load data infile "/tmp/KEN_ALL.CSV"
into table postaldata
FIELDS
    TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    ESCAPED BY ''
LINES
    STARTING BY ''
    TERMINATED BY '\r\n'
IGNORE 0 LINES
(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15);

所要時間 0.92秒。CSVファイルをphpで解析して……をやるより、断然速いです。

使い慣れてくると、ダンプ＆リストアの作業のような感覚になってきます。よくできた安心できるツールのような気がしてきます。