上篇文章介绍了我在项目中遇到的数据导出、导入的常见问题及解决方法。
没想到周五的时候,客户导给我一个数据,我尝试了所有方法都还是导入不进mysql数据库。再一次被打脸。而且网上搜索的一堆文章都无法解决,吐槽下百度。
求助了另外一个项目的客户的DBA(数据库管理员)终于成功导入,又学会了一招。再次默念:时刻抱着向客户学习的心态。
一、数据导入的终极之法
谁叫我们不是专业搞数据库的,上篇文章介绍的都是利用navicat的数据导入向导,这种图形化界面的傻瓜操作方式。
当这种方式解决不了的时候我们也就只能用语句来导入了。
1.建表
上图是客户导给我们的数据。
在导入前我们首先要建一张空表,用来存储我们后面将要导入的数据。
CREATE
这里的语法就是:
create table 表名(
列名 数据类型(长度)
)enging=innodb;
这里的数据类型你可以先设定好,也可以就设置成varchar,数据导入后,还是可以在navicat的“设计表”中更改。
最后的engine=innodb也可以省略,默认选择的引擎就是innodb。
default null 意思是如果没有数据默认就为null,这个也可以不写。
2.导入数据
建好了表,我们就要把客户的数据导入进去了。
LOAD
一共四句话,每句话我们看看是什么意思。
第一句:LOAD DATA INFILE '文件地址及名称'
就是告诉电脑我要把电脑里哪个文件导入进去。需要注意的是这个地址不是你电脑任何地址都可以,是需要有mysql权限的地址。
实际上就是mysql安装的文件夹里面有个Uploads文件夹,只有这里面的文件才可以正确导入进去。那么我需要把我要导的文件拷贝到这里来。
语句中写成 '..Uploads你的文件名称'这样的格式就可以了。
第二句:"into table 表名”
就是要把数据导入到哪张表。这里就是导入到开始我们建好的zfir047表中。
第三句:FIELDS TERMINATED BY '列分隔符'
这句话是什么意思呢?我们看看用记事本打开的csv文件:
我们每个字段(也就是相邻列)之间使用逗号“,"隔开的。
也就是说我们这里就是告诉电脑读取的时候用逗号隔开,把数据分割开来。
第四句:LINES TERMINATED BY '行分隔符' IGNORE 1 LINES;
这句话和上面那句类似,就是告诉电脑什么时候换行。在Windows电脑中换行符是"rn",在linux电脑中是"n",我们自己用的是windows就用'rn'。
后面接了个ignore 1 lines意思是第一行不导入,因为我们导入的数据第一行是标题行,所以我们要加这一句,要是数据不包含标题,就把ignore省去。
3.注意
我们看另外一个客户导给我们的txt文件,每个数据都是用引号包起来的,如果我们用上面的代码执行后会发现,导入的数据还是包含有引号。
这个时候我们需要在第三句那里添加语句:
第三句:FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
就是相当于告诉电脑我们每个数据是用引号包起来的哦,这样导入的时候电脑会把引号去掉。导入真正有效数据。
我今天把之前用navicat导入向导导入过的100多M的数据用语句导入,之前用时大概3分多钟,用语句只需要18秒。
因此如果你导入的数据量特别大,可以考虑用语句导入,这样会节约时间。
二、数据导出的终极之法
既然我们讲了怎么用语句导入,那么也讲讲怎么用语句把查询后的结果数据导出。
SELECT
这里五句话,我们看看什么意思。
第一句是你自己写的查询语句,
第二句是告诉电脑你将查询结果保存的地址及文件名。
第三句是把编码设置成gbk,如果有中文的话需要加上这句,如果全英文,这句省略。
第四句是告诉电脑,每个字段间用什么字符来分隔。
第五句是告诉电脑,每行的换行符是什么。windows用'rn'这样你才能正常打开显示。
其实这个和导入的基本上是对应的,很容易理解。
结语
估计很多审计朋友没心思看,我权当自己学习中遇到的问题总结记录,方便我自己下次回顾。