mysql中l(wèi)oad data infile怎么用

小編給大家分享一下mysql中l(wèi)oad 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 語句以很高的速度從一個文本文件中讀取行到一個表中。文件名必須是一個文字字符串。

關(guān)于INSERT 與LOAD DATA INFILE 的效率和提高LOAD DATA INFILE 速度的更多信息，參考管理員手冊中的加速INSERT 語句 。

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

用戶也可以使用import 實用程序裝載數(shù)據(jù)文件；它通過發(fā)送一個LOAD DATA INFILE 命令到服務(wù)器來實現(xiàn)。--local 選項使得import 從客戶端主機(jī)讀取數(shù)據(jù)文件。如果客戶端與服務(wù)器支持壓縮協(xié)議，用戶可以指定--compress 選項，以在較慢的網(wǎng)絡(luò)中獲得更好的性能。參考管理員手冊中的import — 數(shù)據(jù)導(dǎo)入程序 。

如果用戶指定關(guān)鍵詞LOW_PRIORITY，LOAD DATA 語句的執(zhí)行將會被延遲，直到?jīng)]有其它的客戶端正在讀取表。

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

如果指定了LOCAL 關(guān)鍵字，它將對連接的客戶端做出解釋：

•     如果指定了LOCAL，客戶端主機(jī)上的客戶端組件讀取文件并發(fā)送到服務(wù)器?？梢越o出文件的完整路徑以確定其精確位置。如果給出的是相對路徑，則文件名是相對于客戶端組件啟動時所在的目錄。

•     如果沒有指定LOCAL，文件是位于服務(wù)器的主機(jī)上，并且直接被服務(wù)器讀取。

當(dāng)從服務(wù)器主機(jī)定位文件時，服務(wù)器使用下列規(guī)則：

•     如果給定完整的路徑，服務(wù)器使用該路徑名。

•     如果給定一個或多個前置構(gòu)件的相對路徑，服務(wù)器以相對服務(wù)器的數(shù)據(jù)目錄搜索文件。

•     如果給定沒有前置構(gòu)件的文件名，服務(wù)器從當(dāng)前的數(shù)據(jù)庫目錄搜尋文件。

  注意：

 這些規(guī)則意味著，一個以'/gsfile.txt' 給出的文件是從服務(wù)器的數(shù)據(jù)目錄中讀取的，然而，以`gsfile.txt' 給出的一個文件是從當(dāng)前數(shù)據(jù)庫的數(shù)據(jù)目錄下讀取的。舉例來說，下面的LOAD DATA 語句從db1 數(shù)據(jù)庫目錄下讀取文件'data.txt'，因為db1 是當(dāng)前數(shù)據(jù)庫，即使語句明確把文件載入到db2數(shù)據(jù)庫中的表里，也會從db1 目錄中讀?。?/p>

sqlcli> USE db1;

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

  注意：

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

出于安全的原因，當(dāng)讀取位于服務(wù)器上的文本文件時，文件必須位于數(shù)據(jù)庫目錄下或者可以被所有用戶讀取。也就是說，當(dāng)對服務(wù)器上的文件執(zhí)行LOAD DATA INFILE 時，用戶必須獲得FILE 權(quán)限。

參考管理員手冊中的GBase 提供的權(quán)限 。

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

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

REPLACE 和IGNORE 關(guān)鍵字處理那些與已存在的主鍵值重復(fù)的輸入記錄。

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

如果指定了IGNORE，與已存在行主鍵值重復(fù)的輸入行將被跳過。如果不指定二者中的任一個，則操作行為將依賴是否指定了LOCAL 關(guān)鍵字。沒有指定LOCAL，則如果發(fā)現(xiàn)有重復(fù)的鍵值，將產(chǎn)生一個錯誤，并忽略文本文件的其余部分。如果指定了LOCAL，則缺省的操作行為將與指定了IGNORE 的相同；這是因為，在操作過程中，服務(wù)器沒有辦法終止文件的傳送。

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

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

LOAD DATA INFILE 是SELECT ... INTO OUTFILE 的反操作。參考SELECT 語法 。 使用SELECT ... INTO OUTFILE 將數(shù)據(jù)從一個數(shù)據(jù)庫寫到一個文件中。使用LOAD DATA INFILE 讀取文件到數(shù)據(jù)庫中。兩個命令的FIELDS 和LINES 子句的語法是一樣的。兩個子句都是可選的，但是如果兩個同時被指定，F(xiàn)IELDS 子句必須出現(xiàn)在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 ''

換句話說，當(dāng)讀取輸入時，缺省的LOAD DATA INFILE 表現(xiàn)如下：

•     在換行符處尋找行的邊界。

•     不遺漏任何行前綴。

•     在制表符處將行分離成字段。

•     不認(rèn)為字段由任何引號字符封裝。

•     將有 “\” 開頭的定位符、換行符或`\' 解釋為字段值的一個文字字符。

相反的，當(dāng)寫入輸出時，缺省值導(dǎo)致SELECT ... INTO OUTFILE 表現(xiàn)如下：

•     在字段值間加上制表符。

•     不用任何引號字符封裝字段。

