MySQLのテーブルにCSVファイルのデータを取り込む

基本的な使い方

次のように LOAD DATA LOCAL INFILE の後にファイルパスを、 INTO TABLE の後にデータを取り込む先のテーブルを指定する。

LOAD DATA LOCAL INFILE "ファイルパス" INTO TABLE テーブル名;

この時のデータファイルの1行目は、カラム名と一致している必要がある。例えば次のtestingのようなテーブルを考える。

CREATE TABLE testing {
  id int DEFAULT NULL,
  name varchar(100) DEFAULT NULL
}

testingテーブル

最初の列にid,次の列にnameの値を含むCSVとすると、次のようになる。

id,name
1,foo
2,bar
3,baz

testing-simple.csv

CSVファイルの取り込むには区切り文字を FIELDS TERMINATED BY で指定する必要がある。

LOAD DATA LOCAL INFILE "./testing-simple.csv" TABLE testing
     FIELDS TERMINATED BY ',';

FIELDS TERMINATED BYで区切り文字を指定する

CSVファイルでは、カンマや空白を含む値はダブルクォートで括られる。

id,name
1,foo
2,"b,ar"
3,baz

testing-double-quote.csv testing-simple.csv

このような値を含むCSVを取り込むとダブルクォート文字もそのまま取り込んでしまう。これを回避するには OPTIONALLY ENCLOSED BY を指定する。

LOAD DATA LOCAL INFILE "./testing-simple.csv" TABLE testing
     FIELDS TERMINATED BY ','
     OPTIONALLY ENCLOSED BY '"';

OPTIONALLY ENCLOSED BYでダブルクォートの取り込みを回避する

LOAD DATA LOCAL INFILE を用いると次のようなエラーが出ることがある。

ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

これは権限が足りていないのだが、取り込む為に幾つかの方法を実施した。

サーバー側の設定がどうなっているかによって、この機能が使えるかどうかが変わる。システム変数local_infileが1に設定されている必要がある。

SHOW VARIABLES LIKE 'local_infile';

MySQLの管理者権限を持っていれば次で変更できる。今回はローカルに起動したDocker上のMySQLであり管理者権限を持っていたので、あまり考えずに変更する。

SET GLOBAL local_infile=1;

サーバー側の設定変更だけでなく、MySQLクライアントの起動時に --local-infile=1 を指定する必要があった。

mysql -h localhost --local-infile=1