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内容:
- Date Time Name Weight State
- 2006-09-01 12:00:00 Bill Wills 200 Nevada
- 2006-09-02 09:00:00 Jeff Deft 150 Oklahoma
- 2006-09-04 03:00:00 Bob Hobbs 225 Utah
- 2006-09-07 08:00:00 Hank Banks 175 Texas
文件必须被加载入如下的表:
- create table tbl
- (
- dt datetime,
- last_name char(10),
- first_name char(10),
- weight_kg float,
- st_abbrev char(2)
- )
- create table states
- (
- name varchar(20),
- shortname char(2)
- )
- --states 表中内容:
- name shortname
- Nevada NV
- Oklahoma OK
- Utah UT
- Texas TX
- insert into states
- values('Nevada','NV')
- ,('Oklahoma','OK')
- ,('Utah', 'UT')
- ,('Texas', 'TX')
- --导入:
- load data infile '/tmp/data.txt' into table tbl
- ignore 1 lines
- (@date,@time,@name,@weight_lb,@state)
- set dt=concat(@date,' ',@time),
- first_name=substring_index(@name,' ',1),
- last_name=substring_index(@name,' ',-1),
- weight_kg=@weight_lb * .454,
- st_abbrev = (select shortname from states where name=@state);
- --结果:
- mysql> select * from tbl; +---------------------+-----------+------------+-----------+-----------+ --phpfensi.com
- | dt | last_name | first_name | weight_kg | st_abbrev |
- +---------------------+-----------+------------+-----------+-----------+
- | 2006-09-01 12:00:00 | Wills | Bill | 90.8 | NV |
- | 2006-09-02 09:00:00 | Deft | Jeff | 68.1 | OK |
- | 2006-09-04 03:00:00 | Hobbs | Bob | 102.15 | UT |
- | 2006-09-07 08:00:00 | Banks | Hank | 79.45 | TX |
- +---------------------+-----------+------------+-----------+-----------+
将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
相关文章
- ·mysql中导入数据load data在myisam与innodb区别(2014-09-23)
- ·mysql中常用命令导入导出数据命令(mysqldump,source)(2014-09-23)
- ·合理使用mysql中的load data infile导入数据(2014-09-25)
- ·MySQL数据导入导出实例教程手册(2014-09-27)
- ·MYSQL数据库的导出和导入方法详解(2014-10-04)
- ·mysql中导入数据与导出数据库sql语句(2014-10-09)
- ·mysql导入数据提示的MySQL server has gone away解决办法(2014-10-09)
- ·mysql导入数据库时出现40101错误解决方法(2014-10-10)
- ·mysql中导入较大数据简单实现代码(2014-10-13)
- ·MySQL中into outfile将导入数据到文件(2015-04-17)
推荐文章
热门文章
最新评论文章
- 写给考虑创业的年轻程序员(10)
- PHP新手上路(一)(7)
- 惹恼程序员的十件事(5)
- PHP邮件发送例子,已测试成功(5)
- 致初学者:PHP比ASP优秀的七个理由(4)
- PHP会被淘汰吗?(4)
- PHP新手上路(四)(4)
- 如何去学习PHP?(2)
- 简单入门级php分页代码(2)
- php中邮箱email 电话等格式的验证(2)