Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
EPUB - 7.5Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


13.2.6 LOAD DATA INFILE 構文

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

LOAD DATA INFILE ステートメントは、非常に高速にテキストファイルからテーブルに行を読み取ります。LOAD DATA INFILE は、SELECT ... INTO OUTFILE を補完するものです。(セクション13.2.9.1「SELECT ... INTO 構文」を参照してください。)テーブルからファイルにデータを書き込むには、SELECT ... INTO OUTFILE を使用します。そのファイルをテーブルに読み戻すには、LOAD DATA INFILE を使用します。FIELDS および LINES 句の構文は、両方のステートメントで同じです。どちらの句もオプションですが、両方が指定される場合は、FIELDSLINES の前に指定する必要があります。

mysqlimport ユーティリティーを使用してデータファイルをロードすることもできます。これは、LOAD DATA INFILE ステートメントをサーバーに送信することによって動作します。--local オプションを指定すると、mysqlimport は、クライアントホストからデータファイルを読み取ります。クライアントとサーバーが圧縮されたプロトコルをサポートしている場合は、--compress オプションを指定すると、低速ネットワーク経由のパフォーマンスを向上させることができます。セクション4.5.5「mysqlimport — データインポートプログラム」を参照してください。

INSERTLOAD DATA INFILE の効率の比較、および LOAD DATA INFILE の高速化の詳細は、セクション8.2.2.1「INSERT ステートメントの速度」を参照してください。

ファイル名は、リテラル文字列として指定する必要があります。Windows では、パス名内のバックスラッシュをスラッシュまたは二重のバックスラッシュとして指定します。character_set_filesystem システム変数は、ファイル名の解釈を制御します。

MySQL 5.6.2 以降では、LOAD DATA は、パーティション、サブパーティション、またはその両方の 1 つ以上の名前のカンマ区切りリストを含む PARTITION オプションを使用した明示的なパーティション選択をサポートしています。このオプションが使用されているとき、リストで指定されているいずれかのパーティションまたはサブパーティションにファイルからの行を挿入できない場合、このステートメントは Found a row not matching the given partition set エラーで失敗します。詳細は、セクション19.5「パーティション選択」を参照してください。

テーブルロックを採用したストレージエンジン (MyISAM など) を使用しているパーティション化されたテーブルの場合、LOAD DATA はどのパーティションロックも削除できません。これは、行レベルロックを採用したストレージエンジン (InnoDB など) を使用しているテーブルには適用されません。詳細は、セクション19.6.4「パーティショニングとロック」を参照してください。

サーバーは、character_set_database システム変数によって示されている文字セットを使用してファイル内の情報を解釈します。SET NAMES や、character_set_client の設定は入力の解釈に影響を与えません。入力ファイルの内容にデフォルトとは異なる文字セットが使用されている場合は、通常、CHARACTER SET 句を使用してそのファイルの文字セットを指定することをお勧めします。binary の文字セットは、変換なしを指定します。

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

注記

ucs2utf16utf16le、または utf32 文字セットを使用するデータファイルはロードできません。

LOW_PRIORITY を使用した場合、LOAD DATA ステートメントの実行は、ほかのどのクライアントもそのテーブルから読み取らなくなるまで遅延されます。これは、テーブルレベルロックのみを使用するストレージエンジン (MyISAMMEMORY、および MERGE) にのみ影響を与えます。

並列挿入の条件を満たす (つまり、途中に空きブロックが含まれていない) MyISAM テーブルで CONCURRENT を指定すると、ほかのスレッドは LOAD DATA の実行中にそのテーブルからデータを取得できます。このオプションは、そのテーブルがほかのスレッドから同時に使用されていない場合でも、LOAD DATA のパフォーマンスに少し影響を与えます。

