mysql 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 速度的更多信息,參考管理員手冊中的加速INSERT 語句 。

系統變量character_set_database 所指出的字符集被用來解釋文件中的信息。SET NAMES 和設置character_set_client 不會影響輸入的解釋。

用戶也可以使用import 實用程序裝載數據文件;它通過發送一個LOAD DATA INFILE 命令到服務器來實現。–local 選項使得import 從客戶端主機讀取數據文件。如果客戶端與服務器支持壓縮協議,用戶可以指定–compress 選項,以在較慢的網絡中獲得更好的性能。參考管理員手冊中的import — 數據導入程序 。

如果用戶指定關鍵詞LOW_PRIORITY,LOAD DATA 語句的執行將會被延遲,直到沒有其它的客戶端正在讀取表。

如果一個GSSYS 表滿足同時插入的條件(即該表在中間有空閑塊),並且您對這個GSSYS 表指定瞭CONCURRENT,則當LOAD DATA 正在執行時,其它線程會從表中重新獲取數據。即使沒有其它線程在同時使用本表格,使用本選項也會略微影響LOAD DATA 的性能。

如果指定瞭LOCAL 關鍵字,它將對連接的客戶端做出解釋:

•     如果指定瞭LOCAL,客戶端主機上的客戶端組件讀取文件並發送到服務器。可以給出文件的完整路徑以確定其精確位置。如果給出的是相對路徑,則文件名是相對於客戶端組件啟動時所在的目錄。

•     如果沒有指定LOCAL,文件是位於服務器的主機上,並且直接被服務器讀取。

當從服務器主機定位文件時,服務器使用下列規則:

•     如果給定完整的路徑,服務器使用該路徑名。

•     如果給定一個或多個前置構件的相對路徑,服務器以相對服務器的數據目錄搜索文件。

