mysql导入数据方法(txt,csv)

本文章介绍了利用 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;                                                                                                                                               +---------------------+-----------+------------+-----------+-----------+  --phpfensi.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'