基本的な使い方
次のように 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
}
最初の列にid,次の列にnameの値を含むCSVとすると、次のようになる。
id,name
1,foo
2,bar
3,baz
CSVファイルの取り込むには区切り文字を FIELDS TERMINATED BY
で指定する必要がある。
LOAD DATA LOCAL INFILE "./testing-simple.csv" TABLE testing FIELDS TERMINATED BY ',';
CSVファイルでは、カンマや空白を含む値はダブルクォートで括られる。
id,name
1,foo
2,"b,ar"
3,baz
このような値を含むCSVを取り込むとダブルクォート文字もそのまま取り込んでしまう。これを回避するには OPTIONALLY ENCLOSED BY
を指定する。
LOAD DATA LOCAL INFILE "./testing-simple.csv" TABLE testing FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
LOAD DATA LOCAL INFILE file request rejected due to restrictions on access
LOAD DATA LOCAL INFILE
を用いると次のようなエラーが出ることがある。
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
これは権限が足りていないのだが、取り込む為に幾つかの方法を実施した。
システム変数local_infileを1にする
サーバー側の設定がどうなっているかによって、この機能が使えるかどうかが変わる。システム変数local_infileが1に設定されている必要がある。
SHOW VARIABLES LIKE 'local_infile';
MySQLの管理者権限を持っていれば次で変更できる。今回はローカルに起動したDocker上のMySQLであり管理者権限を持っていたので、あまり考えずに変更する。
SET GLOBAL local_infile=1;
クライアントの起動オプションに–local-infile=1を指定する
サーバー側の設定変更だけでなく、MySQLクライアントの起動時に --local-infile=1
を指定する必要があった。
mysql -h localhost --local-infile=1