mysql中txt的匯入及匯出

匯入資料庫:load data infile 'd:/test.txt' into table nmg fields terminated by ',' lines terminated by '\r\n';
13.2.5. LOAD DATA INFILE文法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語句用於高速地從一個文字檔中讀取行，並裝入一個表中。檔案名稱必須為一個文字字串。
要瞭解有關INSERT和LOAD DATA INFILE的效率的對比和有關LOAD DATA INFILE加速的更多資訊，請參見
7.2.16節，“INSERT語句的速度”
。
由character_set_database系統變數指示的字元集被用於解釋檔案中的資訊。SET NAMES和character_set_client的設定不會影響對輸入的解釋。
注意，目前不能載入UCS2資料檔案。
您也可以通過使用mysqlimport應用程式載入資料檔案；通過向伺服器發送一個LOAD DATA INFILE語句實現此功能。--local選項用於使mysqlimport從客戶主機中讀取資料檔案。如果用戶端和伺服器支援壓縮協議，則您可以指定—compress選項提高在慢速網路中的效能。請參見
8.10節，“mysqlimport：資料匯入程式
。
如果您使用LOW_PRIORITY，則LOAD DATA語句的執行被延遲，直到沒有其它的用戶端從表中讀取為止。
如果一個MyISAM表滿足同時插入的條件（即該表在中間有空閑塊），並且您對這個MyISAM表指定了CONCURRENT，則當LOAD DATA正在執行時，其它線程會從表中重新擷取資料。即使沒有其它線程在同時使用本表格，使用本選項也會略微影響LOAD DATA的效能。
如果指定了LOCAL，則被認為與串連的用戶端有關：
·         如果指定了LOCAL，則檔案會被客戶主機上的用戶端讀取，並被發送到伺服器。檔案會被給予一個完整的路徑名稱，以指定確切的位置。如果給定的是一個相對的路徑名稱，則此名稱會被理解為相對於啟動用戶端時所在的目錄。
·         如果LOCAL沒有被指定，則檔案必須位於伺服器主機上，並且被伺服器直接讀取。
當在伺服器主機上為檔案定位時，伺服器使用以下規則：
·         如果給定了一個絕對的路徑名稱，則伺服器使用此路徑名稱。
·         如果給定了帶有一個或多個引導組件的相對路徑名稱，則伺服器會搜尋相對於伺服器資料目錄的檔案。
·         如果給定了一個不帶引導組件的檔案名稱，則伺服器會在預設資料庫的資料庫目錄中尋找檔案。
注意，這些規則意味著名為./myfile.txt的檔案會從伺服器資料目錄中被讀取，而名為myfile.txt的同樣的檔案會從預設資料庫的資料庫目錄中讀取。例如，下面的LOAD DATA語句會從db1資料庫目錄中讀取檔案data.txt，因為db1是當前資料庫。即使語句明確把檔案載入到db2資料庫中的表裡，也會從db1目錄中讀取。
mysql> USE db1;mysql> LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;
注意，使用正斜杠指定Windows路徑名稱，而不是使用反斜線。如果您使用反斜線，您必須使用兩個。
出於安全原因，當讀取位於伺服器中的文字檔時，檔案必須位於資料庫目錄中，或者是全體可讀的。另外，要對伺服器檔案使用LOAD DATA INFILE，您必須擁有FILE許可權。
見
5.7.3節，“MySQL提供的許可權”
。
與讓伺服器直接讀取檔案相比，使用LOCAL速度略慢，這是因為檔案的內容必須通過用戶端發送到伺服器上。不過，您不需要FILE許可權來載入本地檔案。
只有當您的伺服器和您的用戶端都許可時，LOCAL才可運行。例如，如果使用—local-infile=0啟動mysqld，則LOCAL不運行。請參見
5.6.4節，“LOAD DATA LOCAL安全問題”
。
如果您需要LOAD DATA來從一個管道中讀取，您可以使用以下方法（此處我們把/目錄清單載入一個表格）：
mkfifo /mysql/db/x/xchmod 666 /mysql/db/x/xfind / -ls > /mysql/db/x/xmysql -e "LOAD DATA INFILE 'x' INTO TABLE x" x
有些輸入記錄把原有的記錄複製到唯一關鍵字值上。REPLACE和IGNORE關鍵字用於控制這些輸入記錄的操作。
如果您指定了REPLACE，則輸入行會替換原有行（換句話說，與原有行一樣，對一個主索引或唯一索引具有相同值的行）。請參見
13.2.6節，“REPLACE文法”
。
如果您指定IGNORE，則把原有行複製到唯一關鍵字值的輸入行被跳過。如果您這兩個選項都不指定，則運行情況根據LOCAL關鍵詞是否被指定而定。不使用LOCAL時，當出現重複關鍵字值時，會發生錯誤，並且剩下的文字檔被忽略。使用LOCAL時，預設的運行情況和IGNORE被指定時的情況相同；這是因為在運行中間，伺服器沒有辦法中止檔案的傳輸。
如果您希望在載入運行過程中忽略外鍵的限制，您可以在執行LOAD DATA前發送一個SET FOREIGN_KEY_CHECKS=0語句。
如果您對一個空的MyISAM表使用LOAD DATA INFILE，則所有的非唯一索引會被建立在一個獨立批中（對於REPAIR TABLE）。當您有許多索引時，這通常會使LOAD DATA INFILE大大加快。通常，LOAD DATA INFILE的速度會非常快，但是在某些極端情況下，您可以在把檔案載入到表中之前使用ALTER TABLE...DISABLE KEYS關閉LOAD DATA INFILE，或者在載入檔案之後使用ALTER TABLE...ENABLE KEYS再次建立索引，使建立索引的速度更快。請參見
7.2.16節，“INSERT語句的速度”
。
LOAD DATA INFILE是SELECT...INTO OUTFILE的補語。（見
13.2.7節，“SELECT文法”
。）要從一個表中把資料寫入一個檔案中，應使用SELECT...INTO OUTFILE。要讀取檔案，放回到表中，應使用LOAD DATA INFILE。FIELDS和LINES子句的文法對於兩個語句是一樣的。兩個子句都是自選的，但是如果兩個都被指定了，FIELDS必須位於LINES的前面。
如果您指定了一個FIELDS子句，則每個亞子句（TERMINATED BY, [OPTIONALLY] ENCLOSED BY和ESCAPED BY）也是自選的。不過，您必須指定其中至少一個。
如果您不指定FIELDS子句，則預設值為假設您寫下如下語句時的值：
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
如果您不指定LINES子句，則預設值為假設您寫下如下語句時的值：
LINES TERMINATED BY '\n' STARTING BY ''
換句話說，當讀取輸入值時，預設值會使LOAD DATA INFILE按如下方式運行：
·         在新行處尋找行的邊界。
·         不會跳過任何行首碼。
·         在定位字元處把行分解為欄位。
·         不希望欄位被包含在任何引號字元之中。
·         出現定位字元、新行、或在‘\’前有‘\’時，理解為作為欄位值一部分的文字字元。
相反的，當編寫輸出值時，預設值會使SELECT...INTO OUTFILE按如下方式運行：
·         在欄位之間寫入定位字元。
·         不把欄位包含在任何引號字元中。
·         當欄位值中出現定位字元、新行或‘\’時，使用‘\’進行轉義。
·         在行的末端寫入新行。
注意，要寫入FIELDS ESCAPED BY ‘\\’，您必須為待讀取的值指定兩個反斜線，作為一個單反斜線使用。
注釋：如果您已經在Windows系統中產生了文字檔，您可能必須使用LINES TERMINATED BY ‘\r\n’來正確地讀取檔案，因為Windows程式通常使用兩個字元作為一個行終止符。部分程式，比如WordPad，當編寫檔案時，可能會使用\r作為行終止符。要讀取這樣的檔案，應使用LINES TERMINATED BY ‘\r’。
如果所有您希望讀入的行都含有一個您希望忽略的共用首碼，則您可以使用'prefix_string'來跳過首碼（和首碼前的字元）。如果某行不包括首碼，則整個行被跳過。注釋：prefix_string會出現在一行的中間。
樣本：
mysql> LOAD DATA INFILE '/tmp/test.txt'    -> INTO TABLE test LINES STARTING BY "xxx";
使用此語句，您可以讀入包含有如下內容的檔案：
xxx"row",1something xxx"row",2
並只得到資料("row",1)和("row",2)。
IGNORE number LINES選項可以被用於在檔案的開始處忽略行。例如，您可以使用IGNORE 1 LINES來跳過一個包含列名稱的起始標題列：
mysql> LOAD DATA INFILE '/tmp/test.txt'    -> INTO TABLE test IGNORE 1 LINES;
當您聯合使用SELECT...INTO OUTFILE和LOAD DATA INFILE來從一個資料庫中把資料寫入一個檔案中，然後再讀取檔案，返回到資料庫中時，用於兩個語句的field-和line-handling選項必須匹配。否則，LOAD DATA INFILE不會正確地理解檔案的內容。假設您使用SELECT...INTO OUTFILE來編寫一個的檔案，欄位由逗號分隔：
mysql> SELECT * INTO OUTFILE 'data.txt'    ->          FIELDS TERMINATED BY ','    ->         
FROM table2;
要讀取由逗號分隔的檔案並返回，則正確的語句應該是：
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2    ->           FIELDS TERMINATED BY ',';
如果您嘗試使用以下所示的語句讀入檔案，則不會運行，因為該語句命令LOAD DATA INFILE尋找位於欄位之間的定位字元：
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2    ->           FIELDS TERMINATED BY '\t';
結果很可能是，每個輸入行被理解為一個單一欄位。
LOAD DATA INFILE也可以被用於讀取從外源中擷取的檔案。例如，一個dBASE格式的檔案具有以逗號分隔並且包含在雙引號中的欄位。如果檔案中的各行以新行為結尾，則此處所示的語句描述了您可以用於載入檔案的field-和line-handling選項：
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name    ->           FIELDS TERMINATED BY ',' ENCLOSED BY '"'    ->           LINES TERMINATED BY '\n';
所有field-或line-handling選項都可以指定一個Null 字元串('')。如果字串不是空的，則FIELDS [OPTIONALLY] ENCLOSED BY和FIELDS ESCAPED BY值必須為單一字元。FIELDS TERMINATED BY, LINES STARTING BY和LINES TERMINATED BY值可以超過一個字元。例如，要編寫由斷行符號/換行成對字元作為結尾的行，或讀取包含這類行的檔案，則應指定一個LINES TERMINATED BY ‘\r\n’子句。
如果jokes被由%%組成的行分隔，要讀取包含jokes的檔案，您可以這麼操作：
mysql> CREATE TABLE jokes    ->     (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,    ->     joke TEXT NOT NULL);mysql>
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"
如果您指定了OPTINALLY，則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字元作為首碼，對ENCLOSED BY字元進行轉義。另外，要注意，如果您指定了一個空的ESCAPED BY值，則可能會產生不能被LOAD DATA INFILE正確讀取的輸出值。例如，如果轉義符為空白字元，則剛顯示的先前輸出值應顯示如下。請觀察，第四行中的第二個欄位在引號後麵包含一個逗號，該引號（錯誤地）顯示出來，作為欄位的結尾：
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字元時，此字元可以重複書寫，並被理解為單一的字元。例如，如果指定了ENCLOSED BY '"'，則按照以下方法操作引號：
"The ""BIG"" boss"  -> The "BIG" bossThe "BIG" boss      -> The "BIG" bossThe ""BIG"" boss    -> The ""BIG"" boss
FIELDS ESCAPED BY用於控制如何寫入或讀取特殊字元。如果FIELDS ESCAPED BY字元不是Null 字元，則可以在輸出中用於對以下字元加首碼：
·         FIELDS ESCAPED BY字元
·         FIELDS [OPTIONALLY] ENCLOSED BY字元
·         FIELDS TERMINATED BY和LINES TERMINATED BY值的第一個字元
·         ASCII 0（在轉義符之後編寫的字元實際上是ASCII‘0’，而不是一個值為0的位元組）
如果FIELDS ESCAPED BY字元為空白字元，則沒有字元被轉義，並且NULL被作為NULL輸出，而不是\N。去指定一個空的轉義符不是一個好辦法，特別是如果資料的欄位值包含任何剛給定的清單中的字元時，更不能這麼做。
對於輸入值，如果FIELDS ESCAPED BY字元不是Null 字元，則出現這種字元時會被剝離，然後以下字元被作為欄位值的一部分。例外情況是，被轉義的‘0’或‘N’（例如，\0或\N，此時轉義符為‘\’）。這些序列被理解為ASCII NUL（一個零值位元組）和NULL。用於NULL處理的規則在本節的後部進行說明。
要瞭解有關‘\’-escape文法的更多資訊，請參見
9.1節，“文字值”
。
在特定情況下，field-和line-handling選項相互影響：
·         如果LINES TERMINATED BY是Null 字元串，並且FIELDS TERMINATED BY不是Null 字元串，則各行以FIELDS TERMINATED BY作為結尾。
·         如果FIELDS TERMINATED BY和FIELDS ENCLOSED BY值均為空白值('')，則使用固定行（無分隔）格式。使用固定行格式時，在欄位之間不使用分隔字元（但是您仍然可以有行終止符）。列值使用列的顯示寬度進行寫入和讀取。例如，如果某列被定義為INT(7)，則使用7字元欄位寫入列值。輸出時，通過讀取7個字元擷取列值。
LINES TERMINATED BY仍然用於分隔行。如果某行不包含所有欄位，則其餘的各列被設定到預設值。如果您沒有行終止符，您應該把終止符設定為''。在此情況下，文字檔必須包含每行的所有欄位。
固定行格式也會影響NULL值的操作，這將在以後進行介紹。注意，如果您正在使用一個多位元組字元集，則固定規格格式不會運行。
根據正在使用中的FIELDS和LINES選項的不同，NULL值的操作有所變化：
·         對於預設的FIELDS和LINES值，NULL被作為\N的欄位值編寫，用於輸出；\N欄位值被作為NULL讀取，用於輸入（假設ESCAPED BY字元為‘\’）。
·         如果FIELDS ENCLOSED BY不是空值，則包含以文字詞語NULL為值的欄位被作為NULL值讀取。這與被FIELDS ENCLOSED BY字元包圍的詞語NULL不同。該詞語被作為字串'NULL'讀取。
·         如果FIELDS ESCAPED BY是空值，則NULL被作為詞語NULL寫入。
·         採用固定行格式時（當FIELDS TERMINATED BY和FIELDS ENCLOSED BY均為空白值時採用），NULL被作為一個Null 字元串寫入。注意，這會導致在被寫入檔案時，表中的NULL值和Null 字元串均無法辨別，這是因為兩者都被作為空白字串寫入。如果您需要在讀取檔案並返回時能夠分辨兩者，則您不應使用固定行格式。
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表中的所有列：
mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;
預設情況下，如果在LOAD DATA INFILE語句的末尾處沒有設列清單時，則輸入行預計會包含一個欄位，用於表中的每個列。如果您只想載入一個表的部分列，則應指定一個列清單：
mysql> LOAD DATA INFILE 'persondata.txt'    ->           INTO TABLE persondata (col1,col2,...);
如果輸入檔案中各欄位的順序與表中各列的順序不同，您也必須指定一個列清單。否則，MySQL不能把輸入欄位和表中的列匹配起來。
列清單可以包含列名稱或使用者變數。支援SET子句。這使您可以把輸入值賦予使用者變數，然後在把結果賦予列之前，對這些值進行變換。
SET子句中的使用者變數可以採用多種方式使用。以下例子使用資料檔案中的第一列，直接用於t1.column1的值。在使用者變數被用於t2.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賦值的右側使用子查詢。如果子查詢可以返回一個值，並且此值將被賦予到一個列中，則此子查詢只能是標量子查詢。另外，您不能使用子查詢從一個正在被載入的表中選擇。
·         對于于列/變數清單或SET子句，被IGNORE子句忽略的行不被處理。
·         當載入採用固定行格式的資料時，不能使用使用者變數，因為使用者變數沒有顯示寬度。
當處理一個輸入行時，LOAD DATA會依據列/變數清單和SET子句，把行拆分成欄位，並使用值。然後，得到的行被插入表中。如果有用於表的BEFORE INSERT或AFTER INSERT觸發器，則在插入行之前和插入行之後分別啟動觸發器。
如果一個輸入行含有過多的欄位，則多餘的欄位被忽略，並且警告的數量增加。
如果一個輸入行含有的欄位過少，則輸入欄位缺失的表中的列被設定為預設值。預設值賦值在
13.1.5節，“CREATE TABLE文法”
中進行了說明。
如果欄位值缺失，則對一個空欄位值會被按不同方式理解：
·         對於字串類型，列被設定為空白字串。
·         對於數字類型，列被設定為0。
·         對於日期和時間類型，列被設定為該類型相應的“zero”。請參見
11.3節，“日期和時間類型”
。
如果您明確地把一個Null 字元串賦予一個INSERT或UPDATE語句中的字串類型、數字類型或日期或時間類型，則產生的這些值相同。
只有在兩種情況下TIMESTAMP列被設定為當前日期和時間。一種情況時當列有一個NULL值（也就是\N）時；另一種情況是（僅對於第一個TIMESTAMP列），當一個欄位清單被指定時，TIMESTAMP列會從欄位清單中被略去。
LOAD DATA INFILE把所有的輸入值當作字串，所以您不能按照使用INSERT語句的方式使用ENUM或SET列的數字值。所有的ENUM和SET值必須被指定為字串。
當LOAD DATA INFILE語句結束時，會按以下格式返回一個資訊字串：
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0
如果您正在使用C API，您可以通過調用mysql_info()函數擷取有關語句的資訊。請參見
25.2.3.34節，“mysql_info()”
。
當值通過INSERT語句被插入時或出現相同情況時，會發生警告（見
13.2.4節，“INSERT文法”
）。例外情況是，當輸入行中欄位過多或過少時，LOAD DATA INFILE也產生警告。這些警告並不儲存；警告的數量只用於指示運行是否良好。
您可以使用SHOW WARNINGS來得到第一批max_error_count警告的清單，作為有關運行錯誤的資訊。請參見
13.5.4.22節，“SHOW WARNINGS文法”
。

本文來自ChinaUnix部落格，如果查看原文請點：http://blog.chinaunix.net/u1/51541/showart_1003791.html