行ベースのレプリケーションでは、CONCURRENT は MySQL バージョンにかかわらずレプリケートされます。ステートメントベースのレプリケーションでは、CONCURRENT は MySQL 5.5.1 より前ではレプリケートされません (Bug #34628 を参照してください)。詳細は、セクション17.4.1.17「レプリケーションと LOAD DATA INFILE」を参照してください。

LOCAL キーワードは、あとで説明されているように、ファイルの予測される場所やエラー処理に影響を与えます。LOCAL は、サーバーとクライアントの両方がそれを許可するように構成されている場合にのみ機能します。たとえば、mysqld--local-infile=0 で起動された場合、LOCAL は機能しません。セクション6.1.6「LOAD DATA LOCAL のセキュリティーの問題」を参照してください。

LOCAL キーワードは、ファイルが見つかることが予測される場所に影響を与えます。

  • LOCAL が指定されている場合、ファイルはクライアントホスト上のクライアントプログラムによって読み取られ、サーバーに送信されます。このファイルは、その正確な場所を指定するためにフルパス名として指定できます。相対パス名として指定されている場合、その名前は、クライアントプログラムが起動されたディレクトリを基準にして解釈されます。

    LOCALLOAD DATA とともに使用している場合は、そのファイルのコピーがサーバーの一時ディレクトリ内に作成されます。これは tmpdir または slave_load_tmpdir の値によって決定されるディレクトリではなく、オペレーティングシステムの一時ディレクトリであり、MySQL Server では構成できません。(システムの一時ディレクトリは通常、Linux システムでは /tmp、Windows では C:\WINDOWS\TEMP です。)このディレクトリ内にコピーのための十分な領域がないと、LOAD DATA LOCAL ステートメントが失敗する場合があります。

  • LOCAL が指定されていない場合、ファイルはサーバーホスト上にある必要があり、直接サーバーによって読み取られます。サーバーは、次のルールを使用してファイルを見つけます。

    • ファイル名が絶対パス名である場合、サーバーはそれを指定されたとおりに使用します。

    • ファイル名が 1 つ以上の先行コンポーネントを含む相対パス名である場合、サーバーは、サーバーのデータディレクトリを基準にしてファイルを検索します。

    • 先行コンポーネントを含まないファイル名が指定されている場合、サーバーは、デフォルトデータベースのデータベースディレクトリ内でそのファイルを探します。

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

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

セキュリティー上の理由から、サーバー上にあるテキストファイルを読み取る場合、そのファイルはデータベースディレクトリ内に存在するか、またはすべてのユーザーから読み取り可能のどちらかである必要があります。また、サーバーファイルに対して LOAD DATA INFILE を使用するには、FILE 権限が必要です。セクション6.2.1「MySQL で提供される権限」を参照してください。LOCAL 以外のロード操作では、secure_file_priv システム変数が空以外のディレクトリ名に設定されている場合、ロードされるファイルはそのディレクトリ内に存在する必要があります。

LOCAL を使用すると、クライアントが接続を経由してファイルの内容をサーバーに送信する必要があるため、サーバーが直接ファイルにアクセスできるようにした場合より少し遅くなります。その一方で、ローカルファイルをロードするために FILE 権限は必要ありません。

LOCAL はまた、エラー処理にも影響を与えます。

  • LOAD DATA INFILE では、データ解釈や重複キーのエラーによって操作が終了します。

  • LOAD DATA LOCAL INFILE では、操作の最中にファイルの転送を停止する方法がサーバーにはないため、データ解釈や重複キーのエラーは警告になり、操作は続行されます。重複キーエラーについては、これは IGNORE が指定されている場合と同じです。IGNORE については、このセクションのあとの方でさらに詳細に説明されています。

REPLACE および IGNORE キーワードは、一意のキー値に関して既存の行を複製する入力行の処理を制御します。

  • REPLACE を指定した場合は、入力行によって既存の行が置き換えられます。つまり、主キーまたは一意のインデックスに関して既存の行と同じ値を持つ行のことです。セクション13.2.8「REPLACE 構文」を参照してください。

  • IGNORE を指定した場合は、一意のキー値に関して既存の行を複製する行は破棄されます。

  • どちらのオプションも指定しない場合、その動作は LOCAL キーワードが指定されているかどうかによって異なります。LOCAL が指定されていない場合は、重複キー値が見つかるとエラーが発生し、テキストファイルの残りは無視されます。LOCAL が指定されている場合、デフォルトの動作は 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 を使用してインデックスを再作成することによって、インデックスをさらに高速に作成できます。セクション8.2.2.1「INSERT ステートメントの速度」を参照してください。

FIELDS および LINES 句の構文は、LOAD DATA INFILESELECT ... INTO OUTFILE の両方のステートメントで同じです。どちらの句もオプションですが、両方が指定される場合は、FIELDSLINES の前に指定する必要があります。

FIELDS 句を指定する場合は、その各サブ句 (TERMINATED BY[OPTIONALLY] ENCLOSED BY、および ESCAPED BY) もオプションです。ただし、そのうちの少なくとも 1 つを指定する必要があります。

FIELDS または LINES 句を指定しない場合、そのデフォルトは、次を記述した場合と同じです。

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

(バックスラッシュは SQL ステートメントの文字列内の MySQL のエスケープ文字であるため、リテラルバックスラッシュを指定するには、1 つのバックスラッシュとして解釈される値に対して 2 つのバックスラッシュを指定する必要があります。エスケープシーケンス '\t''\n' は、それぞれタブと改行文字を指定します。)

つまり、デフォルトでは、入力を読み取るとき LOAD DATA INFILE は次のように機能します。

  • 改行の位置にある行の境界を探します。

  • どの行プリフィクスもスキップしません。

  • タブの位置で行をフィールドに分割します。

  • フィールドが引用文字で囲まれていることを期待しません。

  • 前にエスケープ文字\がある文字をエスケープシーケンスとして解釈します。たとえば、\t\n、および\\はそれぞれ、タブ、改行、およびバックスラッシュを示します。エスケープシーケンスの完全なリストについては、あとの FIELDS ESCAPED BY の説明を参照してください。

逆に、デフォルトでは、出力を書き込むとき SELECT ... INTO OUTFILE は次のように機能します。

  • フィールド間にタブを書き込みます。

  • フィールドを引用文字で囲みません。

  • \を使用して、フィールド値の中に現れるタブ、改行、または\のインスタンスをエスケープします。

  • 行の最後に改行を書き込みます。

注記

Windows システム上でテキストファイルを生成した場合、Windows プログラムは通常、行ターミネータとして 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;

SELECT ... INTO OUTFILELOAD DATA INFILE を連携して使用してデータベースからファイルにデータを書き込み、あとでそのファイルを元のデータベースに読み取る場合は、両方のステートメントのフィールド処理と行処理のオプションが一致している必要があります。そうしないと、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';

その結果、各入力行が 1 つのフィールドとして解釈される可能性があります。

LOAD DATA INFILE を使用すると、外部ソースから取得されたファイルを読み取ることができます。たとえば、多くのプログラムは、各行にカンマで区切られ、二重引用符で囲まれた複数のフィールドが含まれており、かつ開始行がカラム名になっているようなカンマ区切り値 (CSV) 形式でデータをエクスポートできます。このようなファイル内の行が復帰改行と改行のペアで終了している場合、次に示すステートメントは、このファイルをロードするために使用するフィールド処理と行処理のオプションを示しています。

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

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

フィールド処理または行処理のどのオプションにも、空の文字列 ('') を指定できます。空でない場合、FIELDS [OPTIONALLY] ENCLOSED BY および FIELDS ESCAPED BY 値は単一の文字である必要があります。FIELDS TERMINATED BYLINES 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 文字は、文字列データ型 (CHARBINARYTEXTENUM など) を持つカラムの値を囲むためにのみ使用されます。

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 は、入力の解釈には影響を与えません。)ENCLOSED BY 文字が ESCAPED 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 文字が空でない場合、その文字が現れると取り除かれ、それに続く文字がフィールド値の一部として文字どおりに解釈されます。最初の文字がエスケープ文字である一部の 2 文字シーケンスは例外です。これらのシーケンスを (エスケープ文字に\を使用して) 次の表に示します。NULL 処理のルールについては、このセクションのあとの方で説明されています。

    文字 エスケープシーケンス
    \0 ASCII NUL (0x00) 文字
    \b バックスペース文字
    \n 改行 (ラインフィード) 文字
    \r 復帰改行文字
    \t タブ文字。
    \Z ASCII 26 (Ctrl+Z)
    \N NULL

    \でのエスケープ構文の詳細は、セクション9.1.1「文字列リテラル」を参照してください。

    FIELDS ESCAPED BY 文字が空である場合、エスケープシーケンスの解釈は実行されません。

  • 出力では、FIELDS ESCAPED BY 文字が空でない場合、その文字は、出力上で次の文字の前に付けるために使用されます。

    • FIELDS ESCAPED BY 文字

    • FIELDS [OPTIONALLY] ENCLOSED BY 文字

    • FIELDS TERMINATED BY および LINES TERMINATED BY 値の最初の文字

    • ASCII 0 (エスケープ文字のあとに実際に書き込まれる文字は 0 の値のバイトではなく、ASCII の0です)

    FIELDS ESCAPED BY 文字が空である場合は、どの文字もエスケープされず、NULL\N ではなく、NULL として出力されます。特に、データ内のフィールド値に今指定したリスト内のいずれかの文字が含まれている場合、空のエスケープ文字を指定することはおそらく適切な方法ではありません。

