审计百万行数据指南（五）：数据导入导出终极之法

上篇文章介绍了我在项目中遇到的数据导出、导入的常见问题及解决方法。

没想到周五的时候，客户导给我一个数据，我尝试了所有方法都还是导入不进mysql数据库。再一次被打脸。而且网上搜索的一堆文章都无法解决，吐槽下百度。

求助了另外一个项目的客户的DBA（数据库管理员）终于成功导入，又学会了一招。再次默念：时刻抱着向客户学习的心态。

1

数据导入的终极之法

谁叫我们不是专业搞数据库的，上篇文章介绍的都是利用navicat的数据导入向导，这种图形化界面的傻瓜操作方式。

当这种方式解决不了的时候我们也就只能用语句来导入了。

1

建表

上图是客户导给我们的数据。

在导入前我们首先要建一张空表，用来存储我们后面将要导入的数据。

CREATE TABLE `ZFIR047`(

客户编号 VARCHAR(200) DEFAULT NULL,

客户名称 VARCHAR(200) DEFAULT NULL,

物料编号 VARCHAR(200) DEFAULT NULL,

物料名称 VARCHAR(200) DEFAULT NULL,

交货日期 VARCHAR(200) DEFAULT NULL,

交货单号 VARCHAR(200) DEFAULT NULL,

订单类型 VARCHAR(200) DEFAULT NULL,

销售订单 VARCHAR(200) DEFAULT NULL,

结转数量 VARCHAR(200) DEFAULT NULL,

结转金额 VARCHAR(200) DEFAULT NULL,

结转应收 VARCHAR(200) DEFAULT NULL,

活动折扣与折让 VARCHAR(200) DEFAULT NULL,

年度折扣与折让 VARCHAR(200) DEFAULT NULL,

结转收入 VARCHAR(200) DEFAULT NULL,

结转税额 VARCHAR(200) DEFAULT NULL

)ENGINE=INNODB;

这里的语法就是：

create table 表名(

列名 数据类型(长度)

)enging=innodb;

这里的数据类型你可以先设定好，也可以就设置成varchar，数据导入后，还是可以在navicat的“设计表”中更改。

最后的engine=innodb也可以省略，默认选择的引擎就是innodb。

default null 意思是如果没有数据默认就为null，这个也可以不写。

2

导入数据

建好了表，我们就要把客户的数据导入进去了。

LOAD DATA INFILE '..\\Uploads\\ZFIR047.csv'

into TABLE `zfir047`

FIELDS TERMINATED BY ','

LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

一共四句话，每句话我们看看是什么意思。

第一句：LOAD DATA INFILE '文件地址及名称'

就是告诉电脑我要把电脑里哪个文件导入进去。需要注意的是这个地址不是你电脑任何地址都可以，是需要有mysql权限的地址。

实际上就是mysql安装的文件夹里面有个Uploads文件夹，只有这里面的文件才可以正确导入进去。那么我需要把我要导的文件拷贝到这里来。

语句中写成 '..\\Uploads\\你的文件名称'这样的格式就可以了。

第二句："into table 表名”

就是要把数据导入到哪张表。这里就是导入到开始我们建好的zfir047表中。

第三句：FIELDS TERMINATED BY '列分隔符'

这句话是什么意思呢？我们看看用记事本打开的csv文件：

我们每个字段（也就是相邻列）之间使用逗号“,"隔开的。

也就是说我们这里就是告诉电脑读取的时候用逗号隔开，把数据分割开来。

第四句:LINES TERMINATED BY '行分隔符' IGNORE 1 LINES;

这句话和上面那句类似，就是告诉电脑什么时候换行。在Windows电脑中换行符是"\r\n"，在linux电脑中是"\n"，我们自己用的是windows就用'\r\n'。

后面接了个ignore 1 lines意思是第一行不导入，因为我们导入的数据第一行是标题行，所以我们要加这一句，要是数据不包含标题，就把ignore省去。

03

注意

我们看另外一个客户导给我们的txt文件，每个数据都是用引号包起来的，如果我们用上面的代码执行后会发现，导入的数据还是包含有引号。

这个时候我们需要在第三句那里添加语句：

第三句：FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

就是相当于告诉电脑我们每个数据是用引号包起来的哦，这样导入的时候电脑会把引号去掉。导入真正有效数据。

我今天把之前用navicat导入向导导入过的100多M的数据用语句导入，之前用时大概3分多钟，用语句只需要18秒。

因此如果你导入的数据量特别大，可以考虑用语句导入，这样会节约时间。

2

数据导出的终极之法

既然我们讲了怎么用语句导入，那么也讲讲怎么用语句把查询后的结果数据导出。

SELECT 列名1，列名2 FROM 表名

INTO OUTFILE '..\\Uploads\\test.csv'

CHARACTER SET gbk

FIELDS TERMINATED BY ','

LINES TERMINATED BY '\r\n' ;

这里五句话，我们看看什么意思。

第一句是你自己写的查询语句，

第二句是告诉电脑你将查询结果保存的地址及文件名。

第三句是把编码设置成gbk，如果有中文的话需要加上这句，如果全英文，这句省略。

第四句是告诉电脑，每个字段间用什么字符来分隔。

第五句是告诉电脑，每行的换行符是什么。windows用'\r\n'这样你才能正常打开显示。

其实这个和导入的基本上是对应的，很容易理解。

结语

估计很多审计朋友没心思看，我权当自己学习中遇到的问题总结记录，方便我自己下次回顾。