ProgrammingSQL講座第3章　SQL構文
第3章　SQL構文

LOAD DATA INFILE構文：データのインポート

LOAD DATA INFILE構文のキーワード

LOAD DATA INFILE構文

LOAD DATA INFILEは、テキストファイルのデータをテーブルに登録します。LOAD DATA INFILEがデフォルトで対応している書式は、フィールド区切りがタブ、行末が改行です。

たとえば、サイト名、URL、IPアドレスというフィールド名があるデータベースにLOAD DATA INFILEを使ってデータを挿入したい場合は、下記のようなファイルを作成します（空白部分はタブで区切られています）。

smart    wwww.rfs.jp    	210.123.**.**
rhythm    www.rhythmfactory.jp      210.123.**.**
spacemonkey    www.spacemonkey.jp    210.123.**.**

上記データのファイル名が"data.txt"、それをsiteというテーブルに挿入するには、下記のような構文になります。

LOAD DATA INFILE "data.txt" INTO TABLE site;

上記の例のような項目の区切りや行末の符号以外のファイルをLOAD DATA文で読み込むこともできます。

LOAD DATA INFILE構文

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL]
INFILE 'ファイル名'
    [REPLACE | IGNORE]
    INTO TABLE テーブル名
    [FIELDS
        [TERMINATED BY '区切り文字']
        [[OPTIONALLY] ENCLOSED BY 'フィールドを囲むキャラクタ']
        [ESCAPED BY 'エスケープシーケンス' ]
    ]
    [LINES TERMINATED BY '\n']
    [IGNORE 数値 LINES]
    [(フィールド名, ...)]

LOAD DATA INFILE構文のキーワード
パラメータ 説明
LOW_PRIORITY LOAD DATAの処理を他の処理よりもプライオリティを下げて行います。
CONCURRENT LOAD DATAの実行中に、他のスレッドがこのテーブルからデータを取り出すことができます。
LOCAL FILE権限なしで、ローカルファイルの読み込みが可能になります。
また、IGNOREと同じように、行の重複エラーがあっても挿入処理が続行されます。
REPLACE 同じユニークキーを持つ既存の行は新しい行で置き換えられます。
IGNORE 既存の行のユニークキーと重複するキーをもつ新しい行は飛ばされます。 
IGNORE 数値 LINES ファイルの先頭にある行を無視するのに使用されます
FIELDS
[OPTIONALLY] ENCLOSED BY		 項目のシングルクォートを制御します。出力の際(SELECT ... INTO OUTFILE)、OPTIONALLY語を省いたなら、全ての項目はENCLOSED BY文字で囲まれます。

項目区切りにコンマを使用して出力。

"1","a string","100.20"
"2","a string containing a , comma","102.20"

OPTIONALLYを指定すると、CHAR項目とVARCHAR項目のみENCLOSED BY文字で囲まれます。

1,"a string",100.20
2,"a string containing a , comma",102.20

ENCLOSED BYで指定したキャラクタが値の両端に現れた場合、その文字は取り去られます。
ESCAPED BY文字が前置きされたENCLOSED BY文字の出現は、現在の値の一部として処理されます。具体的には、あるフィールドがそれ自身、ENCLOSEDBY文字で始まっている場合、フィールド内部で発生する2重のENCLOSED BY文字は、単一のENCLOSED BY文字として処理されます。 

例えば、ENCLOSED BY '"'が指定されると、シングルクォートは以下のように操作されます。

"The ""BIG"" boss" -> The "BIG" boss
The "BIG" boss -> The "BIG" boss
The ""BIG"" boss -> The ""BIG"" boss

ESCAPED BY 特殊文字をどのように書き込んだり読み込んだりするかを制御します。FIELDS
ESCAPED BY'\\'と指定した場合、バックスラッシュ(\)はエスケープ文字として認識されます。

FIELDS ESCAPED BY文字が空でない場合、出力において次のような文字列のプリフィックスに使用されます。

  • FIELDS ESCAPED BY文字 
  • FIELDS [OPTIONALLY] ENCLOSED BY文字
  • FIELDS TERMINATED BY値とLINES
    TERMINATED BY値の最初の文字
  • ASCII 0
    (エスケープ文字の後に続いて実際に書かれる文字はASCII
    '0'で、'ゼロ値'バイトではありません) 

FIELDS ESCAPED BY文字が空であれば、どの文字もエスケープされません。

入力において、FIELDS ESCAPED BY文字が空でない場合、この文字の出現は取り去られ、後続の文字は値の一部としてそのまま受け取られます。例外は、エスケープされた`0'や`N'です(例えば、エスケープ文字が \ である時の\0や\N)。
これらのシーケンスは、ASCII 0、NULLとして処理されます。
LOAD DATA INFILE構文の使用例
USE db1;
LOAD DATA INFILE "./data.txt" INTO TABLE db2.my_table; 

出力の初期値

  • 改行を行の境界とみなす
  • タブ文字によって項目を区切る
  • クォート文字で項目を囲んでいないものとする
  • タブ文字や改行文字及び\の前に\があることにより、それらを値の一部のリテラル文字であるとして処理する。

入力の初期値

  • 項目間にタブ文字を書く
  • いずれのクォート文字でも項目を囲まない
  • \の使用により、項目値の中で使用するタブ文字や改行文字、\をエスケープする
  • 行の終りに改行文字を書く 

LOAD DATA INFILEは、SELECT ... INTO OUTFILEの補完です。データベースからファイルへデータを書き込むには、SELECT ... INTO OUTFILEを使用します。

