ハードディスクメンテナンス

投稿記事数[8500]以上!PC,旧ゲーム,カメラ,動画などを、アヤしゐ專門妖語を使ッて解説スル!

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

   

>>高還元のクレジットカード比較 期間限定のキャッシュバックなど!<<


スポンサーリンク

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



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の内容が格納されている
csvの内容が格納されている

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

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

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

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

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

2回格納されている
2回格納されている

これを防ぐには、

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

のオプションを追加する。

これで、格納前に空にしてくれるので、追記は回避できる。

なお、原因は不明だが -D ではエラーとなる(エラー表示はないが格納されない)。

–delete なら問題なし。

–オプションに対し、-省略形、つまり、

–delete と -D は同意という理解は間違っているのか?

関連:mysqlimport –helpの内容

このphpファイルを定期的に実行するには、cronに登録すればよい。

cd /home/aaa/www ; /usr/local/bin/php test.php > /dev/null

test.phpのあるディレクトリ ; PHPのコマンドパス プログラムを記したphpファイル メール設定

最後の >/dev/null は postmaster@aaa.sakura.ne.jp へメールを送らない場合に書く。

そして、cronが実行できるように、test.phpのパーミッションを755に設定。

設定後は、指定した間隔や時間に、問題なくDBに格納されるかを確認する。

メールを送る設定であれば Permission denied 等のエラーが postmaster@aaa.sakura.ne.jp に送られるので、確実に動くことを確認するまでは >/dev/null を付けない方がいい。

cronは5個までしか設定できないようだが、コントロールパネルからではなく、サーバーに接続しコマンドでやれば、5個以上可能?

関連:さくらインターネットのCRONの設定画面(1.CRONの設定)

関連:さくらインターネットのCRONの設定画面(2.CRONスケジュール設定)

MySQL全機能バイブル ~現場で役立つAtoZ~
鈴木 啓修
技術評論社
売り上げランキング: 170,416
スポンサーリンク



sha-bc@336×280

sha-bc@336×280


sha-bc@336×280

sha-bc@336×280

  関連記事

B00ZQNBSVQ
Logicoolの ワイヤレス レーザーマウス M510BK がタイムセールで2,520円!

スポンサーリンク Unifying対応! 【Amazon.co.jp限定】 Lo …

B00P9VXAMK
Philipsの 23型液晶モニター AH-IPSパネル FullHD(1920×1080) 5年保証 238C5QHSN/11 がタイムセールで16,000円!

スポンサーリンク Philips 23型ワイド液晶ディスプレイ (AH-IPSパ …

B01CCUYZ5Q
Transcend(トランセンド)の SDXCカード 128GB Class10 UHS-I U3対応(最大読込速度95MB/s) TS128GSDU3E がタイムセールで6,980円!

スポンサーリンク 【Amazon.co.jp限定】 Transcend SDXC …

B00TQ8EOB8
ELECOM(エレコム)の Giga対応 スイッチングHUB 5ポート 磁石付き EHC-G05PA-JW-K がタイムセールで2,180円!

スポンサーリンク ELECOM Giga対応スイッチングHub 5ポート プラス …

Windowsバックアップが設定されていません
Windowsバックアップが勝手に無効になっている件 タスクイメージ 破損 0x80041321 AutomaticBackup Windows Backup Monitor

スポンサーリンク サブマシンのバックアップ(Windows7の標準機能)が無効に …

B005LFFE7I
Logicool(ロジクール)の ウェブカム フルHD動画対応 C615 がタイムセールで4,091円!

スポンサーリンク LOGICOOL HDウェブカム フルHD動画対応 C615 …

B00H8JHPO0
Transcendの SDXCカード 64GB Class10 UHS-I対応(最大転送速度90MB/s) TS64GSDXC10U1E がタイムセールで3,580円!

スポンサーリンク 【Amazon.co.jp限定】Transcend SDXCカ …

B00WMK89S6
シリコンパワーの SSD 120GB SATA3 6Gb/s SP120GBSS3S55S25FR がタイムセールで5,391円!

スポンサーリンク サブ機用に! SP シリコンパワー SSD 120GB SAT …

B00FEZD86E
DELLの 29型 超横長(2560×1080,UWFHD,21:9)液晶モニター IPS 非光沢 U2913WM 3年間保証 がタイムセールで39,980円!

スポンサーリンク Dell ディスプレイ モニター U2913WM 29インチ/ …

B00LJ38EFK
ASUSTekの NVIDIA GT730搭載ビデオカード(ファンレス) GT730-SL-2GD3-BRK がタイムセールで5,180円!

スポンサーリンク ASUSTeK NVIDIA GT730 GT730-SL-2 …