项目实训（五）

一、

        这篇博客解决一个有关向mysql里导入和导出csv文件的问题。

二、

        问题起因：需要将世界疫情的历史数据（保存在csv文件中）导入 mysql中。

        首先考虑使用mysql workbench进行导入，这样操作很方便，如下：

        右键要导入的表，选择 TABLE DATA IMPORT

        再选择文件路径，对应字段即可导入。

         需要注意一个问题是有关编码。

        在导入之前要检查需要导入的csv文件的编码是否为UTF8，如果不是，以记事本方式打开文件，另存的时候选择编码为UTF8，如果文本编辑器支持直接修改当然也可以。

        以这种方式导入的话如果数据量大导入速度比较慢，数据量小的话比较方便。

        但是在导入我们的世界疫情历史数据时出现问题，因为世界各国统计方式的问题，我们获取到的数据集内有很多的空值，而这些空值在导入时不会被识别为空值，那么就会出现导入不成功的问题：

        而且因为导入很慢，所以我们采取另一种导入方式，直接使用SQL语句进行导入： 

---

          mysql的导入语句：

load data local infile 'FILE-PATH.csv' 
into table [表名] 
fields terminated by ',' 
optionally enclosed BY '"' 
lines terminated by '\r\n' 
ignore 1 lines;

mysql的导出语句

[SELECT 语句] 
into outfile '/tmp/user.csv' 
fields terminated by ',' 
optionally enclosed by '"' 
lines terminated by '\r\n';

fields terminated by ‘字段间分隔符’
定义字段间的分隔符

optionally enclosed by ‘字段包围符’
定义包围字段的字符（数值型字段无效）

lines terminated by ‘行间分隔符’
定义每行的分隔符

ignore 1 lines

忽略第一行（csv文件第一行字段名）

---

         一些问题的解决：

        （一）路径的问题:

        在MySQL5.7之后的版本，会报以下错误

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

        原因是在这个版本之后 ，mysql增加了一个参数--secure-file-priv 用于限制文件的导入导出，即安全文件夹

        使用如下命令查看：

show global variables like '%secure_file_priv%';

         那我们就需要知道这个变量代表的意义才能对他进行修改（注意，这是一个只读参数，无法直接使用set对其进行修改）：

        那我们通过以下方法对它进行改变：

 进入mysql 目录下找到my.ini文件，里面保存的是mysql的一些参数

         

添加 secure_file_priv，设置为空（或自定义目录）

        设置之后我们再去查看该变量的值：

        会发现无法识别，那么原因出在哪呢。

 添加 secure_file_priv 变量，需要注意在在ini文件中，有[mysqld]、[ mysql ] 和 [client] 等参数，添加变量得放在[ mysqld ]下，而不是一味地加在最后一行

         

        接下来再进入mysql中查看变量值，就会发现它已经被我们修改：

        接下来我们将我们需要导入（导出）的文件放到安全文件夹下就可以使用SQL语句对它们进行操作了。

        （二）空值的问题：

        尽管上面我们解决了导入目录的问题，但在导入过程中还是发生了空值识别错误的问题。如何解决空值报错的问题，也就是要将导入数据中的空值（有些空值是有意义的，比如我们获得的数据集中的空值仅仅代表未知，即没有进行统计，应该让它继续保持空值，而不是用0替代或者使用均值、中数等方式）继续为NULL。

        有两种解决方案

        第一种：

 将上面的导入语句改为下面的形式：

load data local infile 'FILE-PATH.csv' 
into table [表名] 
fields terminated by ',' 
optionally enclosed BY '"' 
lines terminated by '\r\n' 
ignore 1 lines
(@a, b, c)
set
a = NULLif(@a,'');

即识别到csv文件中a字段为空(在csv文件中用''表示)时，使用NULL替代。

        但在使用这种方式是有一个问题，那就是由于我们要导入的文件中字段很多（30多个），且很多字段中包含控制，因此想要 每个字段都进行一次设置命令很长容易出错。

        因此使用第二种方式：

       第二种：

修改sql_mode：

mysql> set @@sql_mode=ANSI;

ANSI模式：宽松模式，更改语法和行为，使其更符合标准SQL。对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告。对于csv导入空值报错，可以先把sql_mode设置为ANSI模式，这样便可以插入数据，而对于无数据的字段值，数据库将会用NULL值代替。

        最后成功导入文件：

        可以看到导入17万行数据，且没行数据包含30多个字段，执行速度很快。

        到这里，问题解决，将获取的数据集全部导入。

---

         注，在导入过程中由于各种问题的出现，于是曾想到使用另一种数据库来进行存储的方式，因为数据集中空值太多。

        选择了Mongodb进行了常识，导入过程非常方便，因为mongo是文档型（存储格式类BSON），所以在导入过程中空值会被直接识别，于是导入后集合中根本不存在这个字段，但是这么操作会导致一个问题，就是在取数据的时候需要判断有没有该字段，在写代码时会很不方便。

---

        这篇博客就到这，后面继续介绍项目实训过程中遇到的问题。