MySQLでファイルのデータをテーブルに挿入する

MySQLでのファイルからのデータ挿入方法について。
バージョン5.7.11でmysqlクライアントを使用して確認しています。

LOAD DATA INFILE

LOAD DATA INFILE を使用してファイルをテーブルに登録することができます。

構文

サーバ上のファイルを使用する場合

LOAD DATA INFILE 'ファイル名' INTO TABLE テーブル名

ローカルファイルを使用する場合

LOAD DATA LOCAL INFILE 'ファイル名' INTO TABLE テーブル名

例

mysql> system sudo cat /var/lib/mysql-files/test.tsv;
1   isono
2   fuguta

mysql> SELECT * FROM test;
Empty set (0.00 sec)

mysql> LOAD DATA INFILE '/var/lib/mysql-files/test.tsv' INTO TABLE test;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM test;
+------+--------+
| id   | name   |
+------+--------+
|    1 | isono  |
|    2 | fuguta |
+------+--------+
2 rows in set (0.00 sec)

secure_file_priv

LOCALオプションを使用せずにサーバ上のファイルを使用する場合で、サーバオプションのsecure_file_privが設定されている場合には、設定されたディレクトリ以外からの入力はエラーになります。

mysql> SELECT @@secure_file_priv
    -> ;
+-----------------------+
| @@secure_file_priv    |
+-----------------------+
| /var/lib/mysql-files/ |
+-----------------------+
1 row in set (0.00 sec)

mysql> LOAD DATA INFILE '/tmp/test.tsv' INTO TABLE test;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

FIELDSオプションとLINESオプション

先の例では区切り文字はタブで認識されています。
FIELDSオプションを使用すれば区切り文字を、LINESオプションを使用すれば改行コードを修正できます。

mysql> system sudo cat /var/lib/mysql-files/test.csv;
1,"磯野"
2,"フグ田"

mysql> SELECT * FROM test;
Empty set (0.00 sec)

mysql> LOAD DATA INFILE '/var/lib/mysql-files/test.csv' INTO TABLE test
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
    -> LINES TERMINATED BY '\r\n';
Query OK, 2 rows affected (0.01 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM test;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 磯野      |
|    2 | フグ田    |
+------+-----------+
2 rows in set (0.00 sec)