解决:MySQL Load Data数据丢失问题

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/MrLevo520/article/details/75092481

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)
  1. 停止mysql服务
  2. 修改my.cnf文件,追加secure-file-priv="/usr/local/mysql/output",当然最后的output是我自己创建的文件夹,如果没有my.cnf文件,请看下面排查问题这里
  3. 重启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

参考:Can’t create/write to file ‘/tmp/#sql_xxxx.MYI’ (Errcode: 13)

如过还是遇到问题:

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

参考:MYSQL导出数据出现The MySQL server is running with the –secure-file-priv option so it cannot execute this


排查原因

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

因为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 ”

多谢→_→LOAD DATA INFILE语句导入数据进入MySQL的一些注意事项


最后

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


致谢

LOAD DATA INFILE语句导入数据进入MySQL的一些注意事项
解决:Mac下mysql配置文件没有my-default.cnf,无法配置my.cnf
修改MySQL导入数据文件大小限制的方案

展开阅读全文

没有更多推荐了,返回首页