LOAD DATA INFILE 構文

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

LOAD DATA INFILE ステートメントは高スピードでテキスト ファイルからテーブルに行を読み込みます。ファイル名は直定数文字列として与えられなければいけません。

LOAD DATA INFILE は SELECT ... INTO OUTFILE の補数です。（詳しくは 項 「SELECT 構文」 をご確認ください。）テーブルからファイルにデータを書き込むには、SELECT ... INTO OUTFILE を利用してください。テーブルにファイルをリード バックするには、LOAD DATA INFILE を利用してください。両方のステートメントに対して FIELDS と LINES 条項の構文は同じです。条項は両方とも任意ですが、もし両方が指定された場合 FIELDS は LINES に先行しなければいけません。

INSERT 対 LOAD DATA INFILE の性能と、LOAD DATA INFILE のスピード アップの更なる情報については、項 「INSERTステートメントの速度」 を参照してください。

character_set_database システム変数によって指示された文字セットはかつてファイルの中の情報を解明していました。SET NAMES と character_set_client の設定はインプットの解明に影響を与えません。

現在は ucs2 文字セットを利用するデータ ファイルをロードするのは不可能だという事に気をつけてください。

MySQL 5.1.6 以降のバージョンでは、character_set_filesystem システム変数は、ファイル名の解明をコントロールします。

mysqlimport ユーティリティを利用する事でデータ ファイルをロードする事もできます。これは、サーバに LOAD DATA INFILE ステートメントを送信する事で機能します。--local オプションは mysqlimport がクライアント ホストからデータファイルを読み込むよう働きかけます。もしクライアントとサーバが圧縮されたプロトコルをサポートするなら、スピードが遅いネットワークにより良い性能を得る為に --compress オプションを指定する事ができます。詳しくは 項7.14. 「mysqlimport — データインポートプログラム」 を参照してください。

もし LOW_PRIORITY を利用すると、別のクライアントがテーブルからの読み込みをしなくなるまで、LOAD DATA の実行が遅れます。

並列挿入の条件を満たす(フリーブロックが途中に無いという事) MyISAM テーブルと共に CONCURRENT を指定すると、LOAD DATA の実行中に他のスレッドがテーブルからデータを検索する事ができます。もし他のスレッドがテーブルを同時に利用していなくても、このオプションを利用する事は LOAD DATA の性能に少しだけ影響を与えます。

もし LOCAL キーワードが指定されたら、それは接続の最後にクライアントに関して解明されます。

これらのルールは、非 LOCAL の場合、 ./myfile.txt としてのファイル名はサーバーのデータ ディレクトリから読まれ、その一方、myfile.txt としてのファイル名はデフォルト データベースのデータベース ディレクトリから読み込まれるという事を意味しますので、注意してください。例えば、もし db1 がデフォルト データベースなら、ステーメントが db2 データベース内のテーブルにファイルを明示的にロードしたとしても、次の LOAD DATA ステートメントが db1 のデータベース ディレクトリからファイル data.txt を読み込みます。

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

ウィンドウズのパス名は、バックスラッシュではなくフォーワードスラッシュで指定されます。もしバックスラッシュを利用すると、それらを2倍にする必要があります。

安全上の理由で、サーバ上にあるテキスト ファイルを読み込む時、ファイルはデータベース ディレクトリ上にあるか、全てによって読み込み可能である必要があります。また、 サーバ ファイル上で LOAD DATA INFILE を利用する時は、FILE 権限が必要です。詳しくは 項 「MySQL 提供の権限」 を参照してください。

LOCAL を利用すると、ファイルのコンテンツはクライアントからサーバに、その接続全体に送られなければいけないので、サーバがファイル ディレクトリにアクセスするのを許可するのよりも少し速度が遅くなります。反対に、ローカル ファイルをロードするのに FILE 特権は必要ありません。

