[MySQL] LOAD DATA LOCAL INFILE でCSVをインポート

2020-02-05PHPSQL

CSV ファイルのデータを MySQL データベースにインポートする方法。
大量のデータを、高速に取り込むことができるという。

構文

[] に囲まれているのは省略可。

LOAD DATA LOCAL
INFILE 'ファイル名'
    [REPLACE | IGNORE]
    INTO TABLE テーブル名
    [FIELDS
        [TERMINATED BY '区切り文字']
        [[OPTIONALLY] ENCLOSED BY '囲み文字']
        [ESCAPED BY 'エスケープシーケンス' ]
    ]
    [LINES TERMINATED BY '行の改行文字']
    [IGNORE 数値 LINES]
    [(@CSVの項目値を格納する変数, ...)]
    [SET DBのカラム名 = @CSV変数 または 任意の値, ...]

13.2.6 LOAD DATA INFILE 構文

CSV ファイルをインポート

CSV ファイルのデータが、左から順にカラムに挿入されていく。

LOAD DATA LOCAL INFILE 'hoge.csv' INTO TABLE sector FIELDS TERMINATED BY ',';

特定のカラムにインポート

インポートするカラムを指定する方法。

LOAD DATA LOCAL INFILE "CSV ファイルパス"
INTO TABLE テーブル名
FIELDS TERMINATED BY '区切り文字'
[LINES TERMINATED BY '行の改行文字']
[IGNORE 1 LINES]
(@1,@2,@3)
SET columnA=@1, columnB=@3;

CSV ファイルにない値を挿入

CSV にはない、任意の値を挿入する。

LOAD DATA LOCAL INFILE "CSV ファイルパス"
INTO TABLE テーブル名
FIELDS TERMINATED BY '区切り文字'
[LINES TERMINATED BY '行の改行文字']
[IGNORE 1 LINES]
SET columnA=任意の値;

条件分岐させる場合

CASE 文を使う。

(@1,@2,@3)
SET columnA=CASE @2
        WHEN 条件値1 THEN 処理
        WHEN 'hoge' THEN NULL
        ELSE @2
    END,
    columnB=@3;

CSV に囲み文字があったりなかったりする場合

大部分が囲み文字なしだが、値に「カンマ（,）」が含まれる場合のみダブルクォートで囲まれているとき。
ENCLOSED BY を使う。 OPTIONALLY はあってもなくても同じ結果だった。

[OPTIONALLY] ENCLOSED BY '\"'