CSVファイルからMySQLへデータを取り込む、取り出す

MySQLのカラムの数とCSVの1行のデータの数が合わない時は、明示的にどのカラムにデータを取り込むか書かないと正しくデータの取り込みができません。なかなか素直にデータ取り込みできない事も多く、忘れてしまうので書き残しておきます。

CSVをMySQLにつっこむ

まずは、テーブルの構造

mysql> DESC mytable;

+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   | PRI | NULL    |       |
| hoge  | varchar(128)     | YES  |     | NULL    |       |
| fuga  | varchar(16)      | YES  |     | NULL    |       |
| flg   | tinyint(1)       | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+

CSVファイルの中身はタブ区切りでこんな感じ。
エクセルからデータをコピーしてメモ帳に貼り付けるとこういうデータになりますね。

1 aaa 111-0000
2 bbb 222-0000
3 ccc 333-0000
4 ddd 444-0000
...

1つ目のデータはID、2つ目のデータはhoge、3つ目のデータはfugaに突っ込みたいのですが、flgは無視したい。
こういう感じでいきます。

CSVファイルの保存先は、/home/ystream/data.csv としておきます。

LOAD DATA LOCAL INFILE '/home/ystream/data.csv' INTO TABLE `mytable` FIELDS TERMINATED BY '\t' (id, hoge, fuga) FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\';

以上で取り込みは完了です。

SELECT * FROM `mytable` LIMIT 10;
+----+------+----------+------+
| id | hoge | fuga     | flg  |
+----+------+----------+------+
|  1 | aaa  | 111-0000 | NULL |
|  2 | bbb  | 222-0000 | NULL |
|  3 | ccc  | 333-0000 | NULL |
|  4 | ddd  | 444-0000 | NULL |
|  5 | eee  | 555-0000 | NULL |
|  6 | fff  | 666-0000 | NULL |
|  7 | ggg  | 777-0000 | NULL |
|  8 | hhh  | 888-0000 | NULL |
|  9 | iii  | 999-0000 | NULL |
| 10 | jjj  | 111-0001 | NULL |
+----+------+----------+------+

FIELDSを指定しない場合は、以下のFIELDSが指定されるようです。

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

つまり

LOAD DATA LOCAL INFILE "/home/ystream/data.csv" INTO TABLE `mytable` (id, hoge, fuga);
  ↓
LOAD DATA LOCAL INFILE "/home/ystream/data.csv" INTO TABLE `mytable` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' (id, hoge, fuga);

となるので、タブ区切りでクォーテーションで囲まれていないデータの場合はFIELDSを書かなくても良いようですが、この仕様を覚える必要もないので書いたほうが捗ると思います。

データをCSVに吐き出す

吐き出す場合は、SELECTで欲しいデータの範囲やカラムを選んで書き出します。

SELECT `id`, `hoge`, `fuga` FROM `mytable` INTO OUTFILE '/tmp/data.csv' FIELDS TERMINATED BY '\t';

超簡単です。ちなみに、ファイルの書き込みはログインしているユーザーではなく、MySQLから行われるので、オーナーはmysqlになり、ログインユーザーのホームディレクトリには書き込めません、多分。一旦/tmpなどに落としてから移動させるのがいいかと思います。