Movatterモバイル変換

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

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

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

• 並列性に関する考慮事項

• 重複キーの処理

• インデックス処理

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

• カラムリストの指定

• 入力前処理

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

• その他のトピック

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 オプションを使用してください。

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

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

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

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

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

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

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

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 修飾子は、一意のキー値で既存の行を複製する新しい (入力) 行の処理を制御します:

ロード操作中に外部キー制約を無視するには、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 は入力の読取り時に次のように動作します:

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

すべての入力行に無視する共通の接頭辞がある場合は、LINES STARTING BY 'prefix_string'を使用して接頭辞およびそれより前のものをスキップできます。 行にプリフィクスが含まれていない場合は、行全体がスキップされます。 たとえば、次のステートメントを発行するとします。

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

データファイルは次のようになっています。

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

結果の行は、("abc",1) および("def",2) です。 ファイル内の 3 行目は、プリフィクスが含まれていないためスキップされます。

IGNOREnumber 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 TABLEtbl_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.202,"a string containing a , comma",102.203,"a string containing a \" quote",102.204,"a string containing a \", quote and comma",102.20

フィールド値内のENCLOSED BY 文字の出現は、先頭にESCAPED BY 文字を付けてエスケープされます。 また、空のESCAPED BY 値を指定すると、LOAD DATA で正しく読み取れない出力が誤って生成される可能性があります。 たとえば、エスケープ文字が空である場合、今示した前の出力は次のようになります。 4 行目の 2 番目のフィールドに含まれる引用符のあとにカンマが続いていることに注目してください。これにより、このフィールドが (誤って) 終了するように見えます。

1,"a string",100.202,"a string containing a , comma",102.203,"a string containing a " quote",102.204,"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" bossThe "BIG" boss      -> The "BIG" bossThe ""BIG"" boss    -> The ""BIG"" boss

FIELDS ESCAPED BY は、特殊文字の読み取りまたは書き込みの方法を制御します。

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

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

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

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

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

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

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

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

これらは、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.txtb'10'b'1111111'shell> mysql testmysql> 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: 0mysql> 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.datchmod 666 /mysql/data/db1/ls.datfind / -ls > /mysql/data/db1/ls.dat &mysql -e "LOAD DATA INFILE 'ls.dat' INTO TABLE t1" db1

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