mysql中使用load data infile導(dǎo)入數(shù)據(jù)的用法

有時(shí)需要將大量數(shù)據(jù)批量寫入數(shù)據(jù)庫，直接使用程序語言和Sql寫入往往很耗時(shí)間，其中有一種方案就是使用mysql load data infile導(dǎo)入文件的形式導(dǎo)入數(shù)據(jù)，這樣可大大縮短數(shù)據(jù)導(dǎo)入時(shí)間。

LOAD DATA INFILE 語句以很高的速度從一個(gè)文本文件中讀取行到一個(gè)表中。文件名必須是一個(gè)文字字符串

1、首先查詢，Mysql服務(wù)是否正在運(yùn)行，且local_infile功能是否開啟
netstat -tulpn|grep mysql
mysql -uroot -p -e "show variables like '%infile%';"

可在配置文件中永久開啟或設(shè)置變量臨時(shí)開啟或使用mysql程序使用響應(yīng)選項(xiàng)

2、當(dāng)讀取位于服務(wù)器上的文本文件時(shí)，文本文件必須處于數(shù)據(jù)庫目錄或可被mysql用戶讀取或可被所有人讀取

基本語法：
load data [low_priority] [local] infile 'file_name txt' [replace | ignore] into table tbl_name
[fields
[terminated by '\t'] 　　　　　　　　　　　　 　　 #字段的分隔符（即指定文本文件中列之間的分隔符）,默認(rèn)情況下是一個(gè)tab字符（\t）
[OPTIONALLY] enclosed by ''] 　　　　　　　　 #字段括起字符，即字段的引用字符
[escaped by'\' ]　　　　　　　　　　　　　　　 #轉(zhuǎn)義字符，默認(rèn)的是反斜杠（backslash\）
]

[lines
[terminated by '\n'] 　　　　　　　　　　　　　 #每條記錄的分隔符，默認(rèn)為'\n'即為換行符
[ignore number lines]
[(col_name, )]
]

1 如果你指定關(guān)鍵詞low_priority，那么MySQL將會(huì)等到?jīng)]有其他人讀這個(gè)表的時(shí)候，才把插入數(shù)據(jù)。
可以使用如下的命令：
load data low_priority infile "/home/mark/data.sql" into table test.Orders; 　　　　　　　　 #test是庫名，Orders是表名

2 如果指定local關(guān)鍵詞，則表明從其他客戶主機(jī)讀文件；如果local沒指定，文件必須位于mysql服務(wù)器上（當(dāng)從本機(jī)讀取文件時(shí)也可以帶上local關(guān)鍵詞）

3 replace和ignore關(guān)鍵詞控制對(duì)現(xiàn)有的唯一鍵記錄的重復(fù)的處理：

• · 如果你指定replace，新行將代替有相同的唯一鍵值的現(xiàn)有行（文本文件中的記錄將會(huì)覆蓋當(dāng)前表中的同名記錄）。

• · 如果你指定ignore，跳過有唯一鍵的現(xiàn)有行的重復(fù)行的輸入（文本文件中的記錄將會(huì)被忽略，不會(huì)覆蓋當(dāng)前表中的同名記錄）。

• · 如果你不指定任何一個(gè)選項(xiàng)，當(dāng)找到重復(fù)鍵時(shí)，出現(xiàn)一個(gè)錯(cuò)誤，并且文本文件的余下部分被忽略。

例如：
load data low_priority infile "/home/mark/data sql" replace into table test.Orders;

4 分隔符
（1） fields關(guān)鍵字指定了文本文件中字段的分割格式，如果使用這個(gè)關(guān)鍵字，MySQL剖析器希望看到至少有下面的一個(gè)選項(xiàng)：

• terminated by 字段的分隔符,默認(rèn)情況下是一個(gè)tab字符（\t）

• enclosed by 字段括起字符,即字段的引用字符

• escaped by 轉(zhuǎn)義字符，默認(rèn)的是反斜杠（backslash：\ ）

例如：load data infile "/home/mark/Orders txt" replace into table test.Orders fields terminated by',' enclosed by '"';

（2）lines 關(guān)鍵字指定了每條記錄的分隔符，默認(rèn)為'\n'即為換行符

如果兩個(gè)字段都指定了那fields必須在lines之前。
如果不指定fields關(guān)鍵字，則默認(rèn)使用： fields terminated by'\t' enclosed by'"' escaped by'\'
如果不指定一個(gè)lines子句，則moren默認(rèn)使用： lines terminated by'\n'

例如：
load data infile "/jiaoben/load.txt" replace into table test.Orders fields terminated by ',' lines terminated by '/n';

5 load data infile 可以按指定的列把文件導(dǎo)入到數(shù)據(jù)庫中（即只導(dǎo)入文本文件中的某些列而不是全部），當(dāng)我們要把數(shù)據(jù)的一部分內(nèi)容導(dǎo)入的時(shí)候，需要加入一些欄目（列/字段/field）到MySQL數(shù)據(jù)庫中，以適應(yīng)一些額外的需要。
比方說，我們要從Access數(shù)據(jù)庫升級(jí)到MySQL數(shù)據(jù)庫的時(shí)候

下面的例子顯示了如何向表中指定的字段(field)中導(dǎo)入數(shù)據(jù)：
load data infile "/home/Order txt" into table test.Orders(Order_Number, Order_Date, Customer_ID);

6 當(dāng)在mysql服務(wù)器主機(jī)上尋找文件時(shí)，服務(wù)器使用下列規(guī)則：
（1）如果給出一個(gè)絕對(duì)路徑名，服務(wù)器使用該路徑名。
（2）如果給出一個(gè)有一個(gè)或多個(gè)前置部件的相對(duì)路徑名，服務(wù)器相對(duì)服務(wù)器的數(shù)據(jù)目錄搜索文件。
（3）如果給出一個(gè)沒有前置部件的一個(gè)文件名，服務(wù)器在當(dāng)前數(shù)據(jù)庫的數(shù)據(jù)庫目錄尋找文件。
例如： /myfile txt”給出的文件是從服務(wù)器的數(shù)據(jù)目錄讀取，而作為“myfile txt”給出的一個(gè)文件是從當(dāng)前數(shù)據(jù)庫的數(shù)據(jù)庫目錄下讀取。

注意：文本文件中字段中的空值用\N表示