特定のケースでは、フィールド処理と行処理のオプションは相互に作用します。

  • LINES TERMINATED BY が空の文字列であり、かつ FIELDS TERMINATED BY が空以外である場合、行は FIELDS TERMINATED BY でも終了します。

  • FIELDS TERMINATED BYFIELDS ENCLOSED BY の値がどちらも空 ('') である場合は、固定行 (区切られていない) フォーマットが使用されます。固定行フォーマットでは、フィールド間に区切り文字は使用されません (ただし、行ターミネータは引き続き存在できます)。代わりに、カラム値は、そのフィールド内のすべての値を保持するために十分に広いフィールド幅を使用して読み取りと書き込みが行われます。TINYINTSMALLINTMEDIUMINTINT、および BIGINT では、宣言されている表示幅にかかわらず、フィールド幅はそれぞれ 4、6、8、11、および 20 です。

    LINES TERMINATED BY は引き続き、行を区切るために使用されます。ある行にすべてのフィールドが含まれていない場合、カラムの残りの部分はそのデフォルト値に設定されます。行ターミネータが存在しない場合は、これを '' に設定してください。この場合は、テキストファイルの各行にすべてのフィールドが含まれている必要があります。

    固定行フォーマットはまた、あとで説明されているように、NULL 値の処理にも影響を与えます。複数バイトの文字セットを使用している場合は、固定サイズフォーマットが機能しません。

