MySQLにCSV形式ファイルを取り込む

最近ではLAMPなどMySQLなどのデータベースと連携したソフトウエア開発がかなり多くなってきました。僕もMySQLとPHPを連携したWebアプリケーションを開発しています。

サンプルデータを入力する際、今まではinsert文をテキストファイルに書いておき、souceコマンドでそのファイルを読み込み、SQLを発行するという方法を取っていたのですが、入力するデータ数が増えるにつれ面倒になってきました。どうにかして簡単にテーブルへデータを挿入する方法はないものか調べてみたところ、テキストファイルからテーブルに行を読み込むSQLが存在することがわかりました。MySQLもこのSQLに対応しているそうです。

使用するのは、LOAD DATA IN FILE構文です。
詳しくはMYSQL5.1のリファレンスに載っています。

http://dev.mysql.com/doc/refman/5.1/ja/load-data.html

今回はOpenOffice Calcで作成した、CSVを取り込む方法を紹介します。
CSVの仕様としては、
文字コード：UTF-8
フィールドの区切り文字：,
テキストの区切り記号：”
です。

OpenOffice Calcでは、保存時にCSVを選択すると、オプションの選択ウィンドウが出てくるので、そこで設定します。

オプションの選択ウィンドウ

このCSVファイルを読み込むSQLは以下のようになります。
（テーブル名：table_name、CSVの絶対パス：csv_passと仮定します。）

LOAD DATA LOCAL INFILE 'csv_pass'
  INTO TABLE table_name
  FIELDS
    TERMINATED BY ','
    ENCLOSED BY '"'

LOCALというのはオプションの一つです。デフォルトではMySQLサーバーが直接ファイルを読み取るのですが、LOCALを指定することによりファイルはクライアントホストによって読み込まれMySQLサーバーに渡されます。（これによってファイルがクライアントホストに読み取り可能であれば良いということになります。また、MySQLユーザは挿入先のテーブルに対するFILE権限がなくても実行可能になるそうです。）

FIELDSオプションでフィールドの区切り記号(TERMINATED BY)テキストの区切り記号(ENCLOSED BY)を指定しています。