本文章介紹了利用 mysqlimport命令來實現數據導入方法,包括txt,csv,.sql的文件的操作,有需要了解的同學可參考一下.

mysqlimport

示例:mysqlimport -uroot -p123456 test /tmp/mytbl.txt;

約定:文件名的最后一部分為表名,以上語句導入到表mytbl.

mysqlimport必須指定數據庫,以上語句數據庫為test.

導入csv:mysqlimport -uroot -p --local --lines-terminated-by="rn" --fields-terminated-by="," --fields-enclosed-by=""" test /tmp/mytbl.csv

load data

示例:mysql> load data  infile '/tmp/mytbl.txt' into table mytbl

load data可以不指定數據庫,以上語句中,mysql必須有/tmp/的讀權限.

導入csv:mysql> load data  infile '/tmp/mytbl.csv' into table mytbl fields terminated by ',' enclosed by '"' lines terminated by 'rn'

處理重復主鍵,替換已有值:

mysql> load data infile '/tmp/mytbl.txt' replace into table mytbl fields terminated by 't' lines terminated by 'n'

表中已有則不導入:

mysql> load data infile '/tmp/mytbl.csv' ignore into table mytbl fields terminated by ',' enclosed by '"' lines terminated by 'rn'

跳過文件行,以下示例為跳過第一行:

mysql> load data infile '/tmp/mytbl.txt' into table mytbl ignore 1 lines;

預處理,文件data.txt內容:

1. Date Time Name Weight State 
2. 2006-09-01 12:00:00 Bill Wills 200 Nevada 
3. 2006-09-02 09:00:00 Jeff Deft 150 Oklahoma 
4. 2006-09-04 03:00:00 Bob Hobbs 225 Utah 
5. 2006-09-07 08:00:00 Hank Banks 175 Texas 

文件必須被加載入如下的表:

1. create table tbl 
2. ( 
3. dt datetime, 
4. last_name char(10), 
5. first_name char(10), 
6. weight_kg float, 
7. st_abbrev char(2) 
8. ) 
9. create table states 
10. ( 
11. name varchar(20), 
12. shortname char(2) 
13. ) 
14. --states 表中內容: 
15. name shortname 
16. Nevada NV 
17. Oklahoma OK 
18. Utah UT 
19. Texas TX 
20. insert into states 
21. values('Nevada','NV') 
22. ,('Oklahoma','OK') 
23. ,('Utah', 'UT') 
24. ,('Texas', 'TX') 
25. --導入： 
26. load data infile '/tmp/data.txt' into table tbl 
27. ignore 1 lines 
28. (@date,@time,@name,@weight_lb,@state) 
29. set dt=concat(@date,' ',@time), 
30.      first_name=substring_index(@name,' ',1), 
31.      last_name=substring_index(@name,' ',-1), 
32.      weight_kg=@weight_lb * .454, 
33.      st_abbrev = (select shortname from states where name=@state); 
34.  
35. --結果： 
36. mysql> select * from tbl;                                                                                                                                               +---------------------+-----------+------------+-----------+-----------+  --Vevb.com 
37. | dt                  | last_name | first_name | weight_kg | st_abbrev | 
38. +---------------------+-----------+------------+-----------+-----------+ 
39. | 2006-09-01 12:00:00 | Wills     | Bill       |      90.8 | NV        | 
40. | 2006-09-02 09:00:00 | Deft      | Jeff       |      68.1 | OK        | 
41. | 2006-09-04 03:00:00 | Hobbs     | Bob        |    102.15 | UT        | 
42. | 2006-09-07 08:00:00 | Banks     | Hank       |     79.45 | TX        | 
43. +---------------------+-----------+------------+-----------+-----------+ 

將windows本地文件導入到linux下的mysql數據庫,加local

load data local infile 'C:/mytbl.txt' into table mytbl fields terminated by 't' lines terminated by 'rn'