NULL 値の処理は、使用されている FIELDS および LINES オプションによって異なります。

  • デフォルトの FIELDS および LINES 値では、NULL は出力として \N のフィールド値として書き込まれ、\N のフィールド値は入力として NULL として読み取られます (ESCAPED BY 文字は\であると仮定します)。

  • FIELDS ENCLOSED BY が空でない場合、リテラルワード NULL をその値として含むフィールドは NULL 値として読み取られます。これは、文字列 'NULL' として読み取られる、FIELDS ENCLOSED BY 文字で囲まれたワード NULL とは異なります。

  • FIELDS ESCAPED BY が空である場合、NULL はワード NULL として書き込まれます。

  • 固定行フォーマット (これは、FIELDS TERMINATED BYFIELDS ENCLOSED BY がどちらも空であるときに使用されます) では、NULL は空の文字列として書き込まれます。これにより、NULL 値と空の文字列がどちらも空の文字列として書き込まれるため、ファイルに書き込まれた場合、テーブル内でこの両方を区別できなくなります。ファイルを読み戻したときにこの 2 つを区別できることが必要な場合は、固定行フォーマットを使用すべきではありません。

NULLNOT NULL カラムにロードしようとすると、そのカラムのデータ型の暗黙のデフォルト値の割り当てが行われて警告が発生するか、または厳密な SQL モードではエラーが発生します。暗黙のデフォルト値については、セクション11.6「データ型デフォルト値」で説明されています。

次の一部のケースは、LOAD DATA INFILE ではサポートされません。

  • 固定サイズ行 (FIELDS TERMINATED BYFIELDS ENCLOSED BY がどちらも空) および BLOB または TEXT カラム。

  • 別の区切り文字と同じか、または別の区切り文字のプリフィクスである区切り文字を指定した場合、LOAD DATA INFILE は入力を正しく解釈できません。たとえば、次の FIELDS 句では問題が発生します。

    FIELDS TERMINATED BY '"' ENCLOSED BY '"'
    
  • FIELDS ESCAPED BY が空である場合、フィールド値の中に FIELDS ENCLOSED BY または LINES TERMINATED BY に続いて FIELDS TERMINATED BY 値が現れると、LOAD DATA INFILE はフィールドまたは行の読み取りを非常に早く停止します。これは、LOAD DATA INFILE が、フィールドまたは行の値がどこで終了するかを正しく判定できないために発生します。

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

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

デフォルトでは、LOAD DATA INFILE ステートメントの最後にカラムリストが指定されていないときは、入力行にテーブルカラムごとのフィールドが含まれていることが期待されます。テーブルのカラムの一部のみをロードする場合は、カラムリストを指定します。

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 句の使用は、次の制限に従います。

  • SET 句の代入では、割り当て演算子の左側にカラム名のみを置くようにしてください。

  • SET の代入の右側では、サブクエリーを使用できます。カラムに代入される値を返すサブクエリーとして使用できるのは、スカラーサブクエリーだけです。また、サブクエリーを使用して、ロードされているテーブルから選択することはできません。

  • IGNORE 句によって無視された行は、カラム/変数リストや SET 句では処理されません。

  • ユーザー変数には表示幅がないため、固定行フォーマットのデータをロードする場合はユーザー変数を使用できません。

入力行を処理する場合、LOAD DATA はそれをフィールドに分割し、カラム/変数リストと SET 句に応じた値 (存在する場合) を使用します。そのあと、結果として得られる行がテーブルに挿入されます。そのテーブルに BEFORE INSERT または AFTER INSERT トリガーが存在する場合、これらのトリガーはそれぞれ、行挿入の前またはあとにアクティブ化されます。

入力行に含まれるフィールドが多すぎる場合は、余分なフィールドが無視され、警告数が 1 増えます。

入力行に含まれるフィールドが少なすぎる場合、入力フィールドがないテーブルカラムはそのデフォルト値に設定されます。デフォルト値の割り当てについては、セクション11.6「データ型デフォルト値」で説明されています。

空のフィールド値はフィールドがないとは見なされず、次のように解釈されます。

  • 文字列型の場合、このカラムは空の文字列に設定されます。

  • 数値型の場合、このカラムは 0 に設定されます。

  • 日付と時間型の場合、このカラムはその型の適切な0の値に設定されます。セクション11.3「日付と時間型」を参照してください。

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

