概要

MySQLでCSVファイルをインポートする手順。

要点は以下の通り。

• CSVファイルはUTF-8(BOMなし)で準備する
• 改行コードに注意
• MySQLサーバーとクライアントの両方でローカルのファイル入力の許可が必要
  • クライアントはログイン時のオプションで、サーバー側はグローバル変数で設定する

CSVファイルの準備

CSVファイルを準備し、MySQLで読み込む場所に配置する。CSV作成時の型と文字コードに注意。

型に関する注意点

数字だが001のように表現したい場合は文字列として扱う。

• Excelの列の形式で適切な型を設定する

日付・時刻形式の空白値に注意。

• 日付・時刻値の列に空のデータがある場合にMySQLのdate/time/datetimeで読み込むと、NULL値ではなく'0000-00-00'や'0000-00-00 00:00:00'で埋められてしまう

文字コードの注意点

CSVファイルはUTF-8のBOMなしで準備する。

• ExcelのシートをCSVファイルとして保存すると、UTF-8(BOMあり)になるので、メモ帳などで開いてBOMなしで保存し直す必要がある

MySQLログイン時のオプション

MySQLにログインするクライアントで、ローカルのファイル入力を許可する必要がある。

ログイン時に以下のオプションのいずれかを付ける

• --enebale-local-infile
• --local_infile=1またはon

MySQLログイン後の設定

グローバル変数の確認

SELECTかSHOW GLOBAL VARIABLESでlocal_infileの値を確認。この値が0/OFFの場合は許可されていない。以下のいずれかで確認できる。

SELECT @@local_infile;

SHOW GLOBAL VARIABLES LIKE 'local_infile';

グローバル変数の設定

SET GLOBALでlocal_infileの値を1またはONにセットする。

SET GLOBAL local_infile=1またはON;

インポート実行

テーブルの準備

CSVの各カラムに対応した構造で、空のテーブルを準備しておく。

MySQLのtimestamp型の2038年問題に注意。

LOAD DATAの実行

SQLの例

CSVファイルをテーブルにインポートするSQLの例。

この例では、WindowsのようにCRLFで改行されたファイルを読み込み、区切り文字がカンマのCSVとして、先頭1行を飛ばして読み込む。

FIELDS

TERMINATED BY

CSV保存時に選んだ区切り文字を指定する。1文字である必要はない。デフォルトは'\t'。

ENCLOSED BY

フィールドを囲み文字を1文字で指定する。デフォルトは''(空)で、フィールドが区切り文字で囲まれていることを期待しない。OPTIONALLYを付けると文字列のみ囲み文字で囲まれていることを期待する。

ESCAPED BY

エスケープ文字を1文字で指定する。エスケープ文字に続く文字がエスケープシーケンスとして解釈される。デフォルトは'\\'で'\'一文字を表している。

LINES

STARTING BY

指定したprefix以降のみ行として扱い、それ以外はスキップする。prefixは1文字でなくてもよい。デフォルトは''でprefixを設定していない(常に行頭から読み込む)。

TERMINATED BY

行末の文字列を指定する。デフォルトは'\n'。

IGNORE

IGNORE 数値 lines/rowsで、先頭の指定した数の行／列を読み込まない。