LOCAL
LOCAL キーワードが指定されれば、ファイルはクライアント・ホストから読み込まれます。

mysqlimportユティリティは、データファイルの読み込みに使用することができます。
--localオプションは、mysqlimportに、クライアント・ホストからデータファイルを読み込ませます。クライアントとサーバが圧縮プロトコルをサポートしていれば、--compressオプションを指定することができます。 

サーバ・ホストにファイルを置く場合

  • 相対パスと共にファイル名が与えられた場合、サーバは、サーバのデータディレクトリ以下からファイルを探します。 
  • ファイル名だけが単に与えられた場合、カレントのデータベースディレクトリを探します。 

完全なパスでファイル名が与えられた場合、サーバはパス名をそのまま使用します。
1つ又は複数の構成要素から成る相対パスと共にファイル名が与えられた場合、サーバは、サーバのデータディレクトリ以下からファイルを探します。
ファイル名だけが単に与えられた場合、サーバは、カレントのデータベースディレクトリを探します。
これらのルールは、ファイルが myfile.txtのように与えられればデータベースディレクトリからファイルが読み出され、 ./myfile.txtのように与えられれば、現在選択しているデータベースのデータディレクトリからファイルが読み出されるという意味であることに注意して下さい。

例えば、以下の LOAD DATA 文は、data.txt ファイルを db1 データベースディレクトリから読みます。なぜなら、db1は現在選択されているデータベースだからです。たとえ、db2 データベースのテーブルに、ファイルから読み込んだデータを挿入するとしても。

以下に示すような構文では、ファイルは db1 データベースディレクトリから読まれます。db2 ではありません。

mysql> USE db1;
mysql> LOAD DATA INFILE "data.txt" INTO TABLE db2.my_table;

使用例

データベースからファイルへデータを書き、それから後でそのファイルからデータベースへデータを読み戻すために、SELECT ... INTO OUTFILE と対に LOAD DATA INFILEを使う場合、双方の項目と行の取扱いに関するオプションは、一致しなければなりません。さもなければ、LOAD DATA INFILE は適切にファイルを処理しないでしょう。

項目をコンマで区切ってファイルへ書き出すために、SELECT ... INTO OUTFILEを使用するとすれば

SELECT * FROM table1 INTO OUTFILE 'data.txt'
	FIELDS TERMINATED BY ','
	FROM ...

コンマ区切りファイルから読み戻すため、正しいステートメントはこうなるでしょう。

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
	FIELDS TERMINATED BY ',';

カンマ( , )区切りと、ダブルクォーテーション（ " ）囲み、行区切りが改行（\n）であれば、次のようになります。 

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
	FIELDS TERMINATED BY ',' ENCLOSED BY '"'
	LINES TERMINATED BY '\n';

LINES TERMINATED BY
リターン文字と改行文字のペアで区切られた行を書き込んだり、このような行を含んだファイルを読み込む場合、LINES TERMINATED BY \r\n を指定します。

項目と行操作オプションが確実に相互作用する事例

  • LINES TERMINATED BYが空文字列でFIELDS TERMINATED BYが空でない場合、各行もまたFIELDS TERMINATED BYで終らせられます。 
  • FIELDS TERMINATED BYとFIELDS ENCLOSED BY値が両方とも空('')の時、(区切られない)固定長行フォーマットが使用されます。固定長行フォーマットでは、項目間に区切り文字列が使用されません。
  • 項目値は、項目の表示幅を使って書き込まれたり、読み込まれます。例えば、ある項目がINT(7)で定義されている場合、項目の値は7文字のフィールドを使って書き込まれます。入力において項目は、7文字の読み込みにより得られます。

FIELDSとLINESオプションによるNULL値の多様な取扱い

  • FIELDSとLINESの初期値のために、出力時にNULLは\Nとして書き込まれ、入力時に\NはNULLとして読み込まれます(ESCAPED BY文字は \ )。
  • FIELDS ENCLOSED BYが空で無い時、定数NULLの項目値はNULL値として読み込まれます。
  • FIELDS ESCAPED BYが空の時、NULLはNULLとして書き込まれます。 
  • 固定長行フォーマット(FIELDS TERMINATED BYとFIELDS ENCLOSED BYがいずれも空の場合)において、NULLは、空白文字列として書き込まれます。

次の例は、persondataテーブルの全ての項目を読み込みます

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

項目リストが指定されていませんから、LOAD DATA INFILEは、入力行がテーブルのそれぞれの項目を含むものと想定します。 

テーブルの一部の項目のみ読み込みたい場合、項目リストを指定します。

LOAD DATA INFILE 'persondata.txt'
	INTO TABLE persondata (col1,col2,...);

テーブル内の項目順と入力ファイルの項目順が異なる場合にも、MySQLにテーブルの項目と入力項目の対応を教えるために、項目リストを指定しなければなりません。 

空のフィールド値は変換されます

  • 文字型の場合、 項目は空文字にセット
  • 数値型の場合、項目は 0 にセット
  • 日付と時刻の型の場合、 項目は 「zero」 の意味する値がセット

TIMESTAMP項目は、項目値にNULL値が指定されていた場合もしくは、項目リストが指定されている時に TIMESTAMP項目がそのリストから除外されていた場合 (最初のTIMESTAMP項目のみ)、現在時刻が設定されるだけです。 
入力行の項目数の方が多い場合、余分な項目は無視され、警告が引き起こされます。 

関連記事