空のフィールド値や正しくないフィールド値の処理は、SQL モードが制限的な値に設定されていると、今説明した処理とは異なってきます。たとえば、sql_mode='TRADITIONAL である場合は、空の値や 'x' などの値を数値カラムに変換すると 0 に変換されるのではなく、エラーが発生します。(LOCAL が指定されている場合は、操作の最中にファイルの転送を停止する方法がサーバーにはないため、制限的な sql_mode 値が設定されていても、エラーではなく警告が発生します。)

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

LOAD DATA INFILE はすべての入力を文字列と見なすため、ENUM または SET カラムの数値を INSERT ステートメントと同じように使用することはできません。ENUM および SET 値はすべて、文字列として指定する必要があります。

BIT 値を、2 進表記 (b'011010' など) を使用してロードすることはできません。これを回避するには、その値を通常の整数として指定し、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)

Unix では、LOAD DATA でパイプから読み取る必要がある場合は次の手法を使用できます (この例では、/ ディレクトリのリストをテーブル db1.t1 にロードします)。

mkfifo /mysql/data/db1/ls.dat
chmod 666 /mysql/data/db1/ls.dat
find / -ls > /mysql/data/db1/ls.dat &
mysql -e "LOAD DATA INFILE 'ls.dat' INTO TABLE t1" db1

ロードされるデータを生成するコマンドと mysql コマンドを別の端末で実行するか、または (前の例に示したように) バックグラウンドでデータ生成プロセスを実行する必要があります。これを行わないと、データが mysql プロセスから読み取られる準備ができるまで、パイプがブロックされます。

LOAD DATA INFILE ステートメントは、完了すると、次の形式の情報文字列を返します。

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

警告は、INSERT ステートメントを使用して値が挿入されるときと同じ状況で発生します (セクション13.2.5「INSERT 構文」を参照してください)。ただし、LOAD DATA INFILE では、入力行内のフィールドが少なすぎるか、または多すぎる場合にも警告が生成されます。

SHOW WARNINGS を使用すると、発生した問題に関する情報として最初の max_error_count 警告のリストを取得できます。セクション13.7.5.41「SHOW WARNINGS 構文」を参照してください。

C API を使用している場合は、mysql_info() 関数を呼び出すことによって、そのステートメントに関する情報を取得できます。セクション23.8.7.35「mysql_info()」を参照してください。


User Comments
  Posted by Ramam Pullella on August 20, 2007
Converting strings into dates while loading data using LOAD DATA INFILE:

In the following example, we are trying to convert the data in the file for date columns col3, col4 in formats 'mm/dd/yyyy', 'dd/mm/yyyy' into MySQL standard YYYY-mm-dd respectively.

load data infile '/tmp/xxx.dat'
into table xxx
fields terminated by '|'
lines terminated by '\n'
(col1,
col2,
@col3,
@col4,
col5)
set
col3 = str_to_date(@col3, '%m/%d/%Y'),
col4 = str_to_date(@col4, '%d/%m/%Y')
;

You could convert into any format you want by using the date_format function around the str_to_date().

Example:
...
set col2 = date_format(str_to_date(@col2, 'format'), 'your format')

  Posted by Jeremy Krieg on December 4, 2007
I just ran into the same problem that the first two posters (Nathan Nuebner and Robert Lee) had with fixed-width imports. I suspect that the reason for this behaviour derives from the following statement from the above documentation:

'...column values are read and written using a field width wide enough to hold all values in the field.'

If you have a VARCHAR(20) column in a multi-byte character set (eg, UTF8), then the "field width wide enough to hold all values" in this field, measured in bytes, will actually be somewhat greater than 20. The two workarounds above worked because they both specified character sets which allocate one byte per character (latin1 and binary).

Specifying the character set in the LOAD DATA INFILE statement does not seem to work around the problem - that seems only to affect the incoming conversion from bytes to characters, it doesn't affect the number of bytes read.

The Latin1/binary examples above worked because they weren't trying to load multi-byte characters, however for someone who was trying to import multi-byte characters (or more specifically, to import character sets like UTF8 that use variable-width encoding for the characters) it would not work. There doesn't appear to be an easy workaround that I can see except to write an import utility in another programming language like Perl, Java or C.
  Posted by Max Floden on February 29, 2008
Step by step guide on how to import Excel data using LOAD DATA: http://www.tjitjing.com/blog/2008/02/import-excel-data-into-mysql-in-5-easy.html
  Posted by Ryan Neve on July 18, 2008
