MySQL load文の詳細
6096 ワード
loadの構文
LOAD DATA INFILE文は、1つのテキストファイルからローを高速に読み込み、テーブルに読み込むために使用されます.ファイル名は文字列でなければなりません.
character_set_Databaseシステム変数が示す文字セットは、ファイル内の情報を解釈するために使用される.SET NAMESとcharacter_set_クライアントの設定は入力の解釈に影響しません.
典型的な例
1つのテーブルのカラムの一部のみをロードする場合は、カラム・リストを指定します.
ファイルのパス
LOCALが指定されている場合は、接続されているクライアントに関係しているとみなされます.
1.LOCALが指定されている場合、ファイルはクライアントホスト上のクライアントによって読み込まれ、サーバに送信されます.ファイルには、正確な場所を指定するために完全なパス名が与えられます.相対パス名が指定されている場合、この名前はクライアントの起動時に存在するディレクトリに対して理解されます.
2.LOCALが指定されていない場合、ファイルはサーバホスト上にあり、サーバによって直接読み込まれる必要があります.
サーバホスト上でファイルを配置する場合、サーバは次のルールを使用します.
1).絶対パス名が指定されている場合、サーバはこのパス名を使用します.2).1つ以上のブートコンポーネントを持つ相対パス名が指定されている場合、サーバはサーバデータディレクトリに対するファイルを検索します.3).ブートコンポーネントを持たないファイル名が指定されている場合、サーバはデフォルトのデータベースのデータベースディレクトリでファイルを探します.
これらのルールは./myfile.txtのファイルはmyfileというサーバデータディレクトリから読み出す.txtの同じファイルは、デフォルトのデータベースのデータベースディレクトリから読み込まれます.
クライアントから絶対パスloadデータを使用
サーバから相対パスloadデータを使用する
次のLOAD DATA文はdb 1データベースディレクトリからファイルdataを読み出す.db 1は現在のデータベースであるため、txt.文がdb 2データベースのテーブルに明確にファイルをロードしても、db 1ディレクトリから読み込まれます.
IGNORE number LINESオプション
IGNORE number LINESオプションは、ファイルの先頭で行を無視するために使用することができる.
IGNORE 1 LINESを使用すると、カラム名を含む先頭ヘッダー行をスキップできます.
REPLACEとIGNORE
一部の入力レコードは、既存のレコードを一意のキーワード値にコピーします.REPLACEおよびIGNOREキーワードは、これらの入力レコードの動作を制御するために使用される.
REPLACEを指定すると、入力行は元の行に置き換えられます(つまり、元の行と同じプライマリ・インデックスまたはユニーク・インデックスに同じ値を持つ行です).
IGNOREを指定すると、既存の行を一意のキー値にコピーする入力行がスキップされます.
どちらも指定しない場合は、LOCALキーワードが指定されているかどうかによって動作が決まります.LOCALを使用しない場合、キーワード値を繰り返すとエラーが発生し、残りのテキストファイルは無視されます.LOCALを使用する場合、デフォルトの運転状況はIGNOREが指定された場合と同じです.これは,実行中にサーバがファイルの転送を中止できないためである.
インデックスの影響
空のMyISAMテーブルに対してLOAD DATA INFILEを使用すると、すべてのユニークでないインデックスが独立したバッチに作成されます(REPAIR TABLEの場合).多くのインデックスがある場合、これは通常、LOAD DATA INFILEを大幅に高速化します.通常、LOAD DATA INFILEの速度は非常に速いですが、極端な場合は、テーブルにファイルをロードする前にALTER TABLEを使用することができます.DISABLE KEYS LOAD DATA INFILEを閉じるか、ファイルをロードした後にALTER TABLEを使用するか...ENABLE KEYSはインデックスを再作成し、インデックスの作成速度を速めます.
FIELDSとLINESのデフォルト値
FIELDS句を指定しない場合、デフォルト値は次の文を書いた場合の値です.
LINES句を指定しない場合、デフォルト値は次の文を書いた場合の値です.
すなわち、入力値が読み込まれると、デフォルトではLOAD DATA INFILEが次のように動作します.
逆に、出力値を記述する場合、デフォルト値はSELECT...INTO OUTFILEは次のように動作します.
FIELDS ESCAPED BY'\'を書き込むには、読み出される値に対して、シングルスラッシュとして使用する2つのスラッシュを指定する必要があります.
注記:Windowsシステムでテキストファイルを生成した場合は、Windowsプログラムで通常2文字を1行の終端として使用するため、LINES TERMINTED BY'r'を使用してファイルを正しく読み込む必要があります.一部のプログラムでは、ファイルを作成するときにrを行終端として使用する場合があります.このようなファイルを読み込むには、LINE TERMINTED BY'r'を使用します.
STARTING LINESオプション
読み込みたいすべてのローに無視したい共通の接頭辞が含まれている場合は、'prefix_を使用します.string'は、接頭辞(および接頭辞の前の文字)をスキップします.ローに接頭辞が含まれていない場合は、ロー全体がスキップされます.注記:prefix_stringは1行の真ん中に現れます.
次のテストでtxtはファイルソース
次のsqlを使用してデータをインポート
最後にデータ(「row」,1)と(「row」,2)のみが得られる.
TERMINTED LINEオプション
jokesが%からなる行で区切られている場合、jokesを含むファイルを読み込むには、次のようにします.
TERMINTED,ENCLOSED,ESCAPED FIELDオプション
TERMINTEDは、フィールドの区切り記号を制御するために使用され、複数の文字であってもよい.
ENCLOSED BYはフィールドを制御するための引用符である、単一文字でなければならない.語OPTIONALLYを無視すると、すべてのフィールドがENCLOSED BY文字列に含まれる.OPTINALLYを指定すると、ENCLOSED BY文字は、CHAR、BINARY、TEXT、ENUMなどの文字列データ型を持つ列の値のみに使用される.
SELECT...INTO OUTFILEエクスポートデータ、ENCLOSED BY'''',無視OPTIONALLY
SELECT...INTO OUTFILEエクスポートデータ,ENCLOSED BY''',指定OPTIONALLY
ESCAPED BYはエスケープに使用され、FIELDS ESCAPED BY値は単一文字でなければなりません.
FIELDS ESCAPED BY文字が空の場合、文字はエスケープされず、NULLはNではなくNULLとして出力される.空のエスケープ文字を指定するのは良い方法ではありません.特に、データのフィールド値に指定したリストの文字が含まれている場合は、そうすることはできません.
フィールド値内にENCLOSED BY文字が表示された場合は、ESCAPED BY文字を接頭辞として使用することにより、ENCLOSED BY文字がエスケープされる.
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char' ]
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name = expr,...)]
LOAD DATA INFILE文は、1つのテキストファイルからローを高速に読み込み、テーブルに読み込むために使用されます.ファイル名は文字列でなければなりません.
character_set_Databaseシステム変数が示す文字セットは、ファイル内の情報を解釈するために使用される.SET NAMESとcharacter_set_クライアントの設定は入力の解釈に影響しません.
典型的な例
LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE tbl_name
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '
'
1つのテーブルのカラムの一部のみをロードする場合は、カラム・リストを指定します.
LOAD DATA LOCAL INFILE 'persondata.txt' INTO TABLE persondata (col1,col2);
ファイルのパス
LOCALが指定されている場合は、接続されているクライアントに関係しているとみなされます.
1.LOCALが指定されている場合、ファイルはクライアントホスト上のクライアントによって読み込まれ、サーバに送信されます.ファイルには、正確な場所を指定するために完全なパス名が与えられます.相対パス名が指定されている場合、この名前はクライアントの起動時に存在するディレクトリに対して理解されます.
2.LOCALが指定されていない場合、ファイルはサーバホスト上にあり、サーバによって直接読み込まれる必要があります.
サーバホスト上でファイルを配置する場合、サーバは次のルールを使用します.
1).絶対パス名が指定されている場合、サーバはこのパス名を使用します.2).1つ以上のブートコンポーネントを持つ相対パス名が指定されている場合、サーバはサーバデータディレクトリに対するファイルを検索します.3).ブートコンポーネントを持たないファイル名が指定されている場合、サーバはデフォルトのデータベースのデータベースディレクトリでファイルを探します.
これらのルールは./myfile.txtのファイルはmyfileというサーバデータディレクトリから読み出す.txtの同じファイルは、デフォルトのデータベースのデータベースディレクトリから読み込まれます.
クライアントから絶対パスloadデータを使用
LOAD DATA LOCAL INFILE '/import/data.txt' INTO TABLE db2.my_table;
サーバから相対パスloadデータを使用する
次のLOAD DATA文はdb 1データベースディレクトリからファイルdataを読み出す.db 1は現在のデータベースであるため、txt.文がdb 2データベースのテーブルに明確にファイルをロードしても、db 1ディレクトリから読み込まれます.
USE db1;
LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;
IGNORE number LINESオプション
IGNORE number LINESオプションは、ファイルの先頭で行を無視するために使用することができる.
IGNORE 1 LINESを使用すると、カラム名を含む先頭ヘッダー行をスキップできます.
LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;
REPLACEとIGNORE
一部の入力レコードは、既存のレコードを一意のキーワード値にコピーします.REPLACEおよびIGNOREキーワードは、これらの入力レコードの動作を制御するために使用される.
REPLACEを指定すると、入力行は元の行に置き換えられます(つまり、元の行と同じプライマリ・インデックスまたはユニーク・インデックスに同じ値を持つ行です).
IGNOREを指定すると、既存の行を一意のキー値にコピーする入力行がスキップされます.
どちらも指定しない場合は、LOCALキーワードが指定されているかどうかによって動作が決まります.LOCALを使用しない場合、キーワード値を繰り返すとエラーが発生し、残りのテキストファイルは無視されます.LOCALを使用する場合、デフォルトの運転状況はIGNOREが指定された場合と同じです.これは,実行中にサーバがファイルの転送を中止できないためである.
インデックスの影響
空のMyISAMテーブルに対してLOAD DATA INFILEを使用すると、すべてのユニークでないインデックスが独立したバッチに作成されます(REPAIR TABLEの場合).多くのインデックスがある場合、これは通常、LOAD DATA INFILEを大幅に高速化します.通常、LOAD DATA INFILEの速度は非常に速いですが、極端な場合は、テーブルにファイルをロードする前にALTER TABLEを使用することができます.DISABLE KEYS LOAD DATA INFILEを閉じるか、ファイルをロードした後にALTER TABLEを使用するか...ENABLE KEYSはインデックスを再作成し、インデックスの作成速度を速めます.
FIELDSとLINESのデフォルト値
FIELDS句を指定しない場合、デフォルト値は次の文を書いた場合の値です.
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES句を指定しない場合、デフォルト値は次の文を書いた場合の値です.
LINES TERMINATED BY '
' STARTING BY ''
すなわち、入力値が読み込まれると、デフォルトではLOAD DATA INFILEが次のように動作します.
。
。
。
。
、 、 ‘\' ‘\' ， 。
逆に、出力値を記述する場合、デフォルト値はSELECT...INTO OUTFILEは次のように動作します.
。
。
、 ‘\' ， ‘\' 。
。
FIELDS ESCAPED BY'\'を書き込むには、読み出される値に対して、シングルスラッシュとして使用する2つのスラッシュを指定する必要があります.
注記:Windowsシステムでテキストファイルを生成した場合は、Windowsプログラムで通常2文字を1行の終端として使用するため、LINES TERMINTED BY'r'を使用してファイルを正しく読み込む必要があります.一部のプログラムでは、ファイルを作成するときにrを行終端として使用する場合があります.このようなファイルを読み込むには、LINE TERMINTED BY'r'を使用します.
STARTING LINESオプション
読み込みたいすべてのローに無視したい共通の接頭辞が含まれている場合は、'prefix_を使用します.string'は、接頭辞(および接頭辞の前の文字)をスキップします.ローに接頭辞が含まれていない場合は、ロー全体がスキップされます.注記:prefix_stringは1行の真ん中に現れます.
次のテストでtxtはファイルソース
xxx"row",1
something xxx"row",2
次のsqlを使用してデータをインポート
LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test LINES STARTING BY "xxx";
最後にデータ(「row」,1)と(「row」,2)のみが得られる.
TERMINTED LINEオプション
jokesが%からなる行で区切られている場合、jokesを含むファイルを読み込むには、次のようにします.
LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes FIELDS TERMINATED BY '' LINES TERMINATED BY '
%%
' (joke);
TERMINTED,ENCLOSED,ESCAPED FIELDオプション
TERMINTEDは、フィールドの区切り記号を制御するために使用され、複数の文字であってもよい.
ENCLOSED BYはフィールドを制御するための引用符である、単一文字でなければならない.語OPTIONALLYを無視すると、すべてのフィールドがENCLOSED BY文字列に含まれる.OPTINALLYを指定すると、ENCLOSED BY文字は、CHAR、BINARY、TEXT、ENUMなどの文字列データ型を持つ列の値のみに使用される.
SELECT...INTO OUTFILEエクスポートデータ、ENCLOSED BY'''',無視OPTIONALLY
"1","a string","100.20"
SELECT...INTO OUTFILEエクスポートデータ,ENCLOSED BY''',指定OPTIONALLY
1,"a string",100.20
ESCAPED BYはエスケープに使用され、FIELDS ESCAPED BY値は単一文字でなければなりません.
FIELDS ESCAPED BY文字が空の場合、文字はエスケープされず、NULLはNではなくNULLとして出力される.空のエスケープ文字を指定するのは良い方法ではありません.特に、データのフィールド値に指定したリストの文字が含まれている場合は、そうすることはできません.
フィールド値内にENCLOSED BY文字が表示された場合は、ESCAPED BY文字を接頭辞として使用することにより、ENCLOSED BY文字がエスケープされる.