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 句の構文は、両方のステートメントで同じです。どちらの句もオプションですが、両方が指定される場合は、FIELDS を LINES の前に指定する必要があります。

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

INSERT と LOAD 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 を使用して、または mysql で SELECT ... INTO OUTFILE ステートメントを発行することによってデータファイルを書き込む場合は、そのファイルが LOAD DATA INFILE でロードされるときに使用される文字セットで出力が書き込まれるように、必ず --default-character-set オプションを使用してください。

注記

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

LOW_PRIORITY を使用した場合、LOAD DATA ステートメントの実行は、ほかのどのクライアントもそのテーブルから読み取らなくなるまで遅延されます。これは、テーブルレベルロックのみを使用するストレージエンジン (MyISAM、MEMORY、および 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 が指定されている場合、ファイルはクライアントホスト上のクライアントプログラムによって読み取られ、サーバーに送信されます。このファイルは、その正確な場所を指定するためにフルパス名として指定できます。相対パス名として指定されている場合、その名前は、クライアントプログラムが起動されたディレクトリを基準にして解釈されます。

  LOCAL を LOAD 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 INFILE と SELECT ... INTO OUTFILE の両方のステートメントで同じです。どちらの句もオプションですが、両方が指定される場合は、FIELDS を LINES の前に指定する必要があります。

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 OUTFILE と LOAD 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 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 は、入力の解釈には影響を与えません。)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 BY と FIELDS ENCLOSED BY の値がどちらも空 ('') である場合は、固定行 (区切られていない) フォーマットが使用されます。固定行フォーマットでは、フィールド間に区切り文字は使用されません (ただし、行ターミネータは引き続き存在できます)。代わりに、カラム値は、そのフィールド内のすべての値を保持するために十分に広いフィールド幅を使用して読み取りと書き込みが行われます。TINYINT、SMALLINT、MEDIUMINT、INT、および 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 BY と FIELDS ENCLOSED BY がどちらも空であるときに使用されます) では、NULL は空の文字列として書き込まれます。これにより、NULL 値と空の文字列がどちらも空の文字列として書き込まれるため、ファイルに書き込まれた場合、テーブル内でこの両方を区別できなくなります。ファイルを読み戻したときにこの 2 つを区別できることが必要な場合は、固定行フォーマットを使用すべきではありません。

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

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

• 固定サイズ行 (FIELDS TERMINATED BY と FIELDS 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()」を参照してください。