To load a text file with fixed width columns, I used the form:
LOAD DATA LOCAL INFILE '<file name>' INTO TABLE <table>
(@var1)
SET Date=str_to_date(SUBSTR(@var1,3,10),'%m/%d/%Y'),
Time=SUBSTR(@var1,14,8),
WindVelocity=SUBSTR(@var1,26,5),
WindDirection=SUBSTR(@var1,33,3),
WindCompass=SUBSTR(@var1,38,3),
WindNorth=SUBSTR(@var1,43,6),
WindEast=SUBSTR(@var1,51,6),
WindSamples=SUBSTR(@var1,61,4);
  Posted by lvaro G. Vicario on January 28, 2013
Importing floating point numbers that use comma as decimal separator requires the same trick than dates:

LOAD DATA LOCAL INFILE 'C:/path/to/mytable.txt' IGNORE
INTO TABLE mytable
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'
(int_col, @float_col)
SET float_col = replace(@float_col, ',', '.');

MySQL casts the value into the column type when it reads it from the file, *before* applying the transformations described in the SET clause. If you instruct it to read it into a variable the value is handled as string from the beginning.
  Posted by Clive le Roux on February 2, 2009
If you get "Skipped records" using "LOAD DATA LOCAL INFILE" copy the data file to the actual database server and do the load without the "LOCAL" keyword.
This will then stop when an error occurs, 9 times out of 10 it will be index issues and you will know why there are skipped records.

e.g. LOAD DATA LOCAL INFILE 'myinfile.txt';
Query OK, 288168 rows affected (1 min 44.49 sec)
Records: 494522 Deleted: 0 Skipped: 206354 Warnings: 0

LOAD DATA INFILE '/data/input/myinfile.txt';
Query OK, 252243 rows affected (0.02 sec)
ERROR 1062 (23000): Duplicate entry '5935009001-2008-08-03 04:19:18' for key 1
  Posted by Mattias Andersson on April 7, 2009
I've looked for a way to conditionally ignore rows in an INFILE for a long time. There may be an obvious way to do this but I have never seen one.
Today I discovered a very neat way to achieve this.
Assume that you are have an INFILE Containing names and genders of people. In my case, the INFILE has fixed fields. The first column is either 1 (=Male) or 2 (=Female) and column 2-18 contains the name of the person.

Now, if you would want to load the males (row[0] == 1) only, create the following table.

CREATE TABLE Names
(
name varchar(255),
gender tinyint
)
PARTITION BY LIST (gender)
(
PARTITION Male VALUES IN (1)
#,PARTITION Female VALUES IN (2)
);

Note that the Female partition is commented out.
Now load the data normally, but be sure to specify the IGNORE keyword.

LOAD DATA INFILE '/tmp/names.dmp' IGNORE INTO TABLE Names (@var)
SET
Name=Trim(SUBSTR(@var,2,17)),
Gender=SUBSTR(@var,1,1)
;

The IGNORE prevents mysql to abort the import mid-file due to the missing partition.
Hope this is helpful to someone.

  Posted by Adrian Singer on April 7, 2009
Here's a great way to use LOAD DATA for UPDATEs, reaping the performance benefits of the super fast LOAD DATA when doing massive data updates:

http://www.softwareprojects.com/resources/programming/t-how-to-use-mysql-fast-load-data-for-updates-1753.html
  Posted by Chris Johnson on February 15, 2010
When the situation warrants, I will use load data infile because of its speed. In other situations I'll resort to using another method to load the data. I wish load data had the ability to output a .bad file. Without this, I must resort to writing code (PHP) to output bad records to a file.
I have an article about different methods to get data in MySQL, I prefer PHP's MySQLi prepared statements because of the lack of .bad files.
http://chrisjohnson.blogsite.org/php-and-mysql-data-import-performance/
  Posted by Mike H on April 26, 2010
It seems you need to remove the byte order mark (BOM) from unicode input files for some reason.
  Posted by Patrick Zahra on April 29, 2010
You can load a CSV file on a hosted server where you have no write access.

First, create a temporary table with one LongText field.

INSERT your CSV into that table as one single cell.

Then do a SELECT INTO DUMPFILE to save the file somewhere on the server accessible to MySQL.

LOAD DATA INFILE will now be able to find your CSV.

You will likely be unable to delete this file, so it is best to overwrite it with another DUMPFILE query, this time giving it an empty string to erase its contents, and you can reuse it again later.
  Posted by Aissam Bazzaoui on September 20, 2010
for people who had encoding problem will loading a file, try convert the file to different encoding.

i used a tool called enca on my linux box (apt-get install enca) and converted the file to latin :

# enca import.csv
Universal transformation format 8 bits; UTF-8

just to check for the file encoding

# enconv -x latin1 import.csv

to convert it to latin1

and runned the following query to populate my table (encoded in utf8_general_ci)