LOCAL は、サーバとクライアントの両方が、これを許容できる場合のみ機能します。例えば、もし mysqld が --local-infile=0 と共に開始された場合、LOCAL は機能しません。詳しくは 項 「LOAD DATA LOCAL のセキュリティ関連事項」 を参照してください。

Unix上では、もしパイプから読み込む為に LOAD DATA が必要であれば、次のテクニックを利用する事ができます。(ここでは、テーブルの中に / ディレクトリのリストをロードします。)

mkfifo /mysql/db/x/x
chmod 666 /mysql/db/x/x
find / -ls > /mysql/db/x/x &
mysql -e "LOAD DATA INFILE 'x' INTO TABLE x" x

ロードされるデータを生成するコマンドと mysql を、別々のターミナルのどちらかで起動させる、または背景でデータ生成プロセスを起動させなければいけない (上記の例で表されているように)、という事に注意してください。もしこれを実行しないと、パイプはデータが mysql プロセスによって読み込まれるまでブロックします。

REPLACE と IGNORE キーワードは、固有のキー値上に既存行を複製するインプット行の扱いをコントロールします。

もしロード操作中に外部キー制約を無視したければ、LOAD DATA を実行する前に SET FOREIGN_KEY_CHECKS=0 ステートメントを発行する事ができます。

もし空の MyISAM テーブル上でLOAD DATA INFILE を利用すると、別のバッチに全ての非ユニーク インデックスが作成されます。(REPAIR TABLE に関して)通常この操作は、インデックスが多くある時に LOAD DATA INFILE のスピードを速くします。いくつかの極端な場合では、テーブルにファイルをロードする前に ALTER TABLE ... DISABLE KEYS を利用してインデックスをオフにしたり、ファイルをロードした後にインデックスを再作成する為に ALTER TABLE ... ENABLE KEYS を利用する事で、インデックスをさらに速く作成できます。詳しくは 項 「INSERTステートメントの速度」 を参照してください。

LOAD DATA INFILE と SELECT ... INTO OUTFILE ステートメントの両方に対して、FIELDS と LINES 条項の構文は同じです。条項は両方とも任意ですが、もし両方が指定された場合 FIELDS は LINES に先行しなければいけません。

もし FIELDS 条項を指定すると、少なくてもどれか1つを指定する必要はありますが、その各サブ条項 (TERMINATED BY、[OPTIONALLY] ENCLOSED BY、そして ESCAPED BY) もまた任意になります。

もし FIELDS 条項を指定しなければ、デフォルトは、このように書き込んだ場合と同じようになります。

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

もし FIELDS 条項を指定しなければ、デフォルトは、このように書き込んだ場合と同じようになります。

LINES TERMINATED BY '\n' STARTING BY ''

言い換えると、インプットを読み込む時、デフォルトは LOAD DATA INFILE が次のように機能するよう働きかけるという事です。

反対に、デフォルトはアウトプットを書き込む時に SELECT ... INTO OUTFILE が次のように機能するよう働きかけます。

バックスラッシュは、MySQL の中では文字列内の拡張文字ですので、FIELDS ESCAPED BY '\\' を書き込むには、単一バックスラッシュだと認識させる為に2つのバックスラッシュを指定しなければいけません。

注意:もしウィンドウズ システム上でテキスト ファイルを生成したら、ウィンドウズのプログラムは通常ラインのターミネータとして2つの文字を利用するので、ファイルを正確に読み込む為には LINES TERMINATED BY '\r\n' を利用しなければいけないでしょう。WordPad のようないくつかのプログラムは、ファイルを書き込む時 \r をライン ターミネータとして利用するでしょう。そのようなファイルを読み込む時は、LINES TERMINATED BY '\r' を利用してください。

