mysql の load data local infile でcsvファイルを読み込ませる時の注意

MySQLにcsv(RFC4180)フォーマットのファイルを読み込ませる

mysqlにはファイルからデータを取り込むために load (local) data というクエリが用意されています。
このクエリでCSVファイルを読み込むには以下の様なクエリを使います。

load data local infile 'text.csv' INTO table load_test FIELDS terminated by ',' enclosed by '"' escaped by '"';

上記はファイル内の

• データのの区切り文字は ,
• データの囲い文字は "
• 囲い文字をエスケープする文字は "

という意味で、これらを指定することによりRFC4180 フォーマットのファイルをテーブルに読み込ませる事が出来ます。

割りと酷い内容のファイルでも読み込ませる事が出来る

例えば

mysql > desc load_test;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| text1 | varchar(256) | YES  |     | NULL    |       |
| text2 | varchar(256) | YES  |     | NULL    |       |
| text3 | varchar(256) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

こんなテーブルがあって、
このテーブルに普通のCSVパーサーではエラーになる様なファイル

hunk_1.csv
aaaaaa,bbb""bbbb,cccccccc
xxxxxx,yyy"yyyy",dddddddd

を読み込ませても意外と取り込んでくれます。

mysql> load data local infile 'hunk_1.csv' INTO table load_test FIELDS terminated by ',' enclosed by '"' escaped by '"';
Query OK, 2 rows affected (0.05 sec)
mysql> select * from load_test;
+-----------+--------------+--------+
| text1     | text2        | text3  |
+-----------+--------------+--------+
| aaaaaaaaa | bbb"bbbb     | cccccc |
| xxxxxx    | yyyyy"yyyyy" | zzzzzz |
+-----------+--------------+--------+

"で始まるデータが有ると読み込みをしくじる

どうも （行頭または） 区切り文字``囲い文字 と文字が続くと、それ以降に現れる 囲い文字``区切り文字 （またはEOF）までを一つのデータとみなす仕組みの様で、行頭またはデータの最初に 囲い文字 があって、データの最後に 囲い文字 がない場合はデータの読み込みに失敗します。

hunk_2.csv
aaaaaa,"bbbbbbb,cccccccc
xxxxxx,yyyy"yyy,dddddddd

mysql> load data local infile 'hunk_2.csv' INTO table load_test FIELDS terminated by ',' enclosed by '"' escaped by '"';
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> select * from load_test;
+----------------------------+---------------------------------------------+--------+
| text1                      | text2                                       | text3  |
+----------------------------+---------------------------------------------+--------+
| aaaaaa                     | "bbbbbbb,cccccccc
xxxxxx,yyy"yyyy,dddddddd
 | NULL   |
+----------------------------+---------------------------------------------+--------+