MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.2.7 LOAD DATA ステートメント

9.1 English
9.0 English
8.4 English
8.0 English
5.7 English

MySQL 8.0 リファレンスマニュアル / ... / LOAD DATA ステートメント

このページは機械翻訳したものです。

13.2.7 LOAD DATA ステートメント

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

LOAD DATA ステートメントは、テキストファイルからテーブルに非常に高速に行を読み取ります。 LOAD DATA は、SELECT ... INTO OUTFILE を補完したものです。 (セクション13.2.10.1「SELECT ... INTO ステートメント」を参照してください。) テーブルからファイルにデータを書き込むには、SELECT ... INTO OUTFILE を使用します。ファイルをテーブルに読み取るには、LOAD DATA を使用します。 FIELDS および LINES 句の構文は、両方のステートメントで同じです。

mysqlimport ユーティリティを使用してデータファイルをロードすることもできます。セクション4.5.5「mysqlimport — データインポートプログラム」を参照してください。mysqlimport は、LOAD DATA ステートメントをサーバーに送信することによって動作します。

INSERT と LOAD DATA の効率および LOAD DATA の高速化の詳細は、セクション8.2.5.1「INSERT ステートメントの最適化」を参照してください。

パーティションテーブルのサポート

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

入力ファイルの名前、場所およびコンテンツの解釈

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

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

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

注記

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

LOCAL 修飾子は、後で説明するように、ファイルの予期される場所およびエラー処理に影響します。 LOCAL は、サーバーとクライアントの両方がそれを許可するように構成されている場合にのみ機能します。たとえば、local_infile システム変数を無効にして mysqld を起動した場合、LOCAL は機能しません。セクション6.1.6「LOAD DATA LOCAL のセキュリティー上の考慮事項」を参照してください。

LOCAL 修飾子は、ファイルが見つかる場所に影響します:

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

LOCAL を LOAD DATA とともに使用すると、MySQL サーバーが一時ファイルを格納するディレクトリにファイルのコピーが作成されます。セクションB.3.3.5「MySQL が一時ファイルを格納する場所」を参照してください。このディレクトリ内にコピーのための十分な領域がないと、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;

注記

また、サーバーは LOCAL 以外のルールを使用して、IMPORT TABLE ステートメントの .sdi ファイルを検索します。

LOCAL 以外のロード操作は、サーバーにあるテキストファイルを読み取ります。セキュリティ上の理由から、このような操作には FILE 権限が必要です。セクション6.2.2「MySQL で提供される権限」を参照してください。また、LOCAL 以外のロード操作は、secure_file_priv システム変数設定の影響を受けます。変数値が空でないディレクトリ名の場合、ロードするファイルはそのディレクトリに配置する必要があります。変数値が空 (セキュアでない) の場合、ファイルはサーバーからのみ読取り可能である必要があります。

LOCAL を使用すると、クライアントからサーバーへの接続を介してファイルコンテンツを送信する必要があるため、サーバーがファイルに直接アクセスするより少し時間がかかります。その一方で、ローカルファイルをロードするために FILE 権限は必要ありません。

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

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

レプリケーションに関する考慮事項

LOAD DATA は、ステートメントベースレプリケーションでは安全でないとみなされます。 binlog_format=STATEMENT が設定されているときに LOAD DATA を使用すると、データを含む一時ファイルが、変更が適用されるレプリケーションスレーブ上に作成されます。バイナリログの暗号化がサーバー上でアクティブな場合、この一時ファイルは暗号化されないことに注意してください。暗号化が必要な場合は、一時ファイルを作成しない行ベースまたは混合バイナリロギング形式を使用してください。 LOAD DATA とレプリケーションの相互作用の詳細は、セクション17.5.1.19「レプリケーションと LOAD DATA」を参照してください。

並列性に関する考慮事項

LOW_PRIORITY 修飾子を使用すると、ほかのクライアントがテーブルから読み取ることがなくなるまで、LOAD DATA ステートメントの実行が遅延されます。これは、テーブルレベルロックのみを使用するストレージエンジン (MyISAM、MEMORY、および MERGE) にのみ影響を与えます。

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

重複キーの処理

REPLACE および IGNORE 修飾子は、一意のキー値で既存の行を複製する新しい (入力) 行の処理を制御します:

REPLACE を指定すると、新しい行で既存の行が置き換えられます。つまり、既存の行と同じ主キーまたは一意インデックスの値を持つ行が既存の行を置換します。セクション13.2.9「REPLACE ステートメント」を参照してください。
IGNORE を指定すると、一意のキー値で既存の行を複製する新しい行は破棄されます。詳細は、IGNORE がステートメントの実行に与える影響を参照してください。
いずれの修飾子も指定しない場合、動作は LOCAL 修飾子が指定されているかどうかによって異なります。 LOCAL が指定されていない場合は、重複キー値が見つかるとエラーが発生し、テキストファイルの残りは無視されます。 LOCAL が指定されている場合、デフォルトの動作は IGNORE が指定されている場合と同じです。これは、操作の最中にファイルの転送を停止する方法がサーバーにはないためです。