SET CHARACTER SET 'utf8';
SET collation_connection = 'utf8_general_ci'

load data local infile '/home/aissam/Documents/data/import.csv' INTO Table cscripts FIELDS TERMINATED BY ';';

good luck
  Posted by Aaron Cohen on October 1, 2010
Further to Mattias Andersson's comment, above, there *is* a convenient way to restrict the rows that end up in your table *if* your table has a unique index on a column of interest.

In my particular case, I have a CSV of all US Zipcodes. A number of entries in the CSV duplicate other entries. But, only one entry is marked as the "Preferred" entry, and that's the canonical entry that I want to have end up in my table. Unfortunately, that entry could appear anywhere in the file - not necessarily first (so I could use IGNORE) or last (so I could use REPLACE).

Since my table has a unique index on zip, the following allowed me to exclude all non-preferred rows from being imported:

LOAD DATA INFILE '#{DATA_FILE_NAME}' IGNORE
INTO TABLE zipcodes
FIELDS TERMINATED BY ','
ENCLOSED BY '\"'
LINES TERMINATED BY '\\n'
(city, state_code, @zip, area_code, county_fips, county_name, @preferred, timezone, dst, lat,
lon, msa, pmsa, @city_abbreviation, ma, zip_type)
SET allow_registrations = 1, zip = IF(@preferred='P', @zip, NULL)

(Note that the above is a string in a Ruby script that is programmatically executed.)

Since the IGNORE keyword is specified, when zip is set to NULL the row is ignored *assuming* the DBMS is configured to not auto-convert NULL to the empty string.

If instead the DBMS is configured to auto-convert NULL to the empty string, the single junk row must be deleted after the above completes.

DELETE FROM zipcodes WHERE zip = '';
  Posted by Mike Laird on October 13, 2010
Loading utf8 character encoded files that are comma separated into MySQL is a technology mine field for many people. The steps below are very specific to transferring data from Excel 2007 to MySQL in a 1 for 1 way, i.e., 4 columns in Excel 2007 into 4 fields in MySQL in the same column order. Other transfers are possible, but require fiddling with step h. below via information in the Help tab of MySQL Query Browser. This is not the only way to do it, but it works and the load is extremely fast.
a. Before starting the csv transformation and transfer, make sure the source data is “cleaned up”, including: sort by the first column and remove duplicate rows/tuples, remove all hyperlinks from copying from web sites, etc. Only use Excel 2007 (or later) because earlier Excel versions do not save data in utf8 character encoding. Some Microsoft manager of updates who is "standards resistant" says 'gotcha' every time you use an earlier version of Excel and try to get data into MySQL.
b. Get or make a filename.xlsx file in Excel 2007. This will store the text in utf8 character encoding. Do not have field name headings at the top of the columns.
c. Upload the filename.xlsx to Google Documents as a spreadsheet. If asked, check the box to put the spreadsheet into Google’s format.
d. Open the file in Google Documents. Check in the Google Docs spreadsheet that only text and/or numbers are in each cell (no hyperlinks, etc.). In the File command, download/export the file and select .csv as the file type. Download it to your own PC. Google Docs ‘magically’ cleans up Microsoft’s mess.
e. Save the filename.csv file on your own PC. Then change the file type to .txt You may change the filename, if you want to.
f. Move this new file, e.g., filename.txt, into the MySQL data directory that has the target schema of interest. An example is: MySQL Server 5.1/data/schema_name
g. Open MySQL Query Browser from Tools in the command bar in MySQL Administrator. MySQL Query Browser is in the MySQL Tools directory. Navigate to your schema_name
h. Enter the following commands into the top command entry bar – or double click the Data Manipulation Statements in the lower right corner, and double click LOAD DATA INFILE; then delete unnecessary lines and all square brackets [ ]. The numbers below should not be entered.
i. LOAD DATA INFILE ‘filename.txt’
ii. REPLACE (this will eliminate duplicates in the new data)
iii. INTO TABLE schema_name.table
iv. CHARACTER SET utf8 (optional, if your table is configured for utf8. Your data is already utf8; but it won’t hurt to do)
v. FIELDS TERMINATED BY ‘,’
vi. ESCAPED BY ‘”’ (handles phrases with commas , )
vii. LINES TERMINATED BY ‘\n’
i. Press the Execute button. You may get a message saying no result set returned. This is OK, if there is no error statement and number.
j. Drag the table icon into the command entry bar, and a SELECT statement appears. Execute it. The data table will appear.
k. Compare the SELECT result set to the original Excel 2007 data file to find errors, e.g., erroneous fields that you didn’t catch up front.
l. Use the MySQL Query Browser to remove row/tuple errors
i. Indicate row by clicking on it
ii. Click Edit at the bottom
iii. Right click and select delete row
iv. Click Apply Changes

  Posted by M Locherer on May 5, 2011