•     使用 “\” 轉(zhuǎn)義出現(xiàn)在字段值中的定位符、換行符或`\' 字符實例。

•     在行的結(jié)尾處加上換行符。

  注意：

為了寫FIELDS ESCAPED BY '\\'，用戶必須指定兩個反斜線，該值會作為一個反斜線被讀入。

  注意：

如果是Windows 系統(tǒng)的文本文件，可能必須使用LINES TERMINATED BY '\r\n' 來讀取文件，這是因為Windows 系統(tǒng)的特點(diǎn)是使用兩個字符作為行終止符。在某些程序中，在書寫文件時，可能使用\r 作為行終止符，如寫字板。讀取這類文件時，需要用LINES TERMINATED BY '\r'。

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

例如：

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

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

用它讀取包含有下面內(nèi)容的文件：

xxx"Row",1

something xxx"Row",2

則可以得到數(shù)據(jù)（“row”，1）和（“row”，2）。

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

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

-> INTO TABLE test IGNORE 1 LINES;

當(dāng)用戶一前一后地使用SELECT ... INTO OUTFILE 和LOAD DATA INFILE 將數(shù)據(jù)從一個數(shù)據(jù)庫寫到一個文件中，然后再從文件中將它讀入數(shù)據(jù)庫中時，兩個命令的字段和行處理選項必須匹配。否則，LOAD DATA INFILE 將不能正確地解釋文件內(nèi)容。假設(shè)用戶使用SELECT ... INTO OUTFILE 以逗號分隔字段的方式將數(shù)據(jù)寫入到一個文件中：

sqlcli> SELECT * INTO OUTFILE 'data.txt'

->     FIELDS TERMINATED BY ','

->     FROM table2;

為了將由逗號分隔的文件讀回時，正確的語句應(yīng)該是：

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

->     FIELDS TERMINATED BY ',';

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

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

->     FIELDS TERMINATED BY '\t';

可能的結(jié)果是每個輸入行將被解釋為一個單獨(dú)的字段。

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

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

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

->     LINES TERMINATED BY '\n';

任何字段和行處理選項都可以指定一個空字符串（''）。如果不是空的，F(xiàn)IELDS [OPTIONALLY] ENCLOSED BY 和FIELDS ESCAPED BY 值必須是一個單個字符。FIELDS TERMINATED BY 和LINES TERMINATED BY 值可以超過一個字符。例如，為了寫入由回車換行符終止的行，或讀取包含這樣的行的文件，應(yīng)該指定一個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

  注意：

在一個字段值中出現(xiàn)的ENCLOSED BY 字符，通過用ESCAPED BY 字符作為其前綴對其轉(zhuǎn)義。同時也要注意，如果用戶指定一個空的ESCAPED BY 值，可能會產(chǎn)生不能被LOAD DATA INFILE 正確讀出的輸出文件。例如，如果轉(zhuǎn)義字符為空，上面顯示的輸出將變成如下顯示的輸出。請注意第四行的第二個字段，它包含一個跟在一個引號后的逗號，看起來 像是一個字段的終止：

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 字符，那么它將被解釋為當(dāng)前字段值的一部分。

如果字段以ENCLOSED 字符開始,只要后面緊跟著字段或行TERMINATED BY 序列 ，這個字符實例就被認(rèn)為用來終止一個字段值。為了明確，如果在字段中要使用ENCLOSED BY 字符，可以重復(fù)寫兩遍該字符，那么它們會被解釋成單個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 （實際上在轉(zhuǎn)義字符后寫上ASCII '0'，而不是一個零值字節(jié)）。

如果FIELDS ESCAPED BY 字符為空，那么將沒有字符被轉(zhuǎn)義并且NULL 值仍輸出為NULL，而不是\N。指定一個空的轉(zhuǎn)義字符可能不是一個好的方法，特別是用戶的數(shù)據(jù)字段值中包含剛才列表中的任何字符時。

對于輸入值，如果FIELDS ESCAPED BY 字符不是空字符，則出現(xiàn)這種字符時會被剝離，然后以下字符被作為字段值的一部分。例外情況是，被轉(zhuǎn)義的‘0’或‘N’（例如， 或\N，此時轉(zhuǎn)義符為‘\’）。這些序列被理解為ASCII NUL（一個零值字節(jié)）和NULL。用于NULL 處理的規(guī)則在本節(jié)的后部進(jìn)行說明。

關(guān)于更多的 “\” 轉(zhuǎn)義語法信息，查看文字值 。

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

•     如果LINES TERMINATED BY 是一個空字符串，F(xiàn)IELDS TERMINATED BY 是非空的，則各行以FIELDS TERMINATED BY作為結(jié)尾。

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

LINES TERMINATED BY 仍然用于分離行。如果一行沒有包含所有的字段，那么列的剩余部分被設(shè)置為它們的默認(rèn)值。如果用戶沒有一個行終結(jié)符，用戶應(yīng)該設(shè)置它為''。在這種情況下，文本文件必須包含每行的所有的字段。

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

NULL 值的處理有很多，取決于用戶所使用的FIELDS 和LINES 選項：

•     對于缺省的FIELDS 和LINES 值，輸出時，NULL 被寫成\N，當(dāng)讀入時，\N 被作為NULL 讀入（假設(shè)ESCAPED BY 字符為 “\”）。

•     如果FIELDS ENCLOSED BY 不是空值，則包含以文字詞語NULL 為值的字段被作為NULL 值讀取。這與被FIELDS ENCLOSED BY 字符包圍的詞語NULL不同。該詞語被作為字符串'NULL'讀取。

•     如果FIELDS ESCAPED BY 是空的，NULL 值被寫為詞NULL。

•     采用固定行格式時（當(dāng)FIELDS TERMINATED BY 和FIELDS ENCLOSED BY 均為空值時采用），NULL 被作為一個空字符串寫入。注意，這會導(dǎo)致在被寫入文件時，表中的NULL 值和空字符串均無法辨別，這是因為兩者都被作為空字符串寫入。如果用戶需要在讀取文件并返回時能夠分辨兩者，則用戶不應(yīng)使用固定行格式。

一些不能被LOAD DATA INFILE 支持的情況：

•     固定尺寸的記錄行（FIELDS TERMINATED BY 和FIELDS ENCLOSED BY 均為空）和BLOB 或TEXT 列。

•     如果用戶指定一個分隔符與另一個相同，或是另一個的前綴，LOAD DATA INFILE 可能會不能正確地解釋輸入。例如，下列的FIELDS 子句將會產(chǎn)生問題：

FIELDS TERMINATED BY '"' ENCLOSED BY '"'

•    

•     如果FIELDS ESCAPED BY 為空，一個字段值中包含有FIELDS ENCLOSED BY 或LINES TERMINATED BY 后面緊跟著FIELDS TERMINATED BY 的值時，將會引起LOAD DATA INFILE 過早地停止讀取一個字段或一行。這是因為LOAD DATA INFILE 不知道字段或行值在哪里結(jié)束。

下面的例子將裝載persondata 表的所有列：

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

默認(rèn)情況下，當(dāng)LOAD DATA INFILE 語句后沒有提供字段列時，那么LOAD DATA INFILE 認(rèn)為輸入行包含表列中所有的字段。如果用戶希望裝載表中的某些列，那指定一個字段列表：

sqlcli> LOAD DATA INFILE 'persondata.txt'

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

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

Column 列表可以包含列名或者用戶變量，并且支持SET 子句。這使得能用輸入值給用戶變量賦值， 并在賦予列的結(jié)果之前對這些值進(jìn)行變換。

SET 子句中的用戶變量有多種用途。下面的例子將數(shù)據(jù)文件的第一列直接作為t1.column1 的值，并將第二列賦予一個用戶變量，此變量在作為t2.column2 的值之前進(jìn)行一個除法操作

LOAD DATA INFILE 'file.txt'

   INTO TABLE t1

   (column1, @var1)

   SET column2 = @var1/100;

SET 子句可以提供不是來源于輸入文件的值。下面的語句將column3 設(shè)置為當(dāng)前的日期和時間：

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 子句中的賦值列名應(yīng)當(dāng)只能在賦值操作符的左邊。

•     在SET 賦值語句中，可以使用子查詢。此查詢返回一個將被賦予列的值，它可能僅僅是一個標(biāo)量查詢。不能用子查詢?nèi)ゲ樵儗⒈粚?dǎo)入的表。

•     對于列/變量列表或SET 子句，由于使用IGNORE 子句而被忽略的行就不會被處理。

•     由于用戶變量沒有顯示寬度，當(dāng)導(dǎo)入的數(shù)據(jù)是行固定格式時，將不能使用用戶變量。

當(dāng)處理輸入行時，LOAD DATA 將行分為域，如果提供了列/變量列表和SET 子句，將按照它去使用這些值。然后得到的行就插入到表中。如果此表有BEFORE INSERT 或AFTER INSERT 觸發(fā)器，則插入行前后將分別激活它們。

如果輸入的記錄行有太多的字段，多余的字段將被忽略，并增加警告的數(shù)目。

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

一個空的字段值不同于字段值丟失的解釋：

•     對于字符串類型，列被設(shè)置為空字符串。

•     對于數(shù)字類型，列被設(shè)置為0。

•     對于日期和時間類型，列被設(shè)置為適合列類型的“零”值。參考日期和時間類型 。

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

只有在兩種情況下TIMESTAMP 列被設(shè)置為當(dāng)前日期和時間。一種情況時當(dāng)列有一個NULL 值（也就是\N）時；另一種情況是（僅對于第一個TIMESTAMP 列），當(dāng)一個字段清單被指定時，TIMESTAMP 列會從字段清單中被略去。

LOAD DATA INFILE 認(rèn)為所有的輸入均是字符串，因而，對于ENUM 或SET 列，用戶不能以INSERT 語句的形式為其設(shè)置數(shù)字值。所有的ENUM 和SET 必須以字符串指定！

當(dāng)LOAD DATA INFILE 查詢結(jié)束時，它返回信息字符串，格式如下：

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

看完了這篇文章，相信你對“mysql中l(wèi)oad data infile怎么用”有了一定的了解，如果想了解更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！