インデックス処理

ロード操作中に外部キー制約を無視するには、LOAD DATA を実行する前に SET foreign_key_checks = 0 ステートメントを実行します。

空の MyISAM テーブルで LOAD DATA を使用する場合、一意でないすべてのインデックスが個別のバッチで作成されます (REPAIR TABLE の場合など)。通常、これにより、多数のインデックスがある場合に LOAD DATA がはるかに高速になります。一部の極端なケースでは、ファイルをテーブルにロードする前に ALTER TABLE ... DISABLE KEYS でインデックスを無効にし、ファイルをロードしたあとに ALTER TABLE ... ENABLE KEYS を使用してインデックスを再作成することによって、インデックスをさらに高速に作成できます。セクション8.2.5.1「INSERT ステートメントの最適化」を参照してください。

フィールドおよび明細の処理

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

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

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

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

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

つまり、デフォルトでは、LOAD DATA は入力の読取り時に次のように動作します:

改行の位置にある行の境界を探します。
行の接頭辞はスキップしないでください。
タブの位置で行をフィールドに分割します。
フィールドが引用文字で囲まれていることを期待しません。
前にエスケープ文字\がある文字をエスケープシーケンスとして解釈します。たとえば、\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 とともに使用してデータベースからファイルにデータを書き込み、後でそのファイルをデータベースに読み取る場合、両方のステートメントのフィールド処理オプションと行処理オプションが一致する必要があります。そうしないと、LOAD DATA はファイルの内容を正しく解釈しません。 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 に指示するため、機能しません:

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

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

LOAD DATA を使用して、外部ソースから取得したファイルを読み取ることができます。たとえば、多くのプログラムは、各行にカンマで区切られ、二重引用符で囲まれた複数のフィールドが含まれており、かつ開始行がカラム名になっているようなカンマ区切り値 (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 で正しく読み取れない出力が誤って生成される可能性があります。たとえば、エスケープ文字が空である場合、今示した前の出力は次のようになります。 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 (`X'00'`) 文字
`\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 文字
- 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 でサポートされていません:

固定サイズ行 (FIELDS TERMINATED BY と FIELDS ENCLOSED BY がどちらも空) および BLOB または TEXT カラム。
別のセパレータと同じセパレータまたは別のセパレータの接頭辞を指定すると、LOAD DATA は入力を正しく解釈できません。たとえば、次の FIELDS 句では問題が発生します。
```
FIELDS TERMINATED BY '"' ENCLOSED BY '"'
```
FIELDS ESCAPED BY が空の場合、FIELDS ENCLOSED BY または LINES TERMINATED BY の出現箇所と FIELDS TERMINATED BY 値が含まれるフィールド値によって、LOAD DATA はフィールドまたは行の読取りを早すぎる状態で停止します。これは、フィールドまたは行の値がどこで終了するかを LOAD DATA が適切に判断できないために発生します。

カラムリストの指定

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

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

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

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata
(col_name_or_user_var [, col_name_or_user_var] ...);

カラムリストはまた、入力ファイル内のフィールドの順序がテーブル内のカラムの順序と異なる場合にも指定する必要があります。そうしないと、MySQL は、入力フィールドとテーブルカラムを一致させる方法がわかりません。

入力前処理

各 col_name_or_user_var 値は、カラム名またはユーザー変数のいずれかです。ユーザー変数を使用すると、結果をカラムに割り当てる前に、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.2「日時データ型」を参照してください。

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

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

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

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

バイナリ表記法 (b'011010'など) を使用して BIT 値を直接ロードすることはできません。これを回避するには、SET 句を使用して先頭の b'および末尾の'を削除し、base-2 から base-10 への変換を実行して MySQL が値を BIT カラムに適切にロードするようにします:

shell> cat /tmp/bit_test.txt
b'10'
b'1111111'
shell> mysql test
mysql> LOAD DATA INFILE '/tmp/bit_test.txt'
       INTO TABLE bit_test (@var1)
       SET b = CAST(CONV(MID(@var1, 3, LENGTH(@var1)-3), 2, 10) 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)

0b バイナリ表記法 (0b011010 など) の BIT 値の場合は、かわりに次の SET 句を使用して、先頭の 0b を削除します:

SET b = CAST(CONV(MID(@var1, 3, LENGTH(@var1)-2), 2, 10) AS UNSIGNED)

ステートメントの結果情報

LOAD DATA ステートメントが終了すると、次の形式の情報文字列が返されます:

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

警告は、INSERT ステートメント (セクション13.2.6「INSERT ステートメント」を参照) を使用して値を挿入する場合と同じ状況で発生しますが、入力行のフィールドが少なすぎるか多すぎる場合にも LOAD DATA によって警告が生成される点が異なります。

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

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

その他のトピック

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 プロセスによってデータが読み取られるまでブロックされます。

PREV HOME UP NEXT