r_nobuホームページ (のぶねこブログ)

本日、MySQLでCSVファイルを取り込もうとして苦労したので、気をつけることについてメモ。

１．データベース接続時に「--local-infile=1」を指定する。

《Tera Term等でMySQLをコマンド操作した時の例》
mysql -udbusername --local-infile=1 -p
※「dbusername」は、データベース名。

「--local-infile=1」を指定しないと、CSVファイル取り込み時に、以下のようなエラーが発生してしまった。

mysql> LOAD DATA LOCAL INFILE '/var/tmp/test.csv' INTO TABLE testtable FIELDS TERMINATED BY ',' ENCLOSED BY '"' (test1, test2);
ERROR 1148 (42000): The used command is not allowed with this MySQL version

２．CSV取り込み時に自動採番させるためのcreate tableで、「integer auto_increment primary key」を書く。

CSV取り込みとはあまり関係がなかったかもしれないが、CSVデータ取り込み時に、各レコードに一意となるID番号を自動採番で割り振りいと思って、create tableでそれを意識したテーブルを作ったつもりだったが、最初の1レコード目のIDが0になり、以降のレコードが取り込まれない、という問題があった。最終的に、以下のcreate tableであれば、うまくできた。

create table testtable (id integer auto_increment primary key,test1 text,test2 text);

３．CSVファイルを「UTF-8」にする。

CSVファイルを取り込むと、文字化けしていたり、CSVファイル内の各フィールドを囲む文字列「"（ダブルクォーテーション）」まで取り込まれていたり、改行コードが「\r\n」で認識されず「\n」で認識されてしまうなど、どうしたらよいのだろう、という現象が起きて途方に暮れていたところ、サクラエディタ（テキストエディタ）でCSVファイルの文字コードを見てみると、「unicode BOM付」となっていることに気付いた。

サクラエディタで、CSVファイルを「unicode BOM付」を「UTF-8」に変更すると、MySQLでのCSVファイル取り込みが全く問題なくできるようになった。

４．CSVファイルを取り込む時の構文は正しいかどうかを何度かテストする。

私の場合は、最終的に、CSVファイルを取り込む時の構文は、以下になった。（CSVファイルの形式などによって構文は違うと思うので、参考にされる方は、このままではダメかもしれないので、ご注意。）

LOAD DATA LOCAL INFILE '/var/tmp/test.csv' INTO TABLE testtable
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
ignore 1 lines
(test1,test2)
;

この構文では、フィールドの区切り文字が「,」（カンマ）、フィールドを囲む文字が「"」（ダブルクォーテーション）、改行コードが「\r\n」（CRLF）、CSVファイル1行目が取り込み対象外の文字列、テーブルの取り込み先カラム（列）を指定している。