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
LINESIGNORE 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.7.7.35「mysql_info()」を参照してください。