もし読み込みたいライン全てに、共通の無視したいプリフィックスがあれば、そのプリフィックスと、その前にある全ての物 を飛び越える為に LINES STARTING BY 'prefix_string' を利用する事ができます。もしラインがプリフィックスを持たなければ、ライン全体がスキップされます。このステートメントを発行すると仮定すると：

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test
  FIELDS TERMINATED BY ','  LINES STARTING BY 'xxx';

もしデータ ファイルがこのようになっていたら:

xxx"abc",1
something xxx"def",2
"ghi",3

行は結果として ("abc",1) と ("def",2) のようになります。ファイル内の3行目は、プリフィックスを含まないのでスキップされます。

IGNORE number LINES オプションはファイルの先頭のラインを無視する為に利用する事ができます。例えば、カラム名を含む冒頭のヘッダ ラインを飛び越える為に IGNORE 1 LINES を利用する事ができます。

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

データベースからのデータをファイルに書き込み、その後そのファイルをデータベースに読み返す為に LOAD DATA INFILE と並行して SELECT ... INTO OUTFILE を利用する時、両方のステートメントの field- と line-handling オプションはマッチする必要があります。そうでなければ、LOAD DATA INFILE はファイルの内容を正確に解釈しません。カンマで区切られたフィールドを持つファイルを書き込む為に、SELECT ... INTO OUTFILE を利用すると仮定してください。

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

カンマで区切られたファイルを読み返す為の正しいステートメントは次のようになります。

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

もし代わりに、次に表されているステートメントを利用してファイルを読み込もうとすると、LOAD DATA INFILE にフィールドの間にあるタブを探すよう指示を出すので、そのステートメントは機能しません。

LOAD DATA INFILE 'data.txt' INTO TABLE table2
  FIELDS TERMINATED BY '\t';

各インプット ラインが単一フィールドとして解釈されるという結果がよく出されます。

LOAD DATA INFILE は、外部ソースから得たファイルを読見込むのに利用する事ができます。例えば、多くのプログラムは、ラインがカンマで区切られたフィールドを持ち、2つの引用句で囲まれている というような、カンマで区切られた値（CSV）のフォーマットでデータをエクスポートする事ができます。もしそのようなファイルの中のラインが改行で終了していたら、ここに表されているステートメントはファイルをロードする為に利用するであろう field- と line-handling オプションを説明します。

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

field- または line-handling オプションは空の文字列を指定する事ができます。 ('')もし空でなければ、FIELDS [OPTIONALLY] ENCLOSED BY と FIELDS ESCAPED BY 値は単一文字という事になります。FIELDS TERMINATED BY、LINES STARTING BY、そして LINES TERMINATED BY 値は一文字以上になり得ます。例えば、キャリッジ リターンと改行のペアで終わっているラインを書いたり、そのようなラインを含むファイルを読み込む為には、 LINES TERMINATED BY '\r\n' 条項を指定してください。

%% で成り立つラインによって区切られているジョークを含むファイルを読み込むには、これを実行する事ができます。

CREATE TABLE jokes
  (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  joke TEXT NOT NULL);
LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes
  FIELDS TERMINATED BY ''
  LINES TERMINATED BY '\n%%\n' (joke);

FIELDS [OPTIONALLY] ENCLOSED BY はフィールドの引用句をコントロールします。アウトプットには(SELECT ... INTO OUTFILE)、もし OPTIONALLY を省略すると全てのフィールドが ENCLOSED BY 文字によって囲まれます。そのようなアウトプット(フィールド デリミタとしてカンマを利用している)の例がここに表されています。

"1","a string","100.20"
"2","a string containing a , comma","102.20"
"3","a string containing a \" quote","102.20"
"4","a string containing a \", quote and comma","102.20"

もし OPTIONALLY を指定すると ENCLOSED BY 文字は (CHAR、BINARY、TEXT、または ENUM のような) 文字列データタイプを持つカラムからの値を囲む為だけに利用されます。

1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a \" quote",102.20
4,"a string containing a \", quote and comma",102.20

