相關引數：

local 從客戶端主機讀取文字檔案
ignore 如果你指定了這個引數，則重複的記錄會被跳過
replace 如果你指定了這個引數，，則重複的記錄會被覆蓋掉
IGNORE number LINES 匯入的時候跳過指定的前幾行
例如：LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;
FIELDS TERMINATED BY 指定列的分隔符，預設是\t
ENCLOSED BY 指定列的包括符，預設是空
例如：load data infile '/tmp/load_starting.txt' into table load_test fields terminated by ',' optionally enclosed by '"' lines starting by 'Neo';
ESCAPED BY 指定轉義符，預設是\
例如：load data infile '/tmp/ld_t2.csv' into table ld_t2 fields terminated by ',' enclosed by '"' escaped by '|';
LINES TERMINATED BY 指定換行符，預設是\n
例如：load data infile '/tmp/ld_t5.csv' into table ld_t5 fields terminated by ',' enclosed by '"' escaped by '|' lines terminated by '\r\n';
LINES STARTING BY 指定每行開始的字元

注意：
每次執行LOAD DATA INFILE語句，都會將被匯入表中的資料清空。

--匯入示例
--檢視匯入檔案檔案的內容
[root@localhost fire]# cat /tmp/dept.txt
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON
50,MAINTENANCE,HULUDAO

--根據欄位格式建立表
mysql> CREATE TABLE `test` (
-> `deptno` int(11) NOT NULL DEFAULT '0',
-> `dname` varchar(15) NOT NULL,
-> `loc` varchar(15) NOT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.20 sec)

--使用root使用者匯入文字檔案中的內容到對應的表中
mysql> use fire
Database changed
mysql> load data infile '/tmp/dept.txt' into table test charset gbk fields terminated by ',';
Query OK, 5 rows affected (0.07 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 0

--查詢匯入表中的內容
mysql> select * from test;
+--------+-------------+-----------+
| deptno | dname | loc |
+--------+-------------+-----------+
| 10 | ACCOUNTING | NEW YORK
| 20 | RESEARCH | DALLAS
| 30 | SALES | CHICAGO
| 40 | OPERATIONS | BOSTON
| 50 | MAINTENANCE | HULUDAO
+--------+-------------+-----------+
5 rows in set (0.00 sec)

--指定列名匯入
load data infile '/tmp/ld_t7.csv' into table ld_t7 fields terminated by ',' enclosed by '"' (id, uname, email, age);

MySQL 5.5使用LOAD DATA INFILE語句匯入資料

相關文章