mysql load操作

load的文法

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語句用於高速地從一個文字檔中讀取行，並裝入一個表中。檔案名稱必須為一個文字字串。

由character_set_database系統變數指示的字元集被用於解釋檔案中的資訊。SET NAMES和character_set_client的設定不會影響對輸入的解釋。

典型的樣本

LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'

如果您只想載入一個表的部分列，則應指定一個列清單：

LOAD DATA LOCAL INFILE 'persondata.txt' INTO TABLE persondata (col1,col2);

 

檔案的路徑

如果指定了LOCAL，則被認為與串連的用戶端有關：

•  如果指定了LOCAL，則檔案會被客戶主機上的用戶端讀取，並被發送到伺服器。檔案會被給予一個完整的路徑名稱，以指定確切的位置。如果給定的是一個相對的路徑名稱，則此名稱會被理解為相對於啟動用戶端時所在的目錄。
•  如果LOCAL沒有被指定，則檔案必須位於伺服器主機上，並且被伺服器直接讀取。

當在伺服器主機上為檔案定位時，伺服器使用以下規則：

• 如果給定了一個絕對的路徑名稱，則伺服器使用此路徑名稱。
• 如果給定了帶有一個或多個引導組件的相對路徑名稱，則伺服器會搜尋相對於伺服器資料目錄的檔案。
• 如果給定了一個不帶引導組件的檔案名稱，則伺服器會在預設資料庫的資料庫目錄中尋找檔案。

注意，這些規則意味著名為./myfile.txt的檔案會從伺服器資料目錄中被讀取，而名為myfile.txt的同樣的檔案會從預設資料庫的資料庫目錄中讀取。

 

從用戶端使用絕對路徑load資料

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

 

從伺服器裡使用相對路徑load資料

下面的LOAD DATA語句會從db1資料庫目錄中讀取檔案data.txt，因為db1是當前資料庫。即使語句明確把檔案載入到db2資料庫中的表裡，也會從db1目錄中讀取。

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 '\n' STARTING BY ''

換句話說，當讀取輸入值時，預設值會使LOAD DATA INFILE按如下方式運行：

在新行處尋找行的邊界。不會跳過任何行首碼。在定位字元處把行分解為欄位。不希望欄位被包含在任何引號字元之中。出現定位字元、新行、或在‘\’前有‘\’時，理解為作為欄位值一部分的文字字元。

相反的，當編寫輸出值時，預設值會使SELECT...INTO OUTFILE按如下方式運行：

在欄位之間寫入定位字元。不把欄位包含在任何引號字元中。當欄位值中出現定位字元、新行或‘\’時，使用‘\’進行轉義。在行的末端寫入新行。

注意，要寫入FIELDS ESCAPED BY ‘\\’，您必須為待讀取的值指定兩個反斜線，作為一個單反斜線使用。

備忘:如果您已經在Windows系統中產生了文字檔，您可能必須使用LINES TERMINATED BY ‘\r\n’來正確地讀取檔案，因為Windows程式通常使用兩個字元作為一個行終止符。部分程式，當編寫檔案時，可能會使用\r作為行終止符。要讀取這樣的檔案，應使用LINES TERMINATED BY ‘\r’。

 

STARTING  LINES選項

如果所有您希望讀入的行都含有一個您希望忽略的共用首碼，則您可以使用'prefix_string'來跳過首碼（和首碼前的字元）。如果某行不包括首碼，則整個行被跳過。注釋：prefix_string會出現在一行的中間。

以下面的test.txt為檔案源

xxx"row",1something xxx"row",2

使用以下sql匯入資料

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

最後並只得到資料("row",1)和("row",2)。

 

TERMINATED LINES選項

如果jokes被由%%組成的行分隔，要讀取包含jokes的檔案，您可以這麼操作：

LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes FIELDS TERMINATED BY '' LINES TERMINATED BY '\n%%\n' (joke);

 

TERMINATED,ENCLOSED,ESCAPED FIELD選項

TERMINATED用於控制欄位的分隔字元，可以為多個字元。

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被作為NULL輸出，而不是\N。去指定一個空的轉義符不是一個好辦法，特別是如果資料的欄位值包含任何剛給定的清單中的字元時，更不能這麼做。

如果在欄位值內出現ENCLOSED BY字元，則通過使用ESCAPED BY字元作為首碼，對ENCLOSED BY字元進行轉義。

 

轉自 http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#load-data