フィールド値内の ENCLOSED BY 文字の発生は、ESCAPED BY 文字と共にそれらをプリフィックスする事で拡張する事ができるという事を覚えておいて下さい。また、もし空の ESCAPED BY 値を指定すると、LOAD DATA INFILE で正しく読み込む事ができないアウトプットを気づかずに生成してしまう可能性がある事も覚えておいて下さい。例えば、もし拡張文字が空なら、表示されただけの先行するアウトプットは次のように現れます。4つ目のラインの2つ目のフィールドの引用句の後に、フィールドを(誤って)終了させるカンマが含まれている事を確認してください。

1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a " quote",102.20
4,"a string containing a ", quote and comma",102.20

インプットに関しては、もし ENCLOSED BY 文字があれば、フィールド値の最後から剥ぎ取られます。(OPTIONALLY が指定されているかどうかは関係なくこれは事実です。OPTIONALLY はインプットの解釈に対して影響しません。)ESCAPED BY 文字に先行された ENCLOSED BY 文字の発生は、現在のフィールド値の一部として解釈されます。

もしフィールドが ENCLOSED BY 文字で始まると、その文字のインスタンスはフィールド、またはライン TERMINATED BY シーケンスが後に続いている場合のみ、そのフィールド値を終了させていると判断されます。曖昧さを防ぐ為に、フィールド値内の ENCLOSED BY 文字の発生を2倍にすると、その文字の単一インスタントとして解釈されます。例えば、もし ENCLOSED BY '"' が指定されると、引用句はここに表されているように扱われます。

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

FIELDS ESCAPED BY は、特別な文字をどのように書き込み、読み込むのかをコントロールします。もし FIELDS ESCAPED BY 文字が空でなければ、それはアウトプット上に次の文字をプリフィックスする為に利用されます。

もし FIELDS ESCAPED BY 文字が空なら、文字が拡張される事はなく、NULL は \N ではなく NULL としてアウトプットされます。特に、もしデータ中のにフィールド値が先ほどのリストの中の文字を含んでいる場合は、空の拡張文字を指定するのは良い考えではないかも知れません。

インプットに関しては、もし FIELDS ESCAPED BY 文字が空でなければ、その文字の発生は削除され、後続文字はフィールド値の一部として文字通りに取り込まれます。　例外は、拡張された ‘0’ または ‘N’ です。(例えば、もし拡張文字が ‘\’ なら \0 または \N です。)これらのシーケンスは、ASCII NUL (ゼロの値のバイト) と NULL として解釈されます。NULL の扱いのルールについてはこのセクションの後半で説明します。

‘\’-escape 構文の更なる情報については、項8.1. 「リテラル値」 を参照してください。

特定の場合には、field- と line-handling オプションは互いに影響しあいます。

NULL 値の扱いは、利用中の FIELDS と LINES オプションによって変わります。

NULL を NOT NULL カラムにロードしようとすると、カラムのデータ タイプと警告の為の暗黙のデフォルト値の割り当て、またはストリクト SQL モードでのエラーが発生します。暗黙のデフォルト値に関しては 項 「データタイプデフォルト値」 で説明されています。

LOAD DATA INFILE によってサポートされない場合もあります。

次の例は、persondata テーブルの全てのカラムをロードします。

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

デフォルトでは、LOAD DATA INFILE ステートメントの最後にカラムリストがない場合、インプット ラインが各テーブル カラムに対してフィールドを含みます。もし1つのテーブルのカラムをいくつかロードしたければ、カラム リストを指定してください。

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

インプット ファイル内のフィールドの順番が、テーブル内のカラムの順番と異なる場合は、カラム リストも指定しなければいけません。そうでなければ、MySQL はインプット フィールドとテーブル カラムをどのようにマッチさせるのか判断できません。

カラム リストはカラム名かユーザ変数のどちらかを含みます。ユーザ変数を利用すると、カラムに結果を割り当てる前に SET 条項で値を変換する事ができます。

