こんにちは。学生アルバイトのsasaokaです。

MySQLにはCSVやTSVなどのテキストファイルから直接データベースのテーブルに高速にデータをロードすることができるLOAD DATA INFILEというコマンドがあります。今回は、その使い方とエスケープに関する注意点を述べ、最後におすすめのパラメータを紹介します。

実行環境

今回使用したマシンとSQLサーバは以下になります。

• MacBook Pro (Intel Core i5)
• MariaDB 10.4.22

使い方

以下の構文で使用します（公式レファレンスより引用）。

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT}
        [, col_name={expr | DEFAULT}] ...]

例えば次のように使用します。

LOAD DATA
    LOCAL
    INFILE 'user.csv'
    INTO TABLE users
    FIELDS
        TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '"'
    IGNORE 1 LINES
    (name, gender, age);

各パラメータについて簡単に紹介します。その他のものについては公式レファレンスを参照してください。

LOCAL

LOCALオプションを付けない場合は、/usr/local/var/mysqlなどのデフォルトのデータベースディレクトリ内からファイルが探されます。したがって、これ以外のディレクトリにあるファイルを相対パスで指定する場合はLOCALオプションを付ける必要があります。

FIELDS

デフォルトはFIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'になっています。

TERMINATED BYの後にフィールドの区切り文字を指定します。したがって、CSVファイルをロードする場合はFIELDS TERMINATED BY ','とする必要があります。

フィールドがダブルクォーテーションで囲まれている場合はENCLOSED BY '"'、一部がダブルクォーテーションで囲まれている場合はOPTIONALLY ENCLOSED BY '"'とします。ダブルクォーテーションで囲まれたフィールドの中のカンマはそのままテキストとして扱われます。

ESCAPED BYの後にエスケープ文字を指定します。これについては後で詳しく説明します。

LINES

デフォルトはLINES TERMINATED BY '\n' STARTING BY ''となっています。入力ファイルの改行コードがCRLFの場合はTERMINATED BY '\r\n'とする必要があります。

IGNORE

入力ファイルにヘッダー行がある場合は、IGNORE 1 LINESオプションを付けることでヘッダー行をスキップできます。

カラムの指定

テーブルのカラムの一部のみをロードする場合は、(col_name1, col_name2, ...)のようにカラム名を指定します。

エスケープ文字について

デフォルトはESCAPED BY '\\'、つまりバックスラッシュがエスケープ文字になっています。このとき、フィールド内の\nや\tはそれぞれ改行、タブとして扱われます。また、フィールドがダブルクォーテーションで囲まれている場合は\"だけでなく""もエスケープされて"となってしまいます。

このように、デフォルトのエスケープでは、想定外のリテラルに変換されることがあるため、使わない方が良く、エスケープ文字はダブルクォーテーションにするのが良いでしょう。

以下の表にエスケープ文字をバックスラッシュ、ダブルクォーテーションにしたときに、各文字がどのように変換されるかをまとめました。ここではフィールドがダブルクォーテーションで囲まれている場合を扱っています。

	改行	\n	\t	""	\"
ESCAPED BY '\\'（デフォルト）	改行	改行	タブ	"	"
ESCAPED BY '"'	改行	\n	\t	"	\"

 

フィールド内の改行と\nの速度の比較

エスケープ文字はダブルクォーテーションにした方が良いと書きましたが、その場合、フィールド内での改行には\nではなく実際に改行を入れる必要があり、ロード速度が遅くなる可能性があります。

一方、デフォルトのバックスラッシュによるエスケープにした場合、フィールド内の実際の改行と\nはともに改行として扱われます。そこで、この設定において、フィールド内の改行を\nにしたCSVファイルと、実際の改行のままにしたCSVファイルをそれぞれロードしたときに速度に差が出るかを調べました。

テーブル

以下のコマンドで作成しました。

CREATE TABLE posts(name TEXT, post TEXT);

CSVファイル

以下の2種類の300万行のCSVファイルを用いました。

\nで改行するバージョン:

"hoge","hello\nhello\nhello\nhello"
"hoge","hello\nhello\nhello\nhello"
︙

フィールド内に実際に改行を入れたバージョン:

"hoge","hello
hello
hello
hello"
"hoge","hello
hello
hello
hello"
︙

実行コマンド

LOAD DATA
    LOCAL
    INFILE 'post.csv'
    INTO TABLE posts
    FIELDS
        TERMINATED BY ','
        ENCLOSED BY '"'
    (name, post)

結果

ロードにかかった時間は以下の表のようになりました。ここで、各時間はロードを3回行ったときの平均値をとっています。

\nによる改行	18.0秒
実際の改行	13.3秒

 

\nを使ったフィールド内改行は速度面でもメリットはなく、あえて使う意味はないということが分かりました。

おすすめのパラメータ

以上から（ヘッダーのある）CSVファイルをロードするときのおすすめのパラメータは以下になります。

LOAD DATA
    LOCAL
    INFILE 'file_name'
    INTO TABLE tbl_name
    FIELDS
        TERMINATED BY ','
        ENCLOSED BY '"'
        ESCAPED BY '"'
    IGNORE 1 LINES
    (col_name1, col_name2, ...);