MySQLのLOAD DATA INFILEを稀によく使うので、使い方をメモ

DataBase

よく使うやつ

LOAD DATA INFILE '/tmp/foo.csv' INTO TABLE foo FIELDS TERMINATED BY ',' IGNORE 1 LINES;

csvを読み込むにはFIELDS TERMINATED BY ','が必要

LOAD DATA INFILEは、タブ区切りがデフォルトになっているらしい。カンマ区切りであるcsvを読み込むにはFIELDS TERMINATED BY ','でカンマ区切りである事を指定します。

1行目にフィールド名を入れてあるので、それを無視する

IGNORE 1 LINESを付けることで、先頭1行を無視することが出来ます。インポート用のcsvを作る際、フィールド名がないと分かりにくいし、事後に修正して再利用することを考えると、インポートする時だけ消すのも面倒くさいので。

主キーであるidも必要

インポートするcsvには、全フィールドが順番どおりに載っている必要があります。auto_incrementするから主キーはいらないと思っても、1列目に主キーを載せないとです。ただ、数値を書かずに空欄にしておいたらauto_incrementは働くので、項目を置いておくだけ*1です。

なお、created_atやupdated_atの様な、テーブルの最後にあるフィールドに、データを入れなくても良いと言う場合、項目数が足りない分には問題ないです。多分warningsにはなると思いますが。

warningsを確認する

SHOW warnings;

LOAD DATA INFILEを使うと、ほぼ必ず何かしらのwarningsは出る*2と思いますが、確認したいときもあります。LOAD DATA INFILE実行直後にのみ、これで確認できます。

ERROR 13 (HY000): Can't get stat of '/home/myuser/foo.csv' (Errcode: 13)

FILE権限が無かったからmysql -u rootでLOAD DATA INFILEしようとしたら、こんなエラーになりました。mysqlのrootでは、別ユーザーのホームディレクトリは参照出来ない様です。/tmp/foo.csv とかに移動したら読み込めました。

参考

ファイル読込｜データのインポート・エクスポート｜MySQL｜PHP & JavaScript Room
ファイルハンドルと出力 - SMART 開発者のためのウェブマガジン

*1:LOAD DATA INFILE実行時にwarningsにはなります

*2:上記の様に主キーをcsvで空欄で持つとwarnings出ますし