load out mysql_解决：MySQL Load Data数据丢失问题

MySQL 5.7

MacOS Sirrea 10.12.1

前言

很简单，我得文件里面20w数据，导入MySQL后只剩下16w条数据，无Deleted无Skipped

排查方法

既然数据丢失了，那把成表后的数据全部导出，然后和原始的数据进行对比验证

select * from db.table INTO OUTFILE '/usr/local/mysql-5.7.18-macos10.12-x86_64/output/all'

如果无法导出permission denly ，这里有一组解决方案

检查 mysql secure_file_priv mysql 是否为None，如果为none，则需要改写配置文件

mysql> SELECT @@secure_file_priv;

+---------------------------------------------------+

| @@secure_file_priv |

+---------------------------------------------------+

| None |

+---------------------------------------------------+

1 row in set (0.00 sec)

停止mysql服务

修改my.cnf文件，追加secure-file-priv="/usr/local/mysql/output"，当然最后的output是我自己创建的文件夹，如果没有my.cnf文件，请看下面排查问题这里

重启mysql服务,进入sql环境

mysql> SELECT @@secure_file_priv;

+---------------------------------------------------+

| @@secure_file_priv |

+---------------------------------------------------+

| /usr/local/mysql-5.7.18-macos10.12-x86_64/output/ |

+---------------------------------------------------+

1 row in set (0.00 sec)

# 测试

#select xx from xx INTO OUTFILE '/usr/local/mysql-5.7.18-macos10.12-x86_64/output/xx.csv'

如果还是出现问题

Can't create/write to file 'xxxxxx' (Errcode: 13 - Permission denied)

解决方案：给自己的路径增加权限: sudo chmod 777 dirpath

如过还是遇到问题：

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

排查原因

可能是单个数据太大，导致导入失败

因为MySQL对单个package是有限制的，所以如果大于默认值的文件导入会失败，但是我这并没有报错，抱着试一试的心态，，，，果然没有效果呢

max_allowed_packet=500M

改之后保存文件并且重启MySQL服务，可以命令行查看下修改的值是否生效

可能是字段内特殊符号

根据对照原始数据，很容易发现那里缺失断层了，抽出原始字段类型字段类型如下

37388xxx|8|在哪裡跌倒，就坐在哪裡玩！"|北京市|东城区|None|None|60|81|40|http://music.163.com/user/fans?id=373xxx

以下是数据导入的方式

LOAD DATA LOCAL INFILE 'xxxxfile' INTO TABLE db.table

FIELDS TERMINATED BY '|' //行内文本分隔符

OPTIONALLY ENCLOSED BY '"'

LINES TERMINATED BY '\n'; //行结束符

ENCLOSED BY "" //字段闭合标签，例如ENCLOSED BY ' " ',则字段以双引号”“闭合，在双引号内部的字段分隔符都会被忽略

简直了！！！这是何等的我曹，这么说来，如果我的其中一个字段中只有一个" ,那我特么以后的分隔符都没用啦！要等到下一个 " 才能进行分，中间全部被包裹！

解决方案：把字符串中"的地方都替换掉，或者设置OPTIONALLY ENCLOSED BY ''

最后

差不多花了半天才找到问题所在，刚开始以为是超出限制了，所以没有导入成功，之后导出数据的时候由是一堆错误，心塞，还好一一解决，这次遇到了数据清洗的问题了，上游数据如果不经过清洗直接导入数据库，这会对下游数据产生很大的影响，切记切记，最后还是要耐心，善用搜索引擎！

致谢