Given:
ItemCount|Status|ExtractTime
1|Proposed|2011-02-10 00:00:00
1|Scoped|2011-02-10 00:00:00
1|Proposed|2011-02-10 00:00:00
1|Scoped Out|2011-02-10 00:00:00
1|Proposed|2011-02-10 00:15:00

load data infile '/home/milo/ISExtract.txt'
into table ISExtract
fields terminated by '|'
lines terminated by '\r\n'
ignore 1 lines
(@ic, Status, @et)
set
ItemCount = convert(@IC, signed),
ExtractTime = str_to_date(@et, '%Y-%m-%d %H:%i:%s');

Don't forget apparmor if you are running Ubuntu - edit /etc/apparmor.d/usr.sbin.mysqld and add /home/milo to the accessible paths for the mysqld process.
lines terminated by '\r\n' --> because the file comes from a Windows source
Hope that helps!
http://www.corporate-insyte.com/Blogs/MilosBlog.aspx

  Posted by John Swapceinski on September 5, 2011
Create a table using the .csv file's header:

#!/bin/sh
# pass in the file name as an argument: ./mktable filename.csv
echo "create table $1 ( "
head -1 $1 | sed -e 's/,/ varchar(255),\n/g'
echo " varchar(255) );"

  Posted by Marc MAURICE on November 13, 2011
A little script I made to import CSV to MySQL. Thanks to John's comment.

http://en.positon.org/post/Import-CSV-file-to-MySQL
  Posted by Barbara Snyder on October 16, 2012
After only about two frustrating hours (I am a total novice), I FINALLY got this to work on Mac by saving as comma-delimited CSV. The trick was to use

LINES TERMINATED BY '\r'

along with (of course)

FIELDS TERMINATED BY ','

  Posted by Tamara Bookal on October 29, 2012
A sample use of LOAD DATA INFILE to parse and load data into MYSQL database table using php script:

"LOAD DATA INFILE '$myFile'" .
" INTO TABLE test FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n'
(@col1,@col2,@col3,@col4,@col5,@col6,@col7,@col8,@col9,@col10,@col11,@col12,@col13,@col14,@col15,@col16)

SET sdate = STR_TO_DATE(@col1,'%m/%d/%Y'),
acq = @col2,
iss = @col3,
tdate = STR_TO_DATE(@col4,'%m/%d/%Y'),
msg = @col5,
rsp = @col6,
rev = @col7,
tid = @col8,
trace = @col9,
ldate = STR_TO_DATE(@col10,'%m/%d/%Y'),
ltime = @col11,
Cnum = @col12,
amount = REPLACE(REPLACE(TRIM(@col13), '$', ''), ',','') ,
txn = @col14,
code = @col15,
anum = @col16,
upload_date = Now(),
upload_source = '".$_FILES['filename']['name']."'");

This is a combination of different samples pieced together. The @col represents the data elements in the file '$myfile' the table fields are assigned to these variables.

Hope this is useful to someone else.
  Posted by Wilzon Mariño Bueno on April 8, 2015
Given:
name,price,status,category
product 01,15,active,fruit
product 02,10,active,fruit
product 03,11,active,vegetables
product 04,15,active,fruit
product 05,19,active,fruit

LOAD DATA INFILE '/var/home/wilzonmb/data/product.csv'
INTO TABLE product
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(product_name, product_price, product_status, category);

In this case, only inserted in fields mentioned, omitting such as ID, stock, etc.
  Posted by gere dses on October 11, 2015
name,price,status,category
product 01,15,active,fruit
product 02,10,active,fruit
product 03,11,active,vegetables
product 04,15,active,fruit
product 05,19,active,fruit

You can use LOAD DATA INFILE command to import csv file into table.

Check this link MySQL - LOAD DATA INFILE.

LOAD DATA LOCAL INFILE 'abc.csv' INTO TABLE abc
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(col1, col2, col3, col4, col5...)

enter image description here
CREATE TABLE USING FOLLOWING QUERY :

CREATE TABLE IF NOT EXISTS `survey` (
`projectId` bigint(20) NOT NULL,
`surveyId` bigint(20) NOT NULL,
`views` bigint(20) NOT NULL,
`dateTime` datetime NOT NULL
);
YOUR CSV FILE MUST BE PROPERLY FORMATTED FOR EXAMPLE SEE FOLLOWING ATTACHED IMAGE :
enter image description here

If every thing is fine.. Please execute following query to LOAD DATA FROM CSV FILE :

NOTE : Please add absolute path of your CSV file
LOAD DATA INFILE '/var/www/csv/data.csv'
INTO TABLE survey
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
If everything has done. you have exported data from CSV to table successfully
Sign Up Login You must be logged in to post a comment.