csvファイルのMySQLへのインポート (LOAD DATA LOCAL INFILE,mysqlimport) さくらインターネット

2014年05月24日

自鯖(自由度高)を使うことが多いが、さくらインターネットのユーザーから質問を受けたのでメモ。

WEBサーバー内にcsvファイルを置いて、それをDB(MySQL)にインポートする。

phpMyAdminのインポート機能を使えば可能だが、phpプログラムで実行したい。

となると、LOAD DATA LOCAL INFILEが考えられる。

① LOAD DATA INFILE
インポート先のDBがあるサーバーにcsvファイルがある場合

② LOAD DATA LOCAL INFILE
クライアントにあるcsvファイルをサーバーのDBへ

MySQL全機能バイブルの381ページ

この場合は①が使用可能と思えるが、csvファイルのあるサーバー(WEBサーバー)とMySQLのあるサーバー(DBサーバー)が別の場合(さくらインターネット等)は、この方法では不可能では？

ネットで調べると、成功したとかしなかったとか、mysqlimportで成功した等の情報があるが、同じレンタルサーバー業者でも、DBのバージョンや設定が異なるので、実際は自分で試してみないとならない。

また、情報の日付に注意。

以前は有効でも今は無効ということがあるからだ。

phpMyAdminでのインポートでは、最大サイズの制限がある(最長：16MiB)。

自己管理のローカルサーバー等、自由度のある場合は上限値を変更できるが、レンタルサーバーの場合は困難だろう。

そのような場合、FTPでcsvファイルを上げておいて、以下の方法でDBにインポートするとよいだろう。

phpでプログラムを書いておくと、コマンド打たなくてもいいしね。

以下、手順。

■csvファイルの準備

WEBサーバー上の /home/aaa/www/ に test.csv を置く。

aaaは、さくらインターネットでのアカウント名。

test.csvの中身は

1,first
2,second
3,third
4,forth
5,fifth

という、カンマ区切りの簡単なもの。

■DBの準備

mysql***.db.sakura.ne.jp – aaa_xxx – test

に、以下のようなテーブル(test)を作成しておく。

テーブル

・id int
・name text

***は、さくらインターネットから割り当てられたDBの番号。

aaa_xxxはDB名だが、必ずaaa_が付加されるのは、さくらインターネットの仕様。

テーブル名は、

■コマンドを記したphpファイルを作成、実行

WEBサーバーのtest.csvと同じディレクトリに

＜?PHP

$import = “mysqlimport -h mysql***.db.sakura.ne.jp -u aaa -p(パスワード) -L –fields-terminated-by=’,’ aaa_xxx /home/aaa/www/test.csv” ;
system($import) ;

?＞

というphpファイル(import.phpとする)を置き、これを実行。

・-h mysql***.db.sakura.ne.jp　ホスト
・-u aaa　DBアクセス用のユーザ名
・-p(パスワード)　DBアクセス用のパスワード、-pに続けて(スペースなしに)書くこと
・-L　ローカルファイルから読み込むオプション
・–fields-terminated-by=’,’　csvファイルの区切文字、今回は「,(半角カンマ)」
・aaa_xxx　DB名
・/home/aaa/www/test.csv　csvファイルの場所

すると、

aaa_xxx.test: Records: 5 Deleted: 0 Skipped: 0 Warnings: 0

と表示され、以下のようにデータが格納される。

csvの内容が格納されている

さて、上のmysqlimport～にて、DB名の指定はあるが、テーブル名の指定はないのか？

と疑問に思うが、test.csvの拡張子を取ったものが、テーブル名なのだ。

つまり、ファイルがtest.txtの場合も、testがテーブル名。

ファイル名とテーブル名が異なる場合は？

import.phpをリロードすると、データが「追記」されてしまう。

2回格納されている

これを防ぐには、

–delete　インポートする前にテーブルを空にする