如何使用MySQL对数据进行清洗--详解其中遇到的各种问题

最近在学陈旸老师的专栏SQL必知必会里面学到如何用SQL对数据进行清洗这一节时，在实操的时候遇到了一些问题，这里记录一下其中对各种问题的解决办法，希望对后面学习的朋友也有帮助！(PS: 我这里是WIN10系统下使用的MySQL-8.0.15版本！！！）

• 第一步，创建数据表titanic_train（陈老师是直接用的Navicat工具创建的，而小白我为了锻炼自己的能力，决定直接用MySQL操作）：

由于我的MySQL数据库并没有这个表格，所以需要我先创建这个表格，下面是创建titanic_train表格的MySQL语句：

DROP TABLE IF EXISTS `titanic_train`;
CREATE TABLE `titanic_train`  (
  `PassengerId` varchar(255),
  `Survived` varchar(255),
  `Pclass` varchar(255),
  `Name` varchar(255),
  `Sex` varchar(255),
  `Age` varchar(255),
  `SibSp` varchar(255),
  `Parch` varchar(255),
  `Ticket` varchar(255),
  `Fare` varchar(255),
  `Cabin` varchar(255),
  `Embarked` varchar(255)
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

OK,数据表已经成功创建好了，下面就是导入外部数据。

• 第二步，导入外部数据集。于是小白成功的迎来了第一个问题：

• The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
  这个问题的具体解决办法已经写在了前面那篇博客，请自行前往了解，谢谢：
  如何解决MySQL8.0外部文件导入的权限问题？

• 好不容易把第一个外部文件导入的权限问题解决了，数据成功导入了，接下来第三步：
  我们就要用MySQL对数据进行清洗：
  首先对数据集进行一个概览：
  
  可以看到正如陈老师文中所写，数据存在缺失。根据陈老师的清洗四字准则---->“完全合一”，我们先检查完整性（即检查字段是否存在空值）：
  陈老师用的是这个命令：

SELECT COUNT(*) as num FROM titanic_train WHERE Age IS NULL;

但是我这里却没有效。。。这就是第二个问题：查询空值用IS NULL无效！

于是我去翻看MySQL8.0的官方文档，发现还有一种查看空值的写法：

于是我也改成这样的命令来查询空值数量，结果就生效了：

SELECT COUNT(*) as num FROM titanic_train WHERE Age = '';

同样对多字段的空值进行统计的命令如下：

1. 对缺失值进行处理，这里采用的是均值填充（缺失值还有其他处理方法，改天专门写一篇来讲）：
   按照陈老师的方法是先建立一张临时表titanic_train2，再对原表titanic_train进行数据填充的修改。所以这里我们要先创建一张临时表，使用如下命令：

CREATE TEMPORARY TABLE titanic_train2 SELECT * FROM titanic_train;

然后对titanic_train进行修改并填充：

UPDATE titanic_train SET age = (SELECT ROUND(AVG(age),1) FROM titanic_train2) WHERE age = '';

修改成功你会看的下面的change为177：

（PS:如果你的UPDATE命令虽然成功执行了，但是change是0，像下面这种情况：

那么恭喜你，遇到了跟我一样的第三个问题：UPDATE命令无效？
一般出现这种情况的原因有两种：
1. 要更新的数据跟原始数据一样；
2. 要更新的数据并不存在；

而小白我的情况后来通过排查发现是第二种，因为我创建临时表的时候并没有把数据全部复制过去，于是导致了临时表虽然存在，但是里面没有数据，这就尴尬了😂。所以解决办法也很简单，就是重新创建临时表，然后再次执行上面的UPDATE命令即可）

然后我们可以对数据集进行查看来确认我们的数据填充是成功的：

OK,接下来我们继续对其他字段进行清洗，跟着陈老师的步骤是Cabin和Embarked这两个字段，这里没有问题，直接上图：

• 好了，直到上面我们对缺失值的处理已经结束，完整性已经ok，下面是检查全面性：
  这里就是观察数值情况并检查字段类型，显然一开始我们设置的表格字段类型是不太全面规范的，需要进行修改。陈老师这里也给出了解决的办法，一般创建数据表应该是这样的才对，所以我们也对字段进行规范修改。
  
• 到这里全面性也检查完了，然后是合法性和唯一性的检查。经过检查，这个数据集不存在这两个问题，所以就不需要操作。
• 最后就是对清洗完的数据进行可视化，这里用的MySQL For Excel 插件工具，除了安装这里要注意一下：需要先安装 一个微软VS Tools 的工具 才可以：
  
  这里是微软工具的下载链接：
  https://www.microsoft.com/en-us/download/details.aspx?id=48217
  你可选择下载中文版，方便你安装。

接着再安装陈老师说的那两个工具，然后去Excel里面设置一下登录账号和密码，就可以把刚刚清洗完的MySQL数据导入Excel：

最后就是成功的了解到用户生存情况（Survived）与 Embarked 字段的关系：

总的操作到这里就结束了，这次实操遇到了三个问题+一个注意事项：

1. MySQL8.0外部文件导入的权限问题；
2. 第二个问题：查询空值用IS NULL无效；
3. UPDATE命令执行成功却Change为0，无效更新；
4. 注意事项：需要先安装 一个微软VS Tools 的工具才可以用MySQL For Excel插件。

总的来说，个人觉得MySQL进行数据清洗没有直接用Python操作更方便，虽然陈老师也建议可以用存储过程进行处理，但是个人还是更喜欢用Python来进行数据清洗，下一篇写写Python来进行数据清洗的方法。

谢谢阅读，如有错误，请指正。

以上数据来源 Kaggle： https://www.kaggle.com/c/titanic/data
文中课程资料来自陈旸老师的：SQL必知必会之SQL项目实战3讲的第一讲：如何用SQL对数据进行清洗