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. DateTimeNameWeightState
2. 2006-09-0112:00:00BillWills200Nevada
3. 2006-09-0209:00:00JeffDeft150Oklahoma
4. 2006-09-0403:00:00BobHobbs225Utah
5. 2006-09-0708:00:00HankBanks175Texas

文件必须被加载入如下的表:

1. createtabletbl
2. (
3. dtdatetime,
4. last_namechar(10),
5. first_namechar(10),
6. weight_kgfloat,
7. st_abbrevchar(2)
8. )
9. createtablestates
10. (
11. namevarchar(20),
12. shortnamechar(2)
13. )
14. --states表中内容:
15. nameshortname
16. NevadaNV
17. OklahomaOK
18. UtahUT
19. TexasTX
20. insertintostates
21. values('Nevada','NV')
22. ,('Oklahoma','OK')
23. ,('Utah','UT')
24. ,('Texas','TX')
25. --导入：
26. loaddatainfile'/tmp/data.txt'intotabletbl
27. ignore1lines
28. (@date,@time,@name,@weight_lb,@state)
29. setdt=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=(selectshortnamefromstateswherename=@state);
35. --结果：
36. mysql>select*fromtbl;+---------------------+-----------+------------+-----------+-----------+--phpfensi.com
37. |dt|last_name|first_name|weight_kg|st_abbrev|
38. +---------------------+-----------+------------+-----------+-----------+
39. |2006-09-0112:00:00|Wills|Bill|90.8|NV|
40. |2006-09-0209:00:00|Deft|Jeff|68.1|OK|
41. |2006-09-0403:00:00|Hobbs|Bob|102.15|UT|
42. |2006-09-0708: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'