•     如果給定沒有前置構件的文件名,服務器從當前數據庫的數據庫目錄搜尋文件。

  註意:

 

 這些規則意味著,一個以'/gsfile.txt' 給出的文件是從服務器的數據目錄中讀取的,然而,以`gsfile.txt' 給出的一個文件是從當前數據庫的數據目錄下讀取的。舉例來說,下面的LOAD DATA 語句從db1 數據庫目錄下讀取文件'data.txt',因為db1 是當前數據庫,即使語句明確把文件載入到db2數據庫中的表裡,也會從db1 目錄中讀取:

 

sqlcli> USE db1;

sqlcli> LOAD DATA INFILE 'data.txt' INTO TABLE db2.gs_table;

 

  註意:

 

指定Windows 路徑名時,使用的是斜線而不是反斜線。如果要用反斜線,必須雙寫。

出於安全的原因,當讀取位於服務器上的文本文件時,文件必須位於數據庫目錄下或者可以被所有用戶讀取。也就是說,當對服務器上的文件執行LOAD DATA INFILE 時,用戶必須獲得FILE 權限。

參考管理員手冊中的GBase 提供的權限 。

與服務器直接訪問文件相比,使用LOCAL 速度稍微慢些,這是因為文件的內容必須通過客戶端到服務器的連接傳送。另一方面,對於本地文件,不需要獲得FILE 權限。

隻有服務器和客戶端都允許時,LOCAL 才可以工作。例如,如果kernel 啟動時,–local-infile=0,則LOCAL 不能工作。參考管理員手冊中的LOAD DATA LOCAL 的安全問題 。

REPLACE 和IGNORE 關鍵字處理那些與已存在的主鍵值重復的輸入記錄。

如果指定瞭REPLACE,輸入行將會代替已存在的行(也就是說,主索引值相同的行將作為存在的行)。參考REPLACE 語法 。

如果指定瞭IGNORE,與已存在行主鍵值重復的輸入行將被跳過。如果不指定二者中的任一個,則操作行為將依賴是否指定瞭LOCAL 關鍵字。沒有指定LOCAL,則如果發現有重復的鍵值,將產生一個錯誤,並忽略文本文件的其餘部分。如果指定瞭LOCAL,則缺省的操作行為將與指定瞭IGNORE 的相同;這是因為,在操作過程中,服務器沒有辦法終止文件的傳送。

如果希望裝載操作中忽略外鍵約束,可以在執行LOAD DATA 之前執行SET FOREIGN_KEY_CHECKS=0 語句。

如果用戶在一個空的GsSYS 表上使用LOAD DATA INFILE,所有非唯一索引會以分批方式被創建(就像REPAIR)。當有許多索引時,這通常可以使LOAD DATA INFILE 更快一些。正常情況下非常快,但也有極端的情況,用戶可以通過在裝載文件之前使用ALTER TABLE .. DISABLE KEYS 關閉它們和在裝載文件之後使用ALTER TABLE .. ENABLE KEYS 重建索引,從而加速索引創建。參考管理員手冊中的加速INSERT 語句 。

LOAD DATA INFILE 是SELECT … INTO OUTFILE 的反操作。參考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 系統的特點是使用兩個字符作為行終止符。在某些程序中,在書寫文件時,可能使用\r 作為行終止符,如寫字板。讀取這類文件時,需要用LINES TERMINATED BY '\r'。

如果所有的將要讀取的行都有用戶希望忽略的前綴,可以使用LINES STARTING BY 'prefix_string' 來跳過此前綴(和在它前面的任何內容)。如果一個行沒有此前綴,則整個行都被跳過。註意,prefix_string 可能在行的中間!

例如:

sqlcli> LOAD DATA INFILE '/tmp/test.txt'

-> INTO TABLE test LINES STARTING BY "xxx";

用它讀取包含有下面內容的文件:

xxx"Row",1

something xxx"Row",2

則可以得到數據(“row”,1)和(“row”,2)。

IGNORE number LINES 這個選項可以用來忽略文件開頭部分的行。例如,可以用IGNORE 1 LINES 來跳過含有列名的的頭一行:

sqlcli> LOAD DATA INFILE '/tmp/test.txt'

-> INTO TABLE test IGNORE 1 LINES;

當用戶一前一後地使用SELECT … INTO OUTFILE 和LOAD DATA INFILE 將數據從一個數據庫寫到一個文件中,然後再從文件中將它讀入數據庫中時,兩個命令的字段和行處理選項必須匹配。否則,LOAD DATA INFILE 將不能正確地解釋文件內容。假設用戶使用SELECT … INTO OUTFILE 以逗號分隔字段的方式將數據寫入到一個文件中:

sqlcli> SELECT * INTO OUTFILE 'data.txt'

->     FIELDS TERMINATED BY ','

->     FROM table2;

為瞭將由逗號分隔的文件讀回時,正確的語句應該是:

sqlcli> LOAD DATA INFILE 'data.txt' INTO TABLE table2

->     FIELDS TERMINATED BY ',';

如果用戶試圖用下面所示的語句讀取文件,它將不會工作,因為命令LOAD DATA INFILE 以定位符區分字段值:

sqlcli> LOAD DATA INFILE 'data.txt' INTO TABLE table2

->     FIELDS TERMINATED BY '\t';

可能的結果是每個輸入行將被解釋為一個單獨的字段。

LOAD DATA INFILE 也可以被用來讀取外部源獲得的文件。例如,dBASE 格式的文件,字段以逗號分隔並以雙引號包圍著。如果文件中的行以一個換行符終止,那麼下面所示的可以說明用戶將用來裝載文件的字段和行處理選項:

sqlcli> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name

->     FIELDS TERMINATED BY ',' ENCLOSED BY '"'

->     LINES TERMINATED BY '\n';

任何字段和行處理選項都可以指定一個空字符串('')。如果不是空的,FIELDS [OPTIONALLY] ENCLOSED BY 和FIELDS ESCAPED BY 值必須是一個單個字符。FIELDS TERMINATED BY 和LINES TERMINATED BY 值可以超過一個字符。例如,為瞭寫入由回車換行符終止的行,或讀取包含這樣的行的文件,應該指定一個LINES TERMINATED BY '\r\n' 子句。

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 正確讀出的輸出文件。例如,如果轉義字符為空,上面顯示的輸出將變成如下顯示的輸出。請註意第四行的第二個字段,它包含一個跟在一個引號後的逗號,看起來 像是一個字段的終止:

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 字符開始,隻要後面緊跟著字段或行TERMINATED BY 序列 ,這個字符實例就被認為用來終止一個字段值。為瞭明確,如果在字段中要使用ENCLOSED BY 字符,可以重復寫兩遍該字符,那麼它們會被解釋成單個ENCLOSED BY字符處理。例如,如果指定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 字符不是空的,它將被用於做為下列輸出字符的前綴:

•     FIELDS ESCAPED BY 字符

•     FIELDS [OPTIONALLY] ENCLOSED BY 字符 。

•     FIELDS TERMINATED BY 和LINES TERMINATED BY 值的第一個字符。

•     ASCII 0 (實際上在轉義字符後寫上ASCII '0',而不是一個零值字節)。

如果FIELDS ESCAPED BY 字符為空,那麼將沒有字符被轉義並且NULL 值仍輸出為NULL,而不是\N。指定一個空的轉義字符可能不是一個好的方法,特別是用戶的數據字段值中包含剛才列表中的任何字符時。

對於輸入值,如果FIELDS ESCAPED BY 字符不是空字符,則出現這種字符時會被剝離,然後以下字符被作為字段值的一部分。例外情況是,被轉義的‘0’或‘N’(例如,\0 或\N,此時轉義符為‘\’)。這些序列被理解為ASCII NUL(一個零值字節)和NULL。用於NULL 處理的規則在本節的後部進行說明。

關於更多的 “\” 轉義語法信息,查看文字值 。

在某些情況下,字段與行處理相互作用:

•     如果LINES TERMINATED BY 是一個空字符串,FIELDS TERMINATED BY 是非空的,則各行以FIELDS TERMINATED BY作為結尾。

•     如果FIELDS TERMINATED BY 和FIELDS ENCLOSED BY 值都是空的(''),那麼一個固定行(無定界符) 格式將被使用。用固定行格式時,在字段之間不使用分隔符(但是用戶仍然有一個行終結符)。列值的寫入和讀取使用列的“顯示”寬度。例如,如果一個列被定義 為INT(7),列的值將使用7 個字符的字段被寫入。對於輸入,列值通過讀取7 個字符來獲得。

LINES TERMINATED BY 仍然用於分離行。如果一行沒有包含所有的字段,那麼列的剩餘部分被設置為它們的默認值。如果用戶沒有一個行終結符,用戶應該設置它為''。在這種情況下,文本文件必須包含每行的所有的字段。

固定行格式也影響對NULL 值的處理;見下面。註意,如果用戶正在使用一個多字節的字符集,固定長度格式將不能工作。

NULL 值的處理有很多,取決於用戶所使用的FIELDS 和LINES 選項:

•     對於缺省的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 值和空字符串均無法辨別,這是因為兩者都被作為空字符串寫入。如果用戶需要在讀取文件並返回時能夠分辨兩者,則用戶不應使用固定行格式。

一些不能被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 表的所有列:

sqlcli> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

默認情況下,當LOAD DATA INFILE 語句後沒有提供字段列時,那麼LOAD DATA INFILE 認為輸入行包含表列中所有的字段。如果用戶希望裝載表中的某些列,那指定一個字段列表:

sqlcli> LOAD DATA INFILE 'persondata.txt'

-> INTO TABLE persondata (col1,col2,…);

如果輸入文件的字段順序不同於表中列的順序,用戶也必須指定一個字段列表。否則GBase 不知道如何將輸入字段與表中的列匹配。

Column 列表可以包含列名或者用戶變量,並且支持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 觸發器,則插入行前後將分別激活它們。

如果輸入的記錄行有太多的字段,多餘的字段將被忽略,並增加警告的數目。

如果一個輸入行的字段較少,沒有輸入字段的列將被設置為缺省值。缺省值賦值在CREATE TABLE 語法 中被描述。

一個空的字段值不同於字段值丟失的解釋:

•     對於字符串類型,列被設置為空字符串。

•     對於數字類型,列被設置為0。

•     對於日期和時間類型,列被設置為適合列類型的“零”值。參考日期和時間類型 。

如果在一個INSERT 或UPDATE 語句中明確地將一個空字符串賦給一個字符串、數字或日期或時間類型,用戶會得到與上面相同的結果。

隻有在兩種情況下TIMESTAMP 列被設置為當前日期和時間。一種情況時當列有一個NULL 值(也就是\N)時;另一種情況是(僅對於第一個TIMESTAMP 列),當一個字段清單被指定時,TIMESTAMP 列會從字段清單中被略去。

LOAD DATA INFILE 認為所有的輸入均是字符串,因而,對於ENUM 或SET 列,用戶不能以INSERT 語句的形式為其設置數字值。所有的ENUM 和SET 必須以字符串指定!

當LOAD DATA INFILE 查詢結束時,它返回信息字符串,格式如下:

Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

  

You May Also Like