北京SEO

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

2019/10/10/17:33:33  阅读:1745  来源:谷歌SEO算法  标签: 发外链

本文章介绍了利用 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);
  34. --结果:
  35. mysql>select*fromtbl;+---------------------+-----------+------------+-----------+-----------+--phpfensi.com
  36. |dt|last_name|first_name|weight_kg|st_abbrev|
  37. +---------------------+-----------+------------+-----------+-----------+
  38. |2006-09-0112:00:00|Wills|Bill|90.8|NV|
  39. |2006-09-0209:00:00|Deft|Jeff|68.1|OK|
  40. |2006-09-0403:00:00|Hobbs|Bob|102.15|UT|
  41. |2006-09-0708:00:00|Banks|Hank|79.45|TX|
  42. +---------------------+-----------+------------+-----------+-----------+

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

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

广告内容

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

相关阅读

热门评论

小潘seo 小潘seo

重庆小潘seo博客和你一起学习SEO知识,共同分享SEO优化~

总篇数165

精选文章

RMAN中catalog和nocatalog区别介绍 小技巧:为Linux下的文件分配多个权限 zimbra8.5.1安装第三方签名ssl证书的步骤 解决mysql不能远程连接数据库方法 windows服务器mysql增量备份批处理数据库 mysql中slow query log慢日志查询分析 JavaScript跨域问题总结 Linux下负载均衡软件LVS配置(VS/DR)教程 mysql中权限参数说明 MYSQL(错误1053)无法正常启动

SEO最新算法