LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_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]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

LOAD DATA INFILE ステートメントは高スピードでテキストファイルからテーブルに行を読み込みます。ファイル名は、リテラル文字列として指定される必要があります。

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

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

character_set_database システム変数によって指示されたキャラクタセットはかつてファイルの中の情報を解明していました。SET NAMES と character_set_client の設定はインプットの解明に影響を与えません。入力ファイルの内容でデフォルトとは異なるキャラクタセットを使用する場合は、通常、CHARACTER SET 節 (MySQL 5.1.17 で使用可能) を使用してそのファイルのキャラクタセットを指定することをお勧めします。binary のキャラクタセットでは「変換は指定されません」。

LOAD DATA INFILE は、フィールド値がロードされるカラムのデータ型には関係なく、ファイル内のすべてのフィールドに同じキャラクタセットが割り当てられていると解釈します。ファイルの内容が正しく解釈されるように、そのファイルが正しいキャラクタセットで書き込まれていることを確認する必要があります。たとえば、mysqldump -T を使用するか、または mysql で SELECT ... INTO OUTFILE ステートメントを発行してデータファイルを書き込む場合は、LOAD DATA INFILE でファイルがロードされるときに使用されるキャラクタセットで出力が書き込まれるように、mysqldump または mysql で必ず --default-character-set オプションを使用してください。

現在は、ucs2、utf16、または utf32 キャラクタセットを使用するデータファイルをロードできないことに注意してください。

MySQL 5.1.6 では、character_set_filesystem システム変数によってファイル名の解釈が制御されます。

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

もし LOW_PRIORITY を利用すると、別のクライアントがテーブルからの読み込みをしなくなるまで、LOAD DATA の実行が遅れます。これは、テーブルレベルロックのみを使用するストレージエンジン (MyISAM、MEMORY、MERGE) にのみ影響を与えます。

並列挿入の条件を満たす (フリーブロックが途中にないということ) MyISAM テーブルと共に CONCURRENT を指定すると、LOAD DATA の実行中にほかのスレッドがテーブルからデータを検索することができます。このオプションを使用することで、たとえほかのスレッドが同時にテーブルを使用しているとしても、LOAD DATA のパフォーマンスに幾分かの影響が与えられます。

CONCURRENT は、ステートメントベースのレプリケーションを使用しているときは複製されません。ただし、行ベースのレプリケーションを使用しているときは複製されます。詳細については、Replication and LOAD DATA INFILE を参照してください。

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

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

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

Windows パス名は、バックスラッシュではなく、スラッシュを使用して指定されます。もしバックスラッシュを利用すると、それらを 2 倍にする必要があります。

安全上の理由で、サーバー上にあるテキストファイルを読み込むとき、ファイルはデータベースディレクトリ上にあるか、すべてによって読み込み可能である必要があります。また、サーバーファイル上で LOAD DATA INFILE を利用するときは、FILE 権限が必要です。詳しくはPrivileges Provided by MySQLを参照してください。LOCAL 以外のロード操作では、secure_file_priv システム変数が空以外のディレクトリ名に設定されている場合、ロードされるファイルはそのディレクトリ内に存在する必要があります。

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

LOCAL を使用しなければ、デフォルトの動作は IGNORE が指定されたときと同じです。これは、サーバーは操作の最中にファイルの送信を中止することができないからです。IGNORE は、この節のあとの方でさらに詳細に説明します。

LOCAL は、サーバーとクライアントの両方が、これを許容できる場合のみ機能します。たとえば、もし mysqld が --local-infile=0 と共に開始された場合、LOCAL は機能しません。Security Issues with 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 を利用することで、インデックスをさらに速く作成できます。項4.2.21. 「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 つのバックスラッシュを指定しなければいけません。

もし読み込みたいラインすべてに、共通の無視したい接頭辞があれば、その接頭辞と、その前にあるすべてのもの を飛び越えるために 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';

入力値が必ずしも引用符内に囲まれていない場合は、ENCLOSED BY キーワードの前に OPTIONALLY を使用します。

field- または line-handling オプションは空の文字列を指定することができます。'')もし空でなければ、FIELDS [OPTIONALLY] ENCLOSED BY と FIELDS ESCAPED BY 値は単一文字ということになります。FIELDS TERMINATED BY、LINES STARTING BY、そして LINES TERMINATED BY 値は 2 文字以上になり得ます。たとえば、キャリッジリターンとラインフィードのペアで終わっているラインを書いたり、そのようなラインを含むファイルを読み込むためには、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 文字が空でなければ、その文字の発生は削除され、後続文字はフィールド値の一部として文字通りに取り込まれます。最初の文字がエスケープ文字になっている、例外である一部の 2 文字シーケンス。これらのシーケンスを (エスケープ文字に 「\」 を使用して) 次の表に示します。NULL の扱いの規則についてはこの節の後半で説明します。

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

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

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

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

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 トリガーがあれば、それらはそれぞれ、行の挿入前か挿入後に起動されます。

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

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

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

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

TIMESTAMP カラムが現在の日付と時刻に設定されるのは、カラムに NULL 値 (つまり、\N) が存在し、そのカラムが NULL 値を許可するように宣言されていない場合か、または TIMESTAMP カラムのデフォルト値が現在のタイムスタンプであり、フィールドリストが指定されたときにカラムがフィールドリストから省略されている場合のみです。

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

BIT 値はバイナリ表記を利用してロードすることはできません。この問題を回避するには、値を通常の整数として指定し、SET 節を使用してそれらの値を変換することにより、MySQL によって数値型の変換が実行されて 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 UNSIGNED);
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() 関数を呼び出すことで、ステートメントの情報を得ることができます。mysql_info() を参照してください。

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

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