插入mysql丢数据 mysql导入数据缺失

转载

mob64ca1405a060 2024-08-15 14:09:21

文章标签 插入mysql丢数据 mysql 数据丢失 MySQL 数据 文章分类 MySQL 数据库

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

参考：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是有限制的，所以如果大于默认值的文件导入会失败，但是我这并没有报错，抱着试一试的心态，，，，果然没有效果呢

解决方案请看

修改my.cnf文件，如果是mac环境：解决：Mac下mysql配置文件没有my-default.cnf，无法配置my.cnf

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 ’ ” ‘,则字段以双引号”“闭合，在双引号内部的字段分隔符都会被忽略

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