SET 条項の中のユーザ変数は、いくつかの方法で利用する事ができます。次の例は、t1.column1 の値に対して直接最初のインプット カラムを利用し、そして2番目のインプット カラムを t1.column2 の値に利用される前に、分割操作に影響されるユーザ変数に割り当てます。

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @var1)
  SET column2 = @var1/100;

SET 条項は、インプット ファイルから派生した物ではない値を提供する事ができます。次のステートメントは、現在の日付と時間に column3 を設定する事ができます。

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, column2)
  SET column3 = CURRENT_TIMESTAMP;

インプット値をユーザ変数に割り当て、変数をテーブル カラムに割り当てない事で、インプット値を廃棄する事ができます。

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @dummy, column2, @dummy, column3);

カラム/変数リストと SET 条項の利用は、次の制約を受けます。

インプット ラインを処理している時、LOAD DATA はそれをフィールドに分割し、カラム/変数リストと SET 条項があれば、それらに従って値を利用します。そして、結果としてできた行がテーブルに挿入されます。もしテーブルに BEFORE INSERT か AFTER INSERT トリガがあれば、それらはそれぞれ、行の挿入前か挿入後に起動されます。

もしインプット ラインのフィールドが多すぎたら、余分なフィールドは無視され、警告数が増加されます。

もしインプット ラインのフィールドが少なすぎたら、インプットフィールドがないテーブル カラムがそれらのデフォルト値として設定されます。デフォルト値の割り当てについては 項 「データタイプデフォルト値」 で説明しています。

空のフィールド値は、フィールド値がない場合とは異なって解釈されます。

これらは、INSERT や UPDATE ステートメントの中で、空の文字列を明示的に文字列、数値、または数値と時間タイプに割り当てた時の結果と同じ値です。

TIMESTAMP カラムは、カラムに NULL 値がある時(\N)、または、TIMESTAMP カラムのデフォルト値が現在のタイム スタンプの時だけ現在の日付と時間に設定され、カラムのデフォルト値はフィールド リストが指定された時に削除されます。

LOAD DATA INFILE は全てのインプットを文字列とみなしますので、INSERT ステートメントと同じ方法で、ENUM か SET カラムにも数値の値を利用する事ができます。全ての ENUM と SET 値は文字列として指定される必要があります。

BIT 値はバイナリ表記を利用してロードする事はできません。(例えば b'011010')これに対処するには、値を標準整数として指定し、それらの変換には、MySQL が数値タイプの変換を行う為に、SET 条項を利用し、そしてそれらを BIT カラムに正確にロードしてください。

shell> cat /tmp/bit_test.txt
2
127
shell> mysql test
mysql> LOAD DATA INFILE '/tmp/bit_test.txt'
    -> INTO TABLE bit_test (@var1) SET b= CAST(@var1 AS SIGNED);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT BIN(b+0) FROM bit_test;
+----------+
| bin(b+0) |
+----------+
| 10       |
| 1111111  |
+----------+
2 rows in set (0.00 sec)

LOAD DATA INFILE ステートメントが終了する時、次のフォーマットで情報文字列を返します。

Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

もしC API を利用していれば、mysql_info() 関数を呼び出す事で、ステートメントの情報を得る事ができます。詳しくは 項35. 「mysql_info()」 を参照してください。

LOAD DATA INFILE もまた、インプット行のフィールドが多すぎる、または少なすぎる時に警告を生成するという事を除き、値が INSERT ステートメントを通して挿入された時と同じ状況下で警告が発生します。(項 「INSERT 構文」 を参照してください。) 警告はどこにも格納されません。警告数は、全てが順調であるかどうかを示す為だけに利用されます。

失敗した内容の情報を表す最初の max_error_count 警告のリストを得る為に、SHOW WARNINGS を利用する事ができます。詳しくは 項31. 「SHOW WARNINGS 構文」 を参照してください。