当前位置:首页 > Mysql教程 > 列表

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

发布:smiling 来源: PHP粉丝网  添加日期:2014-10-13 13:49:53 浏览: 评论:0 

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

将windows本地文件导入到linux下的mysql数据库,加local

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

Tags: mysql导入数据 